Well, let me tell you a funny story. I kept finding that my db hung every time I ran a perfectly functional piece of code, that worked fine before I modularised it. The form of the code was:

# 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]

Well, you probably spotted the problem right away... but it took me a while to figure out that what was happening was that the subroutine in the module was calling DBI methods on a db handle which, yes, had the same "name" as the one in the script (both called $dbh, right?) but actually... was a different db handle.

So I get my lock. Fine. Then, over in the module, we do a call to the db but.. hey, somebody's got a lock on this db, better wait until they're done... waiting... hum de dum, gee that guy sure is taking his time with that db operation... whistle whistle, read a magazine... still locked... oh hell, I'll just shoot myself in despair.

Meantime, back in the script we're waiting for the subroutine to finish... hell, what's going on in there, they've been ages... I want to take the lock off this db, which is bound to be wrecking somebody else's day, but I can't get to that until I get something back from the module... tum de dum... scratches, gets another coffee... looks at memepool... hell, still no answer from that pesky subroutine, I'll just send a connection timed out error and go home for the day.

Update: In case you should want to learn from a couple of other ways to get tangled whilst using DBI, grinder kindly drew my attention to these

So that's the background to my choice of solution - I want something bone simple so that even the bear of smallest brain won't end up with two db handles on the go at once. And, drawing together some of the strands above, the way I'm planning to do it is:

(1) In the script:

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]

and (2) In the module:

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]

Giving the same db handle different names in the script and the module may seem confusing, but it makes sense to me, because it emphasises the fact that they ARE different names, albeit, in this case, mercifully, names for the same thing.

One might also think that the best way to keep from getting one's laces tied together was to pass the db handle as an argument in a subroutine call, and do all the db handle creation in the module. But for portability I like having the db name, password etc in a module; and also I'd like to be sure that the db handle wasn't getting mixed up with the other arguments passed to the subroutine, so that I ended up writing it to my db or something funky like that...

You might also have some sympathy with the view (as perrin) that this is something to manage in MySQL. I like that plan, but against it I see two arguments, both rather specific to me: (1) it reduces portability - I'm just now looking into going to PostGreSQL (2) I don't know much MySQL, and I want to build on the firmest foundations I've got.


I've now incorporated the above into my two main scripts and it seems to work. I'd be very grateful for any tweaking comments, however. And I'm more than grateful for kind advice of monks above.

§ George Sherston