byterock

More Data Fetching

In part 5 and part 6 we looked at fetching a single record from queries into either a array, array ref or even a hash refs now we will look at fetching more than one record at a time.

selectall_arrayref

Many times when working with data, it is desirable to have all of the rows returned at once. The is especially so in the Perl world, which has so many nifty little tools to manipulate arrays in all sorts of different ways. DBI has a both a method on the statement handle and one on the database handle that can utilize batch fetching.

The database handle method, selectall_arrayref(), is most useful when you want to get the data from static SQL more that once in a program.

For example, a record set of country_id() and the name of the country associated with the id, can be used to populate a select box and then be used in the same program to look up the name of a country. So instead of preparing, executing, and iterating though an entire record set each time we need it, needlessly sucking up RDMS resources, you can just use a statement such as this:

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth );

$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');

my $countries =
$dbh->selectall_arrayref("SELECT country_id, country_name
FROM countries
ORDER BY country_name");

foreach my $row (@$countries) {
print "My country_id is $row->[0], ";
print "my country name is $row->[1].\n";
}

foreach my $row (@$countries) {
print "My country’s name is $row->[1], ";
print "my country’s Id is $row->[0].\n";
}

where we use only one DBI method call and just reuse the returned array ref when we need it.

fetchall_arrayref

The statement handle’s batch feed method, fetchall_arrayref() works in the same manner as its other fetch handle methods, in that it needs the prepare and execute stages, but it is flexible enough to work in one of three different ways.

Without arguments, it simply returns a reference to an array of all the data in the same way selectall_arrayref() does. You could rework the code example for selectall_arrayref() like this:

$sth = $dbh->prepare("SELECT country_id, country_name
FROM countries
ORDER BY country_name");
$sth->execute();
my $countries = $sth->fetchall_arrayref();

and the results would be the same.

How Fast is it?
Well unfortunately these methods are usually no faster than using one of the 'fetchrow' methods and doing the iteration yourself and sometimes depending on the DBD they could be very much slower and they usually suck up memory big time,

What is the use for them then?

They do come in very handy though when you want only a few fields in a record set but that is something to look at in Part #8