I have a number of perl modules that manage their own persistence to a SQL database via DBI. I discovered a flaw in my current approach which was to use an Apache::Singleton::Process object to create a connection and in modules that needed a connection, store the connection in a lexically scoped variable near the top of the module. The problem I found with this approach is that I am essentially caching the database connections at module load time, so if the database connection goes away (which it does from time to time because of things like the database server getting restarted), the now broken connection continues to be used.
The code (after quite a bit of distillation) looks basically like: package MyPersistentObject; use strict; use warnings; use MyProject::Dbh; my $dbh = MyProject::Dbh->instance->dbh; ### sub method1 { my ($self, $id) = @_; $dbh->do(qq[delete from table1 where id=?], undef, $id); } #... package MyProject::Dbh; use strict; use warnings; use Apache::Singleton::Process; use base (Apache::Singleton::Process); sub _new_instance { return DBI->connect($dsn, $user, $pass, {RaiseError => 1}); } ----- httpd.conf has: Perlrequire /pathgoeshere/startup.perl and starutp.perl has: use Apache::DBI; ---- What I'm basically trying to accomplish is: 1) Make database connection in one place in the code. 2) Not have to pass $dbh all over the place, but instead have modules that need a database connection get it themselves. 3) Connection only needs to happen once per request. If the connection dies during a request, the next query using that request should fail, and the next request serviced by that child process should get a new connection. 4) I don't want to call 'connect' before every query because the overhead is too high, even when Apache::DBI returns a "pooled" connection. 5) For one request, only one connection should be used (i.e, the connection should be a request singleton). 6) Modules that require DBI connections should not be dependent on a mod_perl environment. I.e. they should also be usable in command line scripts. What I have come up with so far is to rewrite the connection handling as follows, which also seems to remove the need for Apache::DBI: package MyProject::DbhSingleton; use Apache::Singleton::Request; use base (qw Apache::Singleton::Request); sub _new_instance { my $dbh = DBI->connect($dsn, $user, $pass, $attr) or die DBI->errstr; } The thing I'm not sure about is when should I be calling this to get a local copy of $dbh in a module. I know that calling it only at module "use" time is not frequent enough, and calling it before every query is too frequent (too expensive), but I'm not sure of a happy medium. For some objects, calling MyProject::DbhSingleton->instance to store a dbh as a instance data would suffice, but not in all cases. I've looked through the guide and done a number of web searches but have not found anything that directly addresses this. Any help would be greatly appreciated -- [EMAIL PROTECTED]