# phpman > perldoc > Ima::DBI

## NAME
    [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) - Database connection caching and organization

## SYNOPSIS
        package Foo;
        use base '[Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)';

        # 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);

## DESCRIPTION
    [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) 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 separate 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](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) is the
    culmination of all this, put into a nice(?), clean(?) class to be inherited from.

  Why should I use this thing?
    [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) is a little odd, and it's kinda hard to explain. So lemme explain why you'd want to use
    this thing...

    *   Consolidation of all SQL statements and database information

        No matter what, embedding one language into another is messy. DBI alleviates this somewhat,
        but I've found a tendency to have that scatter the SQL around inside the Perl code. [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)
        allows you to easily group the SQL statements in one place where they are easier to maintain
        (especially if one developer is writing the SQL, another writing the Perl). Alternatively,
        you can place your SQL statement alongside the code which uses it. Whatever floats your
        boat.

        Database connection information (data source, username, password, atrributes, etc...) can
        also be consolidated together and tracked.

        Both the SQL and the connection info are probably going to change a lot, so having them well
        organized and easy to find in the code is a Big Help.

    *   Holds off opening a database connection until necessary.

        While [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) is informed of all your database connections and SQL statements at
        compile-time, it will not connect to the database until you actually prepare a statement on
        that connection.

        This is obviously very good for programs that sometimes never touch the database. It's also
        good for code that has lots of possible connections and statements, but which typically only
        use a few. Kinda like an autoloader.

    *   Easy integration of the DBI handles into your class

        [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) causes each database handle to be associated with your class, allowing you to pull
        handles from an instance of your object, as well as making many oft-used DBI methods
        available directly from your instance.

        This gives you a cleaner OO design, since you can now just throw around the object as usual
        and it will carry its associated DBI baggage with it.

    *   Honors taint mode

        It always struck me as a design deficiency that tainted SQL statements could be passed to
        $sth->prepare(). For example:

            # $user is from an untrusted source and is tainted.
            $user = get_user_data_from_the_outside_world;
            $sth = $dbh->prepare('DELETE FROM Users WHERE User = $user');

        Looks innocent enough... but what if $user was the string "1 OR User LIKE '%'". You just
        blew away all your users. Hope you have backups.

        [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) turns on the DBI->connect Taint attribute so that all DBI methods (except
        execute()) will no longer accept tainted data. See "Taint" in DBI for details.

    *   Taints returned data

        Databases should be like any other system call. It's the scary Outside World, thus it should
        be tainted. Simple. [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) turns on DBI's Taint attribute on each connection. This feature
        is overridable by passing your own Taint attribute to set_db as normal for DBI. See "Taint"
        in DBI for details.

    *   Encapsulation of some of the more repetitive bits of everyday DBI usage

        I get lazy a lot and I forget to do things I really should, like using bind_cols(), or
        rigorous error checking. [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) does some of this stuff automatically, other times it just
        makes it more convenient.

    *   Encapsulation of DBI's cache system

        DBI's automatic handle caching system is relatively new, and some people aren't aware of its
        use. [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) uses it automatically, so you don't have to worry about it. (It even makes it
        a bit more efficient)

    *   Sharing of database and sql information amongst inherited classes

        Any SQL statements and connections created by a class are available to its children via
        normal method inheritance.

    *   Guarantees one connection per program.

        One program, one database connection (per database user). One program, one prepared
        statement handle (per statement, per database user). That's what [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) enforces.
        Extremely handy in persistent environments (servers, daemons, mod_perl, FastCGI, etc...)

    *   Encourages use of bind parameters and columns

        Bind parameters are safer and more efficient than embedding the column information straight
        into the SQL statement. Bind columns are more efficient than normal fetching. [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)
        pretty much requires the usage of the former, and eases the use of the latter.

