Managing Your DBI Handles

by Chris Josephes


I'm working with a couple of people on a small perl project that relies on a database. When we started, we were all doing our own DBI perl calls and connecting to the same database. Before long, we realized we had multiple connections going from one application to the same source.



The code was broken up in multiple modules, and each module did have a valid reason for connecting to the database, so it didn't make sense to try and eliminate one person's code. One possibility we briefly considered was opening one database handle, and then passing it between the modules. But then there's the risk that the database could grow, and one handle may not properly reference where all of the data is stored.



DBI does not have a solid mechanism for managing multiple connections (not yet, anyways). The connect_cached method is a step in the right direction, but I wanted to see if there was a way I could make it a little more transparant.



After some searching, I found Apache::DBI::Cache, which is similar to Apache::DBI, but it had a couple of additional features. Most importantly, was that it could be used in a standalong script.



All you need to do is use Apache::DBI::Cache before anything else, then call the init() subroutine.



use Apache::DBI::Cache;

Apache::DBI::Cache::init();

$x=DBI->connect("DBI:foo:database=bar","example","example");
$x->disconnect();

$y=DBI->connect("DBI:foo:database=bar","example","example");
$y->disconnect();



Since the database connect methods are involked with the exact same arguments twice, only one handle is used. The second client connection is actually the first connection, because the the disconnect method was overridden.



It follows most of the same conventions as Apache::DBI, but there are one or two additional features thrown in.