Mailing List Archive: Strategies for DBI access

hi,

after developing with embperl for a few months i'm starting to
develop a new site and I'm going to try the custom-module + simple calls
from embperl pages, instead of developing inside embperl pages. Now all
the site is heavily dependant on DBI connections. I do use Apache::DBI
but I'd really like to be able to disable it at will. I guess I have 2
choices:

- put a 'connect' and 'disconnect' inside every embperl page. It's
the 'right way', explicitly connecting at the top of evey page and
disconnecting at the bottom. But I don't really want to, as it's not
really elegant nor practical. If I do forget one disconnect, Apache::DBI
will cover my tracks ... until the day I disable it and one of the
gazillion sites I'm hosting here starts leaking dbi-handles .. not a
nice thing to debug, really.

- put a 'connect' and 'disconnect' inside every subroutine. it's
much more 'Apache::DBI' dependant, as Apache::DBI will cache connections
and do a no-op upon disconnect. It's much easier. But performance will
be horrible if I drop Apache::DBI.

what strategies are you (with more experience than me) using? am I
messing myself up or explaining it all backwards?

martin

hi martin
I'm not a expert but here what 've done

package MyConn;

use strict;

use vars qw( $dbh ); # cached connection, one by apache child

my $dsn = "dbi:postgresql:dbname=mydb";

my $user = "myuser";
my $password = "mypass";

sub getCached {
my $pkg = shift;
if ( $dbh ){
if ( $dbh->ping() ){
return $dbh;
}
}
$dbh = DBI->connect($dsn,$user, $password, { AutoCommit => 1, RaiseError
=> 1 } )||
die "ERROR NO_CONNECTION_TO_POSTMASTER\n";
return $dbh;
}

It sound like Apache::DBI.

When I need a cached connection in another package, I call
MyConn->getCached()

$dbh = MyConn->getCached();

Then I should implement:

sub get { #uncached connection ( for transaction...)
my $dbh2 = DBI->connect($dsn,$user, $password, { AutoCommit => 1,
RaiseError => 1 } )||
die "ERROR NO_CONNECTION_TO_POSTMASTER\n";
return $dbh2;
}

sub END { # To close cleanly...
if ( $dbh && $dbh->ping() ){
$dbh->disconnect();
}
}

but I working on this ...

Hope its help