Sharing DBI handler between several modules

Hue-Bond has asked for the wisdom of the Perl Monks concerning the following question:

In a program I'm writing, I want to share the DBI database handler between several modules. That way, the functions in that modules could access the database directly. Is this the right approach? How could it be achieved?. I've thought of something like:

package Hue::SQL;
sub open { return DBI->connect blah blah; }
sub close { $main::dbh->disconnect; }          ##  <-- here is it: use
+ $main::dbh

package Hue::Foo;
sub do_something {
    my $data = shift;
    $main::dbh->do ("sql query using \$data"); ##  <-- again
}

package main;
use Hue::SQL;
use Hue::Foo;
$dbh = Hue::SQL::open;
Hue::Foo::do_something ($data);
[download]

But this doesn't seem very clean to me. Another approach is to pass the $dbh as an argument to close(), do_something() and every function that needs it, but I'm not happy with that either. I thought about the possibility of all modules importing $dbh from main, like use does, so $Hue::SQL::dbh, $Hue::Foo::dbh and $main::dbh would be the same, using references or something. Is this feasible? Any other idea?

--
David Serrano

Comment on Sharing DBI handler between several modules Download Code

Replies are listed 'Best First'.
Re: Sharing DBI handler between several modules by gellyfish (Monsignor) on Apr 28, 2006 at 13:05 UTC
In the past I have used a 'Singleton Class' to do a similar sort of thing, something like this: `sub new { my ( $class ) = @_; + if (! defined $UsageDB::_instance ) { $UsageDB::_instance = $class->_init(); } + return $UsageDB::_instance; } + sub _init { my ( $class ) = @_; + my $self = bless {}, $class; + # get configuration for connection $self->{_handle} = DBI->connect($dsn,$user,$pass, {PrintError => 1, RaiseError => 1} +); return $self; } + =item handle + Return the connected DBI handle to the usage DB + + =cut + sub handle { my ( $self ) = @_; + return $self->{_handle}; }` [download] (This is cut straight from working code so is missing part). Then each place the DB connection is needed they can call `UsageDB->new()` and use `handle()` to get the database handle. The advantage of doing this is that the modules that use this don't have to know the implementation details at all and you don't have to interfere with any other packages namespace. /J\	[reply] [d/l] [select]
Re: Sharing DBI handler between several modules by derby (Abbot) on Apr 28, 2006 at 13:03 UTC
I wouldn't do it that way. If your just using packages (and not OO), then look into DBI's connect_cached (hopefully your DBD supports it) and let each package/sub make it's own connection. Otherwise, I would pass the dbh to the constructors (and/or methods). `Hue::Foo::do_something( dbh => $dbh, data => $data );` [download] update: Oops ... forgot to say this is an area where an OO approach would work best. `package Hue::SQL; sub new { return dbi->connect blah blah; } package Hue::Foo { sub new { my $class = shift; my %args = @_; my $self = {}; bless $self, $class; $self->{dbh} = $args{dbh}; $self; } sub do_something { my $self = shift; my %args = @_; $self->{dbh}->do( "sql query using $args{data}" ); } package main; use strict; use warnings; use Hue::SQL; use Hue::Foo; my $dbh = Hue::SQL->new(); my $foo = Hue::Foo->new( dbh => $dbh ); $foo->do_something( data => $data );` [download] -derby	[reply] [d/l] [select]
Re: Sharing DBI handler between several modules by kwaping (Priest) on Apr 28, 2006 at 15:02 UTC
Here's some pseudo-psuedocode that represents what I am doing to share a DBI handle among my local modules. `unless ($local_package::dbh) { $local_package::dbh = [DBI connect stri +ng] or die [DBI error] }; my $dbh = $local_package::dbh;` [download] I put these two lines in every module, like `local_package::do_this` and `local_package::do_that`. It may not be the optimal solution, but it (works \| appears to work) for me. --- It's all fine and dandy until someone has to look at the code.	[reply] [d/l] [select]
Re: Sharing DBI handler between several modules by pajout (Curate) on Apr 29, 2006 at 15:51 UTC
Imho, it depends on ability of such modules to act with database simultaneously. If your application is multithreaded or multiprocessed, I advice some connection pool, for instance SQL Relay. But if you are totally sure that only one module is able to do something in the database always, something like following code is enough, of course, if the only one connection is required... Just idea which I used in some projects - modules can do `my $dbh = MyDB::getDbh();` `package MyDB; use DBI; our $dbh; sub getDbh { if (defined $dbh) { $dbh->ping; } else { DBI->connect(.....); } return $dbh; }` [download]	[reply] [d/l] [select]
Re: Sharing DBI handler between several modules by mattr (Curate) on May 01, 2006 at 02:09 UTC
FWIW, users of Class::DBI do this all the time by creating the database handle in one module like WebApp::DBI and then each of your modules inherit from that module with something like use base 'WebApp::DBI'.	[reply]


XP is just a number
	PerlMonks