DBI::connect_cached, Apache::DBI and Class::DBI

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

This is a question about whether the DB connection pooling in these three modules work together. I have a bunch of scripts for which I put all DB access code into a DB module, it provides, among other things, a sub like:

sub get_connection{
    return DBI->connect_cached($dbUrl, 
                    $dbUser, $dbPassword,
       { RaiseError => 0, PrintError=>1,AutoCommit => 1}) 
    or warn("Failed to connect to DB");

}
[download]

The other scripts simply call get_connection whenever they need a connection. Since the call is to connect_cached, I'm not closing the connection in other scripts. This seems to be working fine. But, some of the scripts are deployed into mod_perl with Apache::DBI pre-loaded, which provides connection caching. Furthermore, I'm also using Class::DBI in some other scripts, which I understand also uses its own connection caching. All of these work on the surface, but I'm not sure whether caching is actually taking place, and which module is doing the caching, and whether there is any potential conflicts here. Thanks.

Comment on DBI::connect_cached, Apache::DBI and Class::DBI Download Code

Replies are listed 'Best First'.
Re: DBI::connect_cached, Apache::DBI and Class::DBI by perrin (Chancellor) on Nov 30, 2004 at 04:10 UTC
Apache::DBI and DBI::connect_cached work fine, but Class::DBI uses Ima::DBI, which doesn't play nicely with Apache::DBI. This will not really affect you unless you either use transactions (Apache::DBI has a cleanup_handler to do safety rollbacks at the end of each request, but Ima::DBI prevents it from working) or use something like Class::DBI::mysql which will try to load open a database connection when you use it and pull it in from your startup.pl (before forking). I've written explanations with sample code and workarounds on the Class::DBI mailing list and the mod_perl list.	[reply]
Re^2: DBI::connect_cached, Apache::DBI and Class::DBI by Arunbear (Prior) on Nov 30, 2004 at 09:00 UTC
I've written explanations with sample code and workarounds on the Class::DBI mailing list and the mod_perl list. Considering the importance of this topic, why not post that here on Perlmonks - it would make a good tutorial.	[reply]
Re^3: DBI::connect_cached, Apache::DBI and Class::DBI by perrin (Chancellor) on Nov 30, 2004 at 15:45 UTC
I'm hoping it will soon be irrelevant because the problem will be fixed. In the meantime here's the post.	[reply]
Re: DBI::connect_cached, Apache::DBI and Class::DBI by etcshadow (Priest) on Nov 30, 2004 at 01:59 UTC
I'm not a Class::DBI guy, so I won't comment there. As for DBI and Apache::DBI, though, here's the scoop: Apache::DBI, if loaded, will override DBI's connect method (by way of defaulting the "connect_method" DBI connection attribute to 'Apache::DBI::connect'), and thus use Apache::DBI's connection caching. However, if you want to override this and ALWAYS use DBI's connection caching, then you can set $DBI::connect_via to "connect_cached". `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l]