Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Ima::DBI(3)	      User Contributed Perl Documentation	   Ima::DBI(3)

       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

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

       o   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 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

	   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.

       o   Holds off opening a database	connection until necessary.

	   While Ima::DBI 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.

       o   Easy	integration of the DBI handles into your class

	   Ima::DBI 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.

       o   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

	   Ima::DBI 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.

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

       o   Encapsulation of some of the	more repetitive	bits of	everyday DBI

	   I get lazy a	lot and	I forget to do things I	really should, like
	   using bind_cols(), or rigorous error	checking.  Ima::DBI does some
	   of this stuff automatically,	other times it just makes it more

       o   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 uses it automatically, so
	   you don't have to worry about it. (It even makes it a bit more

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

       o   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	enforces.  Extremely handy in
	   persistant environments (servers, daemons, mod_perl,	FastCGI,

       o   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 pretty much
	   requires the	usage of the former, and eases the use of the latter.

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

	   Although it is possible to use Ima::DBI 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.

       o   Overkill for	small programs

       o   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 hoops	is worth it for	small
	   programs.  To me, it	takes just as much time	to set up an Ima::DBI
	   subclass as it would	to access DBI without it... but	then again I
	   wrote the module.  YMMV.

       o   Overkill for	programs that only use their SQL statements once

	   Ima::DBI's caching might prove to be	an unecessary performance hog
	   if you never	use the	same SQL statement twice.  Not sure, I haven't
	   looked into it.

       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 DBI for

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

	   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

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

       The translation of the SQL happens in transform_sql(), which can	be
       overridden to do	more complex transformations. See Class::DBI for an

   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() 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

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

       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

       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

       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.

	   $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:  $@";

       To help with use	in forking environments, Ima::DBI 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	handles	keeping	database connections persistent	and
       prevents	problems with handles openend before forking, it is not
       necessary to use	Apache::DBI when using Ima::DBI.  However, there is
       one feature of Apache::DBI 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 {

       Here "MyImaDBI" is your subclass	of Ima::DBI.  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

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 <> and Perrin Harkins <>

       Michael G Schwern <>

       This module is free software.  You may distribute under the same	terms

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

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


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

perl v5.32.0			  2007-06-10			   Ima::DBI(3)


Want to link to this manual page? Use this URL:

home | help