in reply to passing DBI database handles to subroutines

I do this one a little differently than most people: 
{
  my $dbh;
  sub get_db_prod {
    unless (defined ($dbh)) {
      $dbh =  DBI->connect(
        ('DBI:Sybase:' . prod_server),
        prod_user, prod_pass,
        {PrintError => 0, AutoCommit => 0}
      ) or die $DBI::errstr;
    }
    return $dbh;
  }
}

[download]
Previously I had already done a "use constant" to create constants for prod_server, prod_user and prod_pass. I won't paste that code here. (VBG) But the key trick is that you can ask for the connection as many times as you want, from wherever you want. The first time you need it it will spring into existence and from then on you get the memoized instance. Bye bye global variable. :-)

Note that I use a wrapper for errors, so I set PrintError to 0, you may want to change that default. Look at the DBI docs to decide what defaults make sense to you.

A big win with the above code. This is really, really nice in programs which may or may not need a database connection, but if they do will need it in several places. Just place calls to the above function wherever you need it and don't worry about any custom logic for whether or not to initiate a connection. I have some scripts that this idea has really simplified. Depending on what you are trying to do, YMMV.

And to answer your final question, building and destroying connections is very expensive, maintaining them is pretty cheap. So you want to open a connection and not close it again if you can at all help it.

Replies are listed 'Best First'.
RE: RE: passing DBI database handles to subroutines
by merlyn (Sage) on Aug 09, 2000 at 23:15 UTC
    Make sure your named subroutine is inside a BEGIN block, like so: 
    BEGIN {
  my $dbh;
  sub get_db_handle {
    ..
    ..
    return $dbh;
  }
}

    [download]
    If you fail to do that, and manage executing that block twice in a program, you'll get the wonderful $dbh won't stay shared error, indicating some serious bad voodoo.

    -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]
      Right you are of course.

      OTOH another way to guarantee the right behaviour is to put the subroutine in a module. And that module not coincidentally in my case is where the name and password are, making it easy to change the password periodically.

      Of course dealing with mod_perl you probably get to curse the "won't stay shared" error much more than I do. :-)

[reply]
        With mod_perl, you are likely best just making $dbh a package var of a "library module", that everything use's Then the "won't stay shared" is avoided.
        I have quite a bit of code that does this, basically its just wrappers on all the database calls that automagically connect and stay connected, but only a single connection required for each apache process.
[reply]

In Section Seekers of Perl Wisdom