On Sep 13, 2004, at 8:30 AM, Martin Moss wrote:
All,
I'm trying to understand the Apache::DBI mechanism which caches and returns $dbh handles. My concern is that Apache DBI uses the connection options, like AutoCommit=>1 as part of it's caching 'key' mechanism.
Yes it does, it uses the sorted list of connection options.
Thus would I be right in thinking that if I create a $dbh with AutoCommit=>1, this gets cached, but then I later turn AutoCommit off (AutoCommit=>0) that the database handle given to the next user that comes along and requests a $dbh with AutoCommit=>1, actually gets the $dbh, which originally had AutoCommit->1 but was then turned off - effectively getting an AutoCommit=>0 handle.
Yes you would be right. Make sure you revert the attributes of the database handle to their original cached version as soon as you can, or you're in trouble. It's as if you had the DB connection done outside of your cgi code at startup, effectively a global mutable structure on a child basis. Then if you change one of its attributes, it's changed for the life of the child. That's very dangerous to say the least.
You could of course technically revert the attributes back to the default upon getting the db handle from Apache::DBI, but that's quite a hack. Something like this should work (warning: untested!):
my @params = ($dsn, $user, $auth, \%attrs);
# first we connect (or grab back the Apache::DBI cached $dbh) my $dbh = DBI->connect(@params);
# then we recreate the same way as Apache::DBI does it the cache key
my @args = map { defined $_ ? $_ : "" } @params;
if ($args[0] =~ /^dbi:/i) { $args[0] =~ s/^dbi:[^:]+://io; }; # remove the dbi:driver: piece
my $idx = join $;, $args[0], $args[1], $args[2];
if (3 == $#args and ref $args[3] eq "HASH") {
map { $idx .= "$;$_=$args[3]->{$_}" } sort keys %{$args[3]};
}
# now that we have the key, we verify if it exists in Apache::DBI
my $ApacheDBIConnections = Apache::DBI::all_handlers();
if (exists $$ApacheDBIConnections{$idx}) {
# if we're here, it means that the attributes of the cached handle
# are the same as those that you originally connected with
} else {
# this is bad, the attributes are different!
# what you need to do here is reset the attributes of the $dbh
}
Any light or clarity on this would help immensely,
BTW, to whomever is the maintainer of Apache::DBI:
Can we have an exported method that will generate us the key if we give it all the connection parameters? Otherwise those of us who need the key will have to keep our code in sync with Apache::DBI.
I can provide a patch.
