Strategies for DBI access

[Martin Langhoff <ma...@scim.net>]

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

Re: Strategies for DBI access

[Nouguier <ol...@akio-solutions.com>]

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