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.

BLOBs, Synonyms, and DBD::Oracle

INSERTing, UPDATEing, and SELECTing LOBs (CLOB/BLOB) in Oracle using Perl can be a PITA. Case in point: 2 years ago a fellow coworker ended up writing some extremely complex code using ora_lob_write(), etc, when all he really needed to do was use bind_param() and declare that the blob column is a blob so that DBD::Oracle would know to treat is specially. At the time I believe he did try something like:

my $sth = $dbh->prepare(q[
    INSERT INTO some_table (color, blob_data)
    VALUES (?, ?)
]);
$sth->bind_param( 1, 'red' );
$sth->bind_param( 2, $data_for_blob, {ora_type => ORA_BLOB } );
$sth->execute();

But then an error like “DBD::Oracle::st execute failed: ORA-04043: object some_table does not exist (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch)” was raised. Which made no sense since some_table is very much an object and any number of SQL operations work on it… when BLOBs are not involved. In the end he ended up having to write code that was triple the size to get around this issue.

So, 2 years later and a little hair pulling, I come to figure out by luck that the problem is that the underlying DBD::Oracle code, that makes dealing with BLOBs simple, fails when you are using a synonym in your insert. All we had to do is fully spell out the table name, and it works!

my $sth = $dbh->prepare(q[
    INSERT INTO some_schema.some_table (color, blob_data)
    VALUES (?, ?)
]);
$sth->bind_param( 1, 'red' );
$sth->bind_param( 2, $data_for_blob, {ora_type => ORA_BLOB } );
$sth->execute();

Robust DBI Transaction and Connection Handling

Edit: It turns out there is already a module on CPAN that does exactly what I talked about here. Its called DBIx::Connector. I haven’t tried it yet, but it looks like the guy that wrote it designed it with input from the DBIC guys.

I’m a big fan of DBIx::Class. Among DBIC’s many great features is its superb transaction and connection handling via DBIx::Class::Storage::DBI. When I’m using raw DBI I feel like I’m missing these core components. I should always be able to expect robust transaction and connection handling.

I started playing around with creating a new CPAN module (something like DBIx::Robust, DBIx::Transaction, etc…) but each time I dove in to it I kept coming to the conclusion that DBIC’s DBI storage drivers provide everything I need and I would just be shooting myself in the foot by either porting DBIx::Class::Storage::DBI to a DBIC independent API or by writing it from scratch.

Before I go any further, let me show you how attractive and awesome DBIC’s storage layer is:

Transactions may be nested, even without savepoints.
Transaction savepoints can automatically be tracked allowing for reliable and incremental rollbacks.
The appropriate DateTime::Format class is automatically determined based on the type of database.
All database calls efficiently pass through a layer that detects stale connections that will attempt to re-connect to a database.

“There is no charge for awesomeness, or attractiveness.”

The DBIx::Class::Storage::DBI pod illustrates all this in a round-about way: “If you set AutoCommit => 0 in your connect info, then you are always in an assumed transaction between commits, and you’re telling us you’d like to manage that manually. A lot of the magic protections offered by this module will go away. We can’t protect you from exceptions due to database disconnects because we don’t know anything about how to restart your transactions. You’re on your own for handling all sorts of exceptional cases if you choose the AutoCommit => 0 path, just as you would be with raw DBI.”

This stuff is really powerful, and we should never have to work with databases in Perl without it. But, currently, it is only meant to work under DBIC, which is a shame. I’m betting that DBIx::Class::Storage::DBI could be used directly, bypassing DBIC completely. There is some DBIC stuff layered in there, but that should be ignorable.

What do the rest of you think? Has anyone considered moving this logic out in to a generic distribution that isn’t DBIC specific? Is there merit in what I’m talking about? Does anyone use DBIx::Class::Storage::DBI directly, bypassing DBIC?

bluefeet

Using DBI Effectively: bind_columns()

BLOBs, Synonyms, and DBD::Oracle

Robust DBI Transaction and Connection Handling