Ima::DBI - Database connection caching and organization


Ima::DBI - Database connection caching and organization


    package Foo;
    use base 'Ima::DBI';
    # Class-wide methods.
    Foo->set_db($db_name, $data_source, $user, $password);
    Foo->set_db($db_name, $data_source, $user, $password, \%attr);
    my @database_names   = Foo->db_names;
    my @database_handles = Foo->db_handles;
    Foo->set_sql($sql_name, $statement, $db_name);
    Foo->set_sql($sql_name, $statement, $db_name, $cache);
    my @statement_names   = Foo->sql_names;
    # Object methods.
    $dbh = $obj->db_*;      # Where * is the name of the db connection.
    $sth = $obj->sql_*;     # Where * is the name of the sql statement.
    $sth = $obj->sql_*(@sql_pieces);
    $obj->DBIwarn($what, $doing);
    my $rc = $obj->commit;
    my $rc = $obj->commit(@db_names);
    my $rc = $obj->rollback;
    my $rc = $obj->rollback(@db_names);


Ima::DBI attempts to organize and facilitate caching and more efficient use of database connections and statement handles by storing DBI and SQL information with your class (instead of as seperate objects). This allows you to pass around just one object without worrying about a trail of DBI handles behind it.

One of the things I always found annoying about writing large programs with DBI was making sure that I didn't have duplicate database handles open. I was also annoyed by the somewhat wasteful nature of the prepare/execute/finish route I'd tend to go through in my subroutines. The new DBI->connect_cached and DBI->prepare_cached helped a lot, but I still had to throw around global datasource, username and password information.

So, after a while I grew a small library of DBI helper routines and techniques. Ima::DBI is the culmination of all this, put into a nice(?), clean(?) class to be inherited from.

Why should I use this thing?

Ima::DBI is a little odd, and it's kinda hard to explain. So lemme explain why you'd want to use this thing...

Why shouldn't I use this thing.


The basic steps to ``DBIing'' a class are:

  1. Inherit from Ima::DBI

  2. Set up and name all your database connections via set_db()

  3. Set up and name all your SQL statements via set_sql()

  4. Use sql_* to retrieve your statement handles ($sth) as needed and db_* to retreive database handles ($dbh).

Have a look at EXAMPLE below.


Ima::DBI, by default, uses DBI's Taint flag on all connections.

This means that Ima::DBI methods do not accept tainted data, and that all data fetched from the database will be tainted. This may be different from the DBI behavior you're used to. See Taint in the DBI manpage for details.


Class methods

    Foo->set_db($db_name, $data_source, $user, $password);
    Foo->set_db($db_name, $data_source, $user, $password, \%attr);

This method is used in place of DBI->connect to create your database handles. It sets up a new DBI database handle associated to $db_name. All other arguments are passed through to DBI->connect_cached.

A new method is created for each db you setup. This new method is called ``db_$db_name''... so, for example, Foo->set_db(``foo'', ...) will create a method called ``db_foo()''. (Spaces in $db_name will be translated into underscores: '_')

%attr is combined with a set of defaults (RaiseError => 1, AutoCommit => 0, PrintError => 0, Taint => 1). This is a better default IMHO, however it does give databases without transactions (such as MySQL) a hard time. Be sure to turn AutoCommit back on if your database does not support transactions.

The actual database handle creation (and thus the database connection) is held off until a prepare is attempted with this handle.

    Foo->set_sql($sql_name, $statement, $db_name);
    Foo->set_sql($sql_name, $statement, $db_name, $cache);

This method is used in place of DBI->prepare to create your statement handles. It sets up a new statement handle associated to $sql_name using the database connection associated with $db_name. $statement is passed through to either DBI->prepare or DBI->prepare_cached (depending on $cache) to create the statement handle.

If $cache is true or isn't given, then prepare_cached() will be used to prepare the statement handle and it will be cached. If $cache is false then a normal prepare() will be used and the statement handle will be recompiled on every sql_*() call. If you have a statement which changes a lot or is used very infrequently you might not want it cached.

A new method is created for each statement you set up. This new method is ``sql_$sql_name''... so, as with set_db(), Foo->set_sql(``bar'', ..., ``foo''); will create a method called ``sql_bar()'' which uses the database connection from ``db_foo()''. Again, spaces in $sql_name will be translated into underscores ('_'). The actual statement handle creation is held off until sql_* is first called on this name.

To make up for the limitations of bind parameters, $statement can contain sprintf() style formatting (ie. %s and such) to allow dynamically generated SQL statements (so to get a real percent sign, use '%%'). See sql_* below for more details.

  my @database_names   = Foo->db_names;
  my @database_handles = Foo->db_handles;
  my @database_handles = Foo->db_handles(@db_names);

Returns a list of the database handles set up for this class using set_db(). This includes all inherited handles.

db_names() simply returns the name of the handle, from which it is possible to access it by converting it to a method name and calling that db method...

    my @db_names = Foo->db_names;
    my $db_meth = 'db_'.$db_names[0];
    my $dbh = $foo->$db_meth;

Icky, eh? Fortunately, db_handles() does this for you and returns a list of database handles in the same order as db_names(). Use this sparingly as it will connect you to the database if you weren't already connected.

If given @db_names, db_handles() will return only the handles for those connections.

These both work as either class or object methods.

  my @statement_names   = Foo->sql_names;

Similar to db_names() this returns the names of all SQL statements set up for this class using set_sql(), inherited or otherwise.

There is no corresponding sql_handles() because we can't know what arguments to pass in.

Object methods

    $dbh = $obj->db_*;

This is how you directly access a database handle you set up with set_db.

The actual particular method name is derived from what you told set_db.

db_* will handle all the issues of making sure you're already connected to the database.

    $sth = $obj->sql_*;
    $sth = $obj->sql_*(@sql_pieces);

sql_*() is a catch-all name for the methods you set up with set_sql(). For instance, if you did:

    Foo->set_sql('GetAllFoo', 'Select * From Foo', 'SomeDb');

you'd run that statement with sql_GetAllFoo().

sql_* will handle all the issues of making sure the database is already connected, and the statement handle is prepared. It returns a prepared statement handle for you to use. (You're expected to execute() it)

If sql_*() is given a list of @sql_pieces it will use them to fill in your statement, assuming you have sprintf() formatting tags in your statement. For example:

    Foo->set_sql('GetTable', 'Select * From %s', 'Things');

    # Assuming we have created an object... this will prepare the
    # statement 'Select * From Bar'
    $sth = $obj->sql_Search('Bar');

Be very careful with what you feed this function. It cannot do any quoting or escaping for you, so it is totally up to you to take care of that. Fortunately if you have tainting on you will be spared the worst.

It is recommended you only use this in cases where bind parameters will not work.

    $obj->DBIwarn($what, $doing);

Produces a useful error for exceptions with DBI.

I'm not particularly happy with this interface

Most useful like this:

    eval {
        $self->sql_Something->execute($self->{ID}, @stuff);
    if($@) {
        $self->DBIwarn($self->{ID}, 'Something');

Modified database handle methods

Ima::DBI makes some of the methods available to your object that are normally only available via the database handle. In addition, it spices up the API a bit.

=over 4
    $rc = $obj->commit;
    $rc = $obj->commit(@db_names);

Derived from $dbh->commit() and basically does the same thing.

If called with no arguments, it causes commit() to be called on all database handles associated with $obj. Otherwise it commits all database handles whose names are listed in @db_names.

Alternatively, you may like to do: $rc = $obj->db_Name->commit;

If all the commits succeeded it returns true, false otherwise.

    $rc = $obj->rollback;
    $rc = $obj->rollback(@db_names);

Derived from $dbh->rollback, this acts just like Ima::DBI->commit, except that it calls rollback().

Alternatively, you may like to do: $rc = $obj->db_Name->rollback;

If all the rollbacks succeeded it returns true, false otherwise.


    package Foo;
    use base qw(Ima::DBI);
    # Set up database connections (but don't connect yet)
    Foo->set_db('Users', 'dbi:Oracle:Foo', 'admin', 'passwd');
    Foo->set_db('Customers', 'dbi:Oracle:Foo', 'Staff', 'passwd');
    # Set up SQL statements to be used through out the program.
    Foo->set_sql('FindUser', <<"SQL", 'Users');
        SELECT  *
        FROM    Users
        WHERE   Name LIKE ?
    Foo->set_sql('ChangeLanguage', <<"SQL", 'Customers');
        UPDATE  Customers
        SET     Language = ?
        WHERE   Country = ?
    # rest of the class as usual.
    package main;
    $obj = Foo->new;
    eval {
        # Does connect & prepare
        my $sth = $obj->sql_FindUser;
        # bind_params, execute & bind_columns
        $sth->execute(['Likmi%'], [\($name)]);
        while( $sth->fetch ) {
            print $name;
        # Uses cached database and statement handles
        $sth = $obj->sql_FindUser;
        # bind_params & execute.
        @names = $sth->fetchall;
        # connects, prepares
        $rows_altered = $obj->sql_ChangeLanguage->execute(qw(es_MX mx));
    unless ($@) {
        # Everything went okay, commit the changes to the customers.
    else {
        warn "DBI failure:  $@";    

TODO, Caveat, BUGS, etc....

I seriously doubt that it's thread safe.
You can bet cupcackes to sno-cones that much havoc will be wrought if Ima::DBI is used in a threaded Perl.

Should make use of private_* handle method to store information
The docs stink.
The docs were originally written when I didn't have a good handle on the module and how it will be used in practical cases. I need to rewrite the docs from the ground up.

Need to add debugging hooks.
The thing which immediately comes to mind is a Verbose flag to print out SQL statements as they are made as well as mention when database connections are made, etc...


Tony Bowden <>


Michael G Schwern <>


This module is free software. You may distribute under the same terms as Perl itself. IT COMES WITHOUT WARRANTY OF ANY KIND.


Tim Bunce, for enduring many DBI questions and adding Taint, prepare_cached and connect_cached methods to DBI, simplifying this greatly!

Arena Networks, for effectively paying for Mike to write most of this module.


the DBI manpage.

You may also choose to check out the Class::DBI manpage which hides most of this from view.

 Ima::DBI - Database connection caching and organization