question about DBI's fetchrow_arrayref()

[Darren Duncan]

On Wed, 4 Sep 2002, nkuipers wrote:
<snip>
> 1 my @stash;
> 2 while ( my $array_ref = $sth->fetchrow_arrayref ) {
> 3   push @stash, [ @$array_ref ];
> 4 }
> 5 foreach $array_ref ( @stash ) {
> 6   print :Row: @$array_ref\n";
> 7 }

Since we are fetching the whole row set into an array anyway before using
it, I find it much more efficient to use DBI's built-in function for
selecting all rows, rather than manually doing it one at a time.  This is
how I use it in my own code:

... code for preparing and executing $sth ...
... if we get here there was no db error generated by above ...

my ($rowset);
eval {
	$rowset = $sth->fetchall_arrayref({});  # get array of hashes
};
if( $@ ) {
	... register that an error happened ...
}

If there was no error, then we loop result as normal, such as like this:

foreach my $row (@{$rowset}) {
	print "\n-----------------------------\n";
	foreach my $field_name (keys %{$row}) {
		my $field_value = $row->{$field_name};
		print "field '$field_name' contains '$field_value'\n";
	}
}

There is little point in copying to a literal array, as it is just
needless copying.  By leaving the data structure as a reference, it is
easy to pass around as a unit between functions or store it in a larger
structure.  The code I used above (with fetchall) is actually part of a
function in my own class that wraps DBI; that function returns $rowset.

Also, since SQL databases by their definition don't have columns/fields in
a particular order, fetching field values by name will make sure you get
the right one, rather than taking a chance they are returned in a
particular numerical array index.  Thats why I return rows as hashes.

-- Darren Duncan