Programming the Perl DBI

Slice hash reference argument

The final way that fetchall_arrayref( ) can be used is to selectively store columns into an array reference by passing a hash reference argument containing the columns to store. This is similar to the fetchrow_hashref( ) method but returns a reference to an array containing hash references for all rows in the result set.

If we wished to selectively store the name and location columns from an SQL statement declared as:

SELECT name, location, mapref
FROM megaliths

we can instruct fetchall_arrayref( ) to store the appropriate fields by passing an anonymous hash as an argument. This hash should be initialized to contain the names of the columns to store.

For example, storing the name and location columns can be written easily as:

### Store the name and location columns
$array_ref = $sth->fetchall_arrayref( { name => 1, location => 1 } );

The data structure created by fetchall_arrayref( ) running in this mode is a reference to an array of hash references, with each hash reference keyed by the column names and populated with the column values for the row in question. Traversing this data structure is quite straightforward. The following code illustrates a technique to do it:

#!/usr/bin/perl -w # # ch05/fetchall_arrayref/ex3: Complete example that connects to a database, # executes a SQL statement, then fetches all the # data rows out into a data structure. This # structure is then traversed and printed. use DBI; ### The database handle my $dbh = DBI->connect( ...