That said, you can still do one single dbh connection, and simply modify your subs in the separate files to handle this concept. That is, instead of a sub like this:

sub
Write_Bar
{
$dbh->do("INSERT INTO bar (Foo,Fob,Fib) VALUES ('baz','biz','boz')");
}
[download]

sub
Write_Bar
{
my $dbh = shift;
$dbh->do("INSERT INTO bar (Foo,Fob,Fib) VALUES ('baz','biz','boz')");
}
[download]

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

Some possibilities:

• pass $dbh as a parameter
• Have a `setDBIhandle' routine that stashes the handle subs should use in a package variable
• wrap things up in a class and make the handle an instance variable
• just use $main::dbh and note the dependency (sometimes globals can make sense)

Aside from Web Server-specific interfaces for persistent database connections like Apache::DBI, I was just looking at Ima::DBI, which might just solve some of the problems you have. According to the docs, amongst it's features are:

* Sharing of database and sql information amongst inherited classes
Any SQL statements and connections created by a class are available to its children via normal method inheritance.

* Guarantees one connection per program.
One program, one database connection (per database user). One program, one prepared statement handle (per statement, per database user). That's what Ima::DBI enforces. Extremely handy in persistant environments (servers, daemons, mod_perl, FastCGI, etc...)

----Asim, known to some as Woodrow.

I handled this once by having the module create and tear down the db handle, via BEGIN and END clauses, and when I needed the use the handle directly in my main script, I simply used something like this:

package My::Module;

my $db;
BEGIN {
  $db = DBI->connect($blah, $blah, $blah) or die "...\n";
}
END {
  $db and $db->disconnect;
}

sub db {
  $db;
}
[download]

Which meant that I could grab the handle whenever I needed to do some ad-hoc querying, and have the module take care of the fiddly bits, as well as all the standard db stuff it was supposed to do anyway, all nicely encapsulated. You can actually do what appears to be rather weird...

my $ss = My::Module::db->prepare($sql);
[download]

I.e., nary a scalar in sight, but it does what you expect, without having to explicitly save the $db reference in a scalar. It's not exactly walking into some-one's living room without being invited, but it's close to leaning on the window sill and watching the TV set.

I'm not sure I'd do this in a codebase with multiple people, but it worked well enough for me.

g r i n d e r

Step 1. Connect to the database, and get a database handle. You could do this in your main script, or in the module. If you do it in the module, have it return the database handle to the main script, like this:

In the module:
sub george_connect {
    return DBI->connect('yada yada');
}
In the main script:
my $dbh = george_connect;
[download]

Step 3. Disconnect in module or main script.

hth,
andy.

update: works fine with Apache::DBI

Actually, going further with this, if you grab your dbh handle from a function in the 'main' part of the module, you can create a so-called singleton-like system, thus instead of:

sub george_connect {
    return DBI->connect('yada yada');
}
[download]

you can do:

my $dbh;
sub george_connect {
    if ( !$dbh ) {
       $dbh = DBI->connect('yada yada');
    }
    return $dbh;
}
[download]

which maintains a handle one created, but never tried to reconnect it again.

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

# declare a db handle
# get a lock on the table
# call a subroutine from the module that does something to the db
# unlock the table
[download]

use dbmodule;
my $dbh;
BEGIN {$dbh = DBModule_Start}
END {DBModule_Finish}

# then do various stuff including
$sth = $dbh->prepare("BLAH BLAH") die $dbh->errstr;
$sth->execute or die $dbh->errstr;
DBModule_Sub1;
[download]

my $module_dbh;

sub
DBModule_Start
{
    unless ($module_dbh) {
       $module_dbh = DBI->connect("DBI:mysql:database=db_name", "user_
+name", "password");
    }
    return $module_dbh;
}

sub
DBModule_Sub1
{
    $module_dbh->do{'BLAH BLAH BLAH'} or die $module_dbh->errstr;
}

sub
DBModule_Finish
{
    $module_dbh->disconnect;
}
[download]

#!/usr/bin/perl -Tw

use strict;
use warnings;

my $dbh = self->new("DBI:mysql:database=test", undef, undef);

#   Reference database handle via self package
#
$dbh->execute("select * from users");

#   ... or ...

#   Reference database handle methods directly from calling script
#
$dbh->{dbh}->do("select * from users");

exit 0;


package self;

use DBI;
use strict;

sub new {
    my $param = shift;
    my $self = {};
    bless ($self, (ref($param) || $param));
    $self->{dbh} = DBI->connect(@_);
    return $self;
};

sub execute {
    my $self = shift;
    return $self->{dbh}->do(@_);
};

1;

__END__
[download]

This code is fairly straight-forward in that a class self is created and returned to the calling namespace. This class can be accessed either within the class-space as per the execute method or via dereference to the included references to other classes ($self->{dbh}).

Ooohhh, Rob no beer function well without!

sub SomeDBRoutine {
  my $self = shift;
  $obj->dbh->WhatEver;
}

sub dbh {
  my $self = shift;
  if ( @_ ) {
    $connection_string = shift;
    $self->{dbh} = DBI->Connect($connection_string, "user","pass");
  }
  return $self->{dbh};
}
[download]