Writing MySQL-Based Programs - MySQL - page 69

4. If you don't fetch the entire result set, release resources associated with it by calling
finish() .
The following example illustrates these steps, using fetchrow_array() as the row-
fetching method and assuming that RaiseError is enabled so that errors terminate the
script:
my $sth = $dbh -> prepare ( "SELECT id, name, cats FROM profile" );
$sth -> execute ();
my $count = 0 ;
while ( my @val = $sth -> fetchrow_array ())
{
print "id: $val[0], name: $val[1], cats: $val[2]\n" ;
++ $count ;
}
$sth -> finish ();
print "Number of rows returned: $count\n" ;
The row array size indicates the number of columns in the result set.
The row-fetching loop just shown is followed by a call to finish() , which closes the
result set and tells the server to free any resources associated with it. If you fetch every
row in the set, DBI notices when you reach the end and releases the resources for you.
Thus, the example could omit the finish() call without ill effect.
As the example illustrates, to determine how many rows a result set contains, count
them while fetching them. Do not use the DBI rows() method for this purpose. The
DBI documentation discourages this practice because rows() is not necessarily reliable
for SELECT statements—due not to a deficiency in DBI, but to differences in behavior
among database engines.
DBI has several methods that fetch a row at a time. The one used in the preceding
example, fetchrow_array() , returns an array containing the next row, or an empty list
when there are no more rows. Array elements are present in the order named in the
SELECT statement. Access them as $val[0] , $val[1] , and so forth.
The fetchrow_array() method is most useful for statements that explicitly name the
columns to select. (With SELECT * , there are no guarantees about the positions of col‐
umns within the array.)
fetchrow_arrayref() is like fetchrow_array() , except that it returns a reference to
the array, or undef when there are no more rows. As with fetchrow_array() , array
elements are present in the order named in the statement. Access them as $ref->[0] ,
$ref->[1] , and so forth:
while ( my $ref = $sth -> fetchrow_arrayref ())
{
print "id: $ref->[0], name: $ref->[1], cats: $ref->[2]\n" ;
}