One of the most under-utilized features of DBI is is the bind_columns() method. The majority (as in everyone) that I show bind_columns() to have never seen it before or used it. Here’s what it looks like:
my $sth = $dbh->prepare('SELECT name, email FROM users');
$sth->execute();
$sth->bind_columns( \my( $name, $email ) );
while ($sth->fetch()) {
print "$name: $email\n";
}
bind_columns() takes a list of scalar references as its arguments. The form you see above with \my(…) is just a little known Perl-ism shortcut for:
my ($name, $email);
$sth->bind_columns( \$name, \$email );
There are two important reasons why bind_columns() is so awesome:
First, it greatly reduces the complexity of the code within the while() loop since you do not have to lookup in to an array ($sth->fetchrow_array()), de-reference an array ($sth->fetchrow_array()), or de-reference a hash-ref ($sth->fetchrow_hashref()). Instead the values themselves are available via appropriately named scalars.
Secondly, when using bind_columns() DBI is re-using the same scalars every time a fetch() is done which is much faster than creating an array or hash every fetch and typically causes the values to be copied one less time than normal. Benchmark it yourself - bind_columns() can make a huge difference when processing large sets of data.
