Reading A TAble

PerceptiveJohn has asked for the wisdom of the Perl Monks concerning the following question:

Hello Again.New to Perl. Geting There.

I have this script that will read a Table. The table has 2 fields: Draw date (Primary Key), Draw Number (an Index). The table is MYSql in MYISAM format.

I copied the script below from a book to get the database to open then read the table. If I understand correctly, gthis script reads the entire table into memory then extracts thae rows based on fetchrow.

We come from the MicroFocus Icobol platform that uses similar isam files. How would you just Read one record at a time (by either primary key or the other index) without storing them all in memory, do what you have to do with it, then read the next record?

# Connect To The Database
my $dbh = DBI->connect("DBI:mysql:masslottery_db", "root", "system1");
 die "Connect to masslottery_db failed: " . DBI->errstr() unless $dbh;
 print "connect to masslottery_db successful!\n";
 
#Prepare The Query To Get Data From Table Dailymf
#$dbh Is Called The Database Handler
my $sth = $dbh->prepare("Select dailymf_drawdate,dailymf_number FROM d
+ailymf") 
                 or die "Prepare For Dailymf Failed: " . $dbh->errstr(
+);

#Execute The Query
#$sth Is Called The State Handler
$sth->execute()
       or die "Execute For Dailymf Failed: " . $sth->errstr();

#Read (loop) Each Row (record) and Print It
while (($dailymf_drawdate, $dailymf_number) = $sth->fetchrow()) {
        print "$dailymf_drawdate : $dailymf_number\n";
        }
[download]

Thanks again for the help.

Comment on Reading A TAble Download Code

Replies are listed 'Best First'.
Re: Reading A TAble by Ieronim (Friar) on Jul 12, 2006 at 18:39 UTC
The way of fetching data you demonstrated is nearly as fast and effective as possible :) I don't think it needs any improvements in your case, as you want to extract all data matching your criteria. Of course, if you want to fetch only a certain number of rows, you can use the `LIMIT` modifier in your SELECT statements. See Mysql Documentation.	[reply] [d/l]
Re^2: Reading A TAble by PerceptiveJohn (Acolyte) on Jul 12, 2006 at 19:58 UTC
Ok, but doesn't this method take a lot of memory?? And, do you know the syntax of the select command to read by primary key or another index key? Thanx.	[reply]
Re^3: Reading A TAble by Ieronim (Friar) on Jul 12, 2006 at 22:19 UTC
Just let MySQL do its work :) The memory management for each request isn't your task, it's the task of database engine. the syntax of the select command to read by primary key or another index key? I did not understand your question. Maybe you want to get the data corresponding to a key? In this case you need to specify the WHERE clause to read only certain data from the table, e.g. `SELECT id, name FROM mytable WHERE id=1` [download] I repeat: RTFM! MySQL documentation contains the full info on SQL syntax. 10 minutes of reading the docs will save for you hours of asking questions :)	[reply] [d/l]
Re^3: Reading A TAble by duckyd (Hermit) on Jul 12, 2006 at 20:47 UTC
Accessing the table in the method you described should not load the whole table into memory. If you'd like to order the results by some field (primary key or otherwise), use an ORDER BY clause in your select statement.	[reply]
Re: Reading A TAble by derby (Abbot) on Jul 12, 2006 at 18:23 UTC
It's really a function of the driver as to how data is passed from the server to the client. Looking at the docs for DBD::mysql, it appears there is an attribute - mysql_use_result - that controls what you want. -derby	[reply]