mp-

Here's one way to manage your connections. You can create a wrapper class around the actual connection. i've done some *basic* connection management for Win32::ODBC.

(1) have a single place in the code where all connections are made so that it is easy to change connection parameters later;

I kept a default connection string in the dbConnectionClass. All new connections that do not have a connection string specified use the default connection string.

(2) have connections be persistent (Apache::DBI);
The (Win32::ODBC) connections persist in connection pools hashed on their connectionString. a pool is an array of db connections.

(3) have one point in each module at which the connection is made for that module;
I propose that you call new dbConnection liberally and let the underlying connection pooling manage the number of connections kept open.

(4) in a given process, share all connections (i.e. connection is a singleton or is managed by a singleton);
yep...

(5) Have modules that require database access manage their own connection rather than require that a database handle be passed to them;
yes. each method that requires db access should just create a new dbConnection. (you dont care wether a new one is really created or one is pulled from a pool)

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]

Hope this was helpful!

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.