byterock

Get Just What You Want

In part 7 we used fetchall_arrayref to get an entire resultset into an array ref. One nice thing that DBI adds with this method it the ability to get just the fields we want and not the entire row.

So if where given this this SQL

SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id FROM employees

and from this were are required only want the first, third, second, and the fifth column, we can invoke fetchall_arrayref() to cut down on the amount of space our array ref may take up.

So what we want is simply invoke fetchall_arrayref() like this:

my $employees = $sth->fetchall_arrayref([0,2,1,5]);

Our final code would look like this:

$sth = $dbh->prepare("SELECT Employee_Id ,First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id FROM employees"); $sth->execute(); my $employees = $sth->fetchall_arrayref([0,2,1,5]);

foreach my $row (@$employees){
print "My employee id is $row->[0], my name is $row->[1], ";
print "$row->[2] and I started on $row->[3].\n";
}

We can use even Perl array indices specifying a range of columns like this [0 ... 6] (columns one to six) or even negative indices like this [-2, -1] (last two columns).

Easy on the Eyes

Since all of us like to write code that is easy to read and understand, the fetchall_arrayref() method also lets us use the actual column names rather than confusing indices, by mapping the SQL column names to an anonymous hash (employee_id=>1). So for our example above, we could substitute the following:

my $employees = $sth->fetchall_arrayref({
employee_id=>1,
hire_date=>1,
first_name=>1,
last_name=>1,});

foreach my $row (@$employees){
print "My employee id is $row->{employee_id}, ";
print "my name is $row->{last_name}, $row->{first_name} ";
print "and I started on $row->{hire_date}.\n";
}

and get the same results. As you can see, this form of data return is very convenient, as is does not care in which order the mapping is done or how it is pulled out of the reference array. You need only make a call for the appropriate key, some of the 'Dark majik' found in DBIx::Class maybe?. .

Two important points to remember when working with hash key values are:

1. SQL column names are always treated in lower case, so the case in the query is ignored.
2. If your SQL statement has columns with the same name, your returned hash reference will have only a single value for that key.