Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have a quick question concerning miminmizing/passing database connections. Basically, I have a about 3,000 lines of code broken into various subroutines. I would like to parse similar subroutines into packages so I can keep track of the code. However, the way I have the program run now is that everytime the script is instantiated, a database connection is readied as a global variable, I then I start calling subroutines.It looks like this..
$dbh=new db connection
#some code here that calls subroutines
sub test{
$sth=$dbh->prepare(etc..)
}
sub test2{
$sth=$dbh->prepare(etc..)
}

[download]
This way I can call multiple subroutines that use the db without having to constantly create db connections.
If I start packaging up the subroutines and calling them from a main script, I won't be able to maintain a global database connection. I really want to avoid, creating an individual db connection in each subroutine. This isn't very efficient right? Is it acceptable to create a db connection in the main portion of the script, then pass it to the subroutines in the packages?
for instance,
$dhb=new db connection
Test::Package->($dbh); #call sub with db connection

[download]
Is there a better way to do this?

Replies are listed 'Best First'.
Re: Database Connections
by Fastolfe (Vicar) on Jan 15, 2001 at 19:43 UTC
    You might decide to do something like this: 
    package DBHandle;
use DBI;
my $dbh;

sub new {
   $dbh ||= DBI->connect(...) or die "...";
   return $dbh;
}

# and everywhere you need it:

my $dbh = new DBHandle;

    [download]
    It might even be clearer to future maintainers of your code if you used 'get' instead of 'new'.
[reply]
[d/l]
      I like this suggestion. Following it along, I'd suggest making sure to extend this notion to separating as much of your database connection logic into one package and your program logic into another. This approach is more flexible long-term (changing databases doesn't require dealing with code entangled in the program logic) and makes it easier to develop your code in the present (by not having to try and debug DB logic and program logic at the same time because they are intermingled).
[reply]
      My question about the above approach is that I would still be creating a db connection everytime I need to use the db, right? In other words, if for one script instantiation I call two sub routines, test1 and test2 and they both use the db then they both will be creating two separate db connections, right? 
      test1();
test2();
sub test1
{
 $dbh=new MyDBLayer;
$dbh->do(etc..);
}
sub test2
{
 $dbh=new MyDBLayer;
$dbh->do(etc..);
}

      [download]
      It is best to minimize the number of times you have to make a db connection right? If I do it this way though, I only make one db connection but use it in two subroutines. 
      $dbh=new db connection;
test1($dbh);
test2($dbh);
sub test1
{
 
$dbh->do(etc..);
}
sub test2
{
 
$dbh->do(etc..);
}

      [download]
      Is it wrong to do it this way?
      Thanks.
[reply]
[d/l]
[select]
        No, they'll be returning the same $dbh. The use of the ||= operator is equivalent to $dbh = $dbh || DBI->connect(...);. Thus, $dbh will be connected the first time (since it hasn't been defined yet), but will simply be re-used each additional time the function is called.
[reply]
[d/l]
[select]
        Actually, Fastolfe's suggestion allows you to only create a single DB connection for the life of the script.

        The first time new MyDBLayer is called, the variable $dbh will be undef, so a database connection will be created. The second time new MyDBLayer is called, $dbh will already hold a database object; that object will be returned, instead of a new connection being made.

        The key is the use of the ||= operator; a new database connection will be made only if $dbh doesn't already hold a database object.

        Personally, I've used the approach of passing a database connection into a subroutine, but I really like Fastolfe's approach.

[reply]
[d/l]
[select]
Re: Database Connections
by merlyn (Sage) on Jan 15, 2001 at 19:48 UTC
    Well, from a design perspective, you could either let the main code "own" the database handle, as you've done, or let the test package "own" the database handle as a package variable that gets lazily initialized: 
    package main;
Test::Things::test1();
Test::Things::test2();
..
package Test::Things;
use vars qw($dbh);
sub test1 {
  $dbh ||= Database::Manager::get_dbh();
   ..
}
sub test2 {
  $dbh ||= Database::Manager::get_dbh();
  ..
}
..
package Database::Manager;
sub get_dbh {
  return DBI->connect($dsn, $user, $password, ...);
}

    [download]
    Something like that.

    -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]

Back to Seekers of Perl Wisdom