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

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]

Back to Seekers of Perl Wisdom