### Why shouldn't I use this thing.
    *   It's all about OO

        Although it is possible to use [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) as a stand-alone module as part of a
        function-oriented design, its generally not to be used unless integrated into an
        object-oriented design.

    *   Overkill for small programs

    *   Overkill for programs with only one or two SQL statements

        Its up to you whether the trouble of setting up a class and jumping through the necessary
        [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) hoops is worth it for small programs. To me, it takes just as much time to set up
        an [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) subclass as it would to access DBI without it... but then again I wrote the
        module. YMMV.

    *   Overkill for programs that only use their SQL statements once

        [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)'s caching might prove to be an unnecessary performance hog if you never use the
        same SQL statement twice. Not sure, I haven't looked into it.

## USAGE
    The basic steps to "DBIing" a class are:

    1   Inherit from [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)

    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 retrieve database
        handles ($dbh).

    Have a look at EXAMPLE below.

## TAINTING
    [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown), by default, uses DBI's Taint flag on all connections.

    This means that [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) 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 DBI for details.

## Class Methods
  set_db
        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 when used with the default MyISAM table type) 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.

  set_sql
        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.

  transform_sql
    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 '%%').

    The translation of the SQL happens in transform_sql(), which can be overridden to do more
    complex transformations. See [Class::DBI](https://www.chedong.com/phpMan.php/perldoc/Class%3A%3ADBI/markdown) for an example.

  db_names / db_handles
      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
    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.

  sql_names
      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

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

## Object Methods
  db_*
        $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.

  sql_*
        $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.

  DBIwarn
        $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');
                    return;
        }

## Modified database handle methods
    [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) 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.

  commit
        $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.

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

    Derived from $dbh->rollback, this acts just like [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown)->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.

## EXAMPLE
        package Foo;
        use base qw([Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown));

        # 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 ?
        SQL

        Foo->set_sql('ChangeLanguage', <<"SQL", 'Customers');
            UPDATE  Customers
            SET     Language = ?
            WHERE   Country = ?
        SQL

        # 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.
            $sth->execute('%Hock');
            @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.
            $obj->commit('Customers');
        }
        else {
            $obj->rollback('Customers');
            warn "DBI failure:  $@";
        }

USE WITH MOD_PERL, FASTCGI, ETC.
    To help with use in forking environments, [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) database handles keep track of the PID of the
    process they were openend under. If they notice a change (because you forked a new process), a
    new handle will be opened in the new process. This prevents a common problem seen in
    environments like mod_perl where people would open a handle in the parent process and then run
    into trouble when they try to use it from a child process.

    Because [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) handles keeping database connections persistent and prevents problems with
    handles openend before forking, it is not necessary to use [Apache::DBI](https://www.chedong.com/phpMan.php/perldoc/Apache%3A%3ADBI/markdown) when using [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown).
    However, there is one feature of [Apache::DBI](https://www.chedong.com/phpMan.php/perldoc/Apache%3A%3ADBI/markdown) which you will need in a mod_perl or FastCGI
    environment, and that's the automatic rollback it does at the end of each request. This rollback
    provides safety from transactions left hanging when some perl code dies -- a serious problem
    which could grind your database to a halt with stale locks.

    To replace this feature on your own under mod_perl, you can add something like this in a handler
    at any phase of the request:

       $r->push_handlers(PerlCleanupHandler => sub {
           MyImaDBI->rollback();
       });

    Here "MyImaDBI" is your subclass of [Ima::DBI](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown). You could also make this into an actual module and
    set the PerlCleanupHandler from your httpd.conf. A similar approach should work in any
    long-running environment which has a hook for running some code at the end of each request.

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](https://www.chedong.com/phpMan.php/perldoc/Ima%3A%3ADBI/markdown) 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...

## MAINTAINERS
    Tony Bowden <<tony@tmtm.com>> and Perrin Harkins <<perrin@elem.com>>

## ORIGINAL AUTHOR
    Michael G Schwern <<schwern@pobox.com>>

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

## THANKS MUCHLY
    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.

## SEE ALSO
    DBI.

    You may also choose to check out [Class::DBI](https://www.chedong.com/phpMan.php/perldoc/Class%3A%3ADBI/markdown) which hides most of this from view.