You can pass an already initialized database handler, store it inside the object and then reuse it at will. Here's a tested example.

#!/usr/bin/perl -w
use DBI;
use strict;
package Other;

sub new {
    my $class = shift;
    my $self = bless {}, $class;
    my $dbh = shift || return undef;
    $self->{dbh} = $dbh;    # store the dbh
                            # for future use
    return $self;
}

sub do_it {
    my $self = shift;
    my $query = shift;
    #
    # reuses the stored dbh
    #
    my $sth= $self->{dbh}->prepare($query);
    $sth->execute;
    while (my $row = $sth->fetchrow_arrayref()) {
        print "@$row\n";
    }
}

package main;

sub do_it_differently {
    my $db = shift;
    my $query = shift;
    my $rows = $db->selectall_arrayref($query);
    for my $row (@$rows) {
        print "<@$row>\n";
    }
}

my $dbh = DBI->connect("DBI:driver:database", # change it
    'username', 'password', {RaiseError => 1})
    or die "can't connect\n";

my $query = qq{select * from departments };
my $ext = new Other $dbh          # passes a dbh to an object
        or die "can't create\n";
$ext->do_it($query);              # calls a method that uses a stored 
+dbh

do_it_differently ($dbh, $query); # passes a dbh to a sub

$dbh->disconnect();
[download]

 _  _ _  _  
(_|| | |(_|><
 _|   

The only trouble with this approach is that in a mod_perl setting that object could exist for hours with no use, during which time that connection might time out. If you fetch your $dbh from Apache::DBI at least once per request, you will avoid this. Apache::DBI will notice that the connection is dead and reconnect.

I don't see why you couldn't. You might want your modules to do an isa() check on what was passed in, but other than that, it should be OK to just pass around the database handle. Especially true with using Apache::DBI, since you know the handle will always be open (or if not, that it will be automatically reopened), so there's no worry an underlieing module might try to do something with it after its been disconnected.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

The real question is are you using transactions in SQL? if not Apache::DBI can hide a lot of this from your modules. You can get a new handle for each module and let Apache::DBI sort out the optimisation of handle usage.

You need to be careful if you wish to use transactions within you SQL. Both queries in a transaction need to be passed the same handle to work on otherwise you generate two independant transactions.

• Generally selects rarely need to be part of a transaction (except when doing a FOR UPDATE lock)
• Single updates or inserts do not need to be part of a transaction.
• Multiple table updates or inserts do need to be part of a transaction if it is required that a single failure causes all updates/insets to fail.

Hope it helps
UnderMine

Given a pooling mechanism, my inclination is to let each bit of code that needs a DB handle just grab it from the pool (and you can simplify this by creating a connector that just returns a connection for you, instead of having to manually call DBI->connect all the time). The one exception to this model would be if you have a backend that supports transactions¹, so that the session associated with a given handle really matters, in which case, of course the thing to do is simply pass the handle around as you would any other object, as has already been pointed out.

¹ I suppose it might also apply if your DBMS has something equivalent to MySQL's last_insert_id, as that may be session-and-thus-connection-dependent as well.

If not P, what? Q maybe?
"Sidney Morgenbesser"

Apache::DBI will give you the same handle every time if you connect with the same parameters. Grabbing it from the "pool" is no different from passing it, since there is actually only one database connection per process when you keep using the same connection string. Apache::DBI provides persistent connections, not pooled ones.