package myFoo;
use strict;
use warnings;
use dbConnection;

sub new
{
    my $self  = bless( {}, shift );
    $self->{id} = shift;
    my $db = new dbConnection();

    #
    # build sql
    # load from $db
    #
 

}
[download]

#
# this is not my production code... i had to chop it down significantl
+y to for the purpose of this example
# although i welcome comments/suggestions!
#
package dbConnection;
use strict;
use warnings;
use Win32::ODBC;

my %_ConnectionPools;
my $_DefaultConnectionString;


sub DefaultConnectionString
{
    @_ ? $_DefaultConnectionString = shift
       : $_DefaultConnectionString;
}

sub _PoolConnection
{
    my $poolname = shift;
    my $connection = shift;
    my $pool = _GetPool($poolname);
    push @$pool,$connection;
}

sub _GetPool
{
    my $poolname = shift;
    my $pool = $_ConnectionPools{$poolname};
    if (!defined $pool)
    {
        my @connections;
        $pool = \@connections;
        $_ConnectionPools{$poolname} =  $pool;
    }
    return $pool;
}

sub new
{
    my $self  = bless( {}, shift );
    $self->{_ConnectionString} = shift;

    #
    # check if any connection exist in pool
    #
    my $pool = _GetPool( $self->{_ConnectionString} );
    my $connection = shift @$pool;
    if ( ! $connection )
    {
        #
        # no connection exists, create new connection
        #
        $connection = new Win32::ODBC( $self->{_ConnectionString} );
        if ( ! $connection )
        {
            die "Could not connect to [" . $self->{_ConnectionString} 
+. "]";
        }
    }

    $self->{_Connection} = $connection;

    return $self;
}

sub DESTROY
{
    my $self = shift;
    _PoolConnection($self->{_ConnectionString}, $self->{_Connection} )
+;
    $self->{_Connection} = 0;
}


1;
[download]

Your reply was helpful. I now have the following to create a singleton database handle.

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
+;
}
[download]

This would work if I called it within every method (sub) that does database access, but I'm looking for a way to avoid going to that extreme.

pacakge MyProject::DBH;

use DBI;

my $DBH;    # lexical scope outside the constructor
            # makes it available to all calls to new().

sub get_dbh { 
    if ref($DBH) eq 'DBI::db') { 
        # dbh already created, so return it
        return $DBH;
    } else { 
        # connect to the DB and return the handle
        # (assume $user, $pass and $dsn come from somewhere)
        $DBH = DBI->connect($dsn, $user, $pass);
        return $DBH;
    }
}
[download]

Then, it's just a simple matter of every object calling MyProject::DBH->get_dbh; to get the same database handle.

Could you give an example of where you call MyProject::DBH->get_dbh in a module and where you store the database handle? I.e., do you call it from the constructor then store the database handle in the object as instance data, or do you call it from each object method that needs a database handle?

In that project, I had each class which needed access to the DB call MyProject::DBH->get_dbh in its constructor and store the returned database handle in the instance data. Then, it's available for each method that needs it in $self->{'dbh'}.

You could just as easily make the DBH a package variable, but doing it this way seemed "neater" despite the increaed number of calls to get_dbh.

In a non-persistant world I manage my database connections via a database handler. Basically I can call and "cache" any amount of statements and handles I wish.

In a persistant world, as you rightly mention the database handles are kept open by mod_perl. From memory mod_perl will go and check the connection is still up, and if not it will bring it back up. So in effect mod_perl manages your connections for you. (I've read the theory, but not used it IRL yet, so I'll stand corrected here).