NAME

Ima::DBI - Makes an object be the database connection

SYNOPSIS

  package Foo;

  use Ima::DBI;
  @ISA = qw(Ima::DBI);

  # Set up the database connections used, but don't actually connect, yet.
  Foo->setConnection('Users', 'dbi:Oracle:STAR', 'UserAdmin', 'b4u3');
  Foo->setConnection('Customers', 'dbi:Oracle:STAR', 'Staff', 'r3d0dd');

  Foo->setStatement('FindUser', 
					'SELECT * FROM USERS WHERE name = ?','Users');
  Foo->setStatement('AddUser', $AddUserSQL, 'Users');
  Foo->setStatement('AlterCustomer', 
					'UPDATE CUSTOMERS SET language = ? 
                     WHERE country = ?', 'Customers');


  sub new {
    # the usual stuff
  }

  
  package main;

  $obj = Foo->new;
  
  eval {
    $obj->sql_FindUser->execute(['Butch'], [\($name)]);  # bind and execute.
    while( $obj->sql_FindUser->fetch ) {  # fetch
      last if $name eq 'Cassidy';
    }
  
    # Only does $sth->bind_cols, execute and $sth->fetchall_array, since 
    # we're already connected, and prepared...
    $obj->sql_FindUser->execute(['Sundance']);
    @row = $obj->sql_FindUser->fetchall;
    
    # binds and executes the UPDATE statement.
    $rowsAltered = $obj->sql_AlterCustomer->execute(['es', 'mx']);
  };
  unless ($@) {
    # commit will figure out which DBH's to commit.
    $obj->commit('FindUser', 'AlterCustomer');
  }
  else {
    $obj->rollback('FindUser', 'AlterCustomer');
    warn "We've failed you!:  $@";
  }

  # $obj will disconnect its db's automatically upon destruction.

PUBLIC METHODS

setConnection

  Module::Name->setConnection($connectionName, $dataSource, 
		                      $username, $password) ||
    warn "We already have a connection with the name $connectionName";

Tells how to connect to a database. This connection will be refered to by its $connectionName. The rest is passed to DBI/connect, eventually.

It will return undef if there is already a connection with this $connectionName, a true value otherwise.

Note: This does -not- actually connect to the database. Connections are defered until an sql_*() method is called. Eventually this will do something more clever, like the dbh checkin/checkout system described in the new TPJ.

Eventually it will use the DBI->connect_cached that's been promised for a while.

setStatement

Module::Name->setStatement($statementName, $sql, $connectionName) ||
   warn "We couldn't make the statement $statementName";

Sets up an SQL statement and access method on the database specified by $connectionName, which was set up by setConnection(). The access method is named "sql_$statementName()". Returns undef if a statement with this name has already been created, true otherwise.

ie. Module::Name->setStatement('Bubba', 'select * from table', 'Data'); creates a method called sql_Bubba().

sql_*

$sth = $obj->sql_name;

Access method to an SQL statement, set eariler with setStatement(). Preperation of the statement is handled for you.

Returns a statement handle(). While you can use this handle directly, the prefered method of using sql_* is not to do this. Use it as "$obj->sql_name->sth_method" where sth_method is one of the methods outlined in "statement handle".

commit

$obj->commit;
$obj->commit(@connectionNames);

Wrapper around DBI/commit.

Ima::DBI shuts off AutoCommit by default. If you want to turn it back on... well, there's no good way to do that just yet. Deal. So you must commit your changes.

If called with no args, it commits all open database handles available to it, via DBI/commit (this may be a Bad Thing). If given arguments, it will commit only on those @connectionNames (as created with setConnection).

commit will die if it is given an invalid connection name. It should probably do something more pleasent.

rollback

$obj->rollback;
$obj->rollback(@connectionNames);

Wrapper around DBI/rollback.

Acts exactly like "commit".

Ima::DBI::st - DBI statement handle subclass.

sql_*() returns a special subclass of the normal DBI/statement handle which works very much like a normal DBI/statement handle with a few differences outlined here.

You should avoid using the statement handle directly, but should instead access it through the sql_*() method.

Modified Statement Handle Methods

Unless otherwise specified here, all methods available to a normal DBI Statement Handle operate normally.

The below examples all use "sql_name", but any sql_*() method can be used.

execute

$obj->sql_name->execute(\@bind_params, \@bind_columns);

Execute performs all the functions of DBI/bind_param, DBI/bind_col and DBI/execute without you worrying your pretty little head about such things.

@bind_params is fed to DBI/bind_param one at a time, roughly equivalent to...

for (1..@bind_params) { 
  $sth->bind_param($_, $bind_params[$_]);
}

or simply

$sth->execute(@bind_values);

If @bind_params is formated as a list of lists, then it is used as follows...

for(1..@bind_params) { 
  $sth->bind_param($_, $bind_params[$_][0], $bind_params[$_][1]);
}

First element is the bind value, the seconds would be a hash reference for the DBI/\%attr.

@bind_columns is used in a manner similar to @bind_params, but it feeds to "bind_col" in DBI. In the future it may feed to "bind_columns" in DBI, but I need to figure out the differences between the use of \%attr between the two.

fetch

$row_ref = $obj->sql_name->fetch;
@row     = $obj->sql_name->fetch;

This implements a context sensitive version of "fetch" in DBI. If used in scalar context it will act as "fetchrow_arrayref" in DBI. If used in list context it will act as "fetchrow_array" in DBI.

Makes more sense to me.

fetchall

$tbl_ref = $obj->sql_name->fetchall;
@tbl     = $obj->sql_name->fetchall;

If used in scalar context it will act as "fetchall_arrayref" in DBI. If used in list context it will return an array of rows fetched. This is, of course horribly inefficient and is provided more for convenience and orthigality with "fetch" than anything else.

fetchall_hashref

$tbl_ref = $obj->sql_name->fetchall_hash;
@tbl     = $obj->sql_name->fetchall_hash;

This method acts very much like fetchrow_hashref(), except that it slurps down the every row of the fetch, as fetchall does, but into an array of hashes, rather than an array of arrays.

Like fetchall(), it is sensitive to the context, so if used in list context it returns an array of hashes. In scalar context it returns a reference to an array of hashes (I don't know how much that really buys you.)

CAVET SCRIPTOR! fetchrow_hashref is bad enough as a potential memory/performance hog, fetchall_hashref has the potential to -really- hose things if used on large fetches. It's ment as a convenience, don't abuse it too badly.

TODO

Unstable interface.

I haven't decided if I like the way this module works, so expect the worst. The interface will change.

Checkin/checkout system for $dbh's.

DBI::dh needs to be subclassed. connect(), disconect() to work with the checkin/out system.

Clean up unused $sth's.

OO EVILNESS

Ima::DBI contains no OO Evilness. No new items are added to your object's hash... in fact, it doesn't even assume that your object will be modeled as a hash.

Please examine Ima::DBI::setStatement()'s code and let me know if you have a better way to accomplish this.

DEPENDENCIES

DBI

AUTHOR

Michael G. Schwern <schwern@starmedia.net> for Starmedia Networks