byterock

More Data Fetching

In part 5 we look at fetching records for queries into arrays and array refs now we will look at fetching into a hash ref.

fetchrow_hashref
Not a big step here from the ftechrow_arrayref one is simple fetching the name values pairs of the row into a hash ref. So our query could look like this

#!/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 $result = $sth->fetchrow_hashref();

print "My city is ". $result->{city}.". \n";
print "My state is ". $result->{state}.".\n";

$sth->finish();

So as one can see our selected row was converted into a simple hash ref, where the field name is the key and it value is the value returned from the DB.

Aliases

There are a few little things to look out for when using it. For one duplicate field name are returned only once in the hash. So if you have an SQL like this

'SELECT l.city, l.state_province, b.state_province FROM locations l , buyers b where l.city = b.city'

Where you want the one field twice for what ever reason you would need to use a column alias in the SQL to get both. The above would just give you a has with one 'city' key.

This SQL would work;

'SELECT l.city, l.state_province as location_state, b.state_province as buyer_state FROM locations l , buyers b where l.city = b.city'

You will now have a hash that would look like this

{city=>'Ottawa,
location_state=>'ON',
buyer_state=>'PQ'}

Another point to remember is if you are using any aggregate functions in you SQL such as 'MAX', 'COUNT' etc, it is strongly recommended you use aliases in you SQL for the field names as it is the RDMBS and not DBI or DBD that will set the 'NAME' of such fields. If you don't your results may not look like what you expect.

Unlike the array_ref fetch a new hash ref is created for each fetch so it may use more memory. As well there is some extra overhead to construct the hash ref so this is not the fastest way to select you data.