byterock

Fetching Data

In part 4 we look at queries that do not return rows now we are going to have a good look at fetching data.

You might remember in Part 1 you saw the fetchrow() method used to get at our data -- or "record set", to use the proper RDBMS term -- that has been returned via the Driver.

This is of course familiar to all RDBMS programmers as it is the similar to a cursor, in that we iterate though the returned records sequentially, processing each as we move along until none are left.

fetchrow

The fetchrow() method works by getting the current row from the record set and binding the values from the record set into specific Perl variables. So given this code:

#!/usr/bin/perl
use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $city = 'Seattle';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city, state_province, country_id FROM locations
WHERE city = :p_city';
$sth = $dbh->prepare($sql);
$sth->bind_param(':p_city', $city);
$sth->execute();

my ($r_city, $r_state) = $sth->fetchrow();

print "My city is $r_city. \n";
print "My state is $r_state.\n";

$sth->finish();

you would get this result:

$ perl test_4.pl
My city is Seattle.
My state is Washington.

Note how in our SQL statement we were also selecting country_id but we did not use it when we fetched the data from the result set. If I changed to code to this:

my ($r_city, $r_state, $r_country) = $sth->fetchrow();

we could then also print out the country value.

fetchrow_array

Using fetchrow() in this manner quickly gets very tedious when you are returning a large number of fields from a database. Fortunately, fetchrow is just an alias for the fetchrow_array() method which returns the row as an array. It work the same way as fetchrow() where we name the values to be fetched individually.

my ($r_city, $r_state, $r_country) = $sth->fetchrow_array();

Or we could fetch all the data into a Perl array.

my (@a_row) = $sth->fetchrow_array();

The code to print this would look like this:

print "$a_row[0] \n";
print "$a_row[1]\n";
print "$a_row[2]\n";

(Note that this method always returns a 0--based array.)

fetchrow_arrayref

You also have the option of using the 'fetchrow_arrayref' or its alias just 'fetch' which works exactly the same however it returns as the name suggest a reference to an array.

If you are like me an love speed then 'fetch' is what you want to use as it is a little faster however it you do use it remember that the same reference is reused for each fetch so don't store these values as you will not get the results you want.

You Can't Get There From Here

With fetching it is always a one way trip. You can only iterate forward though the records that the STH has returned so you can't get back. Some Drivers do allow you to go forward and back though a cursor but these are not part of the DBI spec and are Driver specific, DBD::Oracle is one that does have a scrollable record set like this.