You can get a global handle through a closure. That's more or less ok in OO since it's a method which accesses the handle ;-)

package My;
use DBI;
{
    my $dbh = DBI::connect( . . .); # add error handling
    sub dbh () { $dbh }
}
# call up the handle with My::dbh().
# You don't need to import anything from DBI unless you
# want to see or modify its globals

package My::Collection;
# . . .
[download]

Note that sharing a dbi connection between objects will break for threaded or forked code. In those cases a private handle should be preferred.

In code I'm working on at present I have two classes of interest. One class is intended for creating a single instance from that manages the database connection. The second class is the base class for a bundle of classes that mediate access to different tables in the database.

The table accessors don't require a connection at create time and can do a limited range of stuff without a connection being provided, but they die if you try to use them in a way that would require a connection and you've not provided one yet. That allows a Task object to be created for example with a bunch of task information, then handed over to a scheduler that gets the task queued (added to the task table).

The table object base class provides members to insert a derived instance into its table and to update/fetch/delete entries matching various criteria. The base class will also create the table for a derived class if the table doesn't exist (the derived class provides a column spec). Typical derived class code looks like:

package Job;

require Exporter;
our @ISA = ('DBObject');
our @EXPORT = qw( );


my $jobTableDef = ['jobs', # Job specifications
     'id INTEGER PRIMARY KEY NOT NULL,
     command VARCHAR(1024) NOT NULL,
     maxrunmins INTEGER,
     numruns INTEGER,
     runtimetotal INTEGER,
     flags VCHAR(20)
     '
    ];

sub new {
    my ($class, %params) = @_;

    $params{flags} = '' unless exists $params{flags};
    return DBObject::new ($class, %params, tableDef => $jobTableDef);
}

1;
[download]

and typical usage looks like:

sub CheckTasks {
    my $self = shift;
    my $task = Task->new (dbh => $self->{db});
    my @readyTasks = $task->getOldest ();
    
    if (@readyTasks) {
        my $taskInfo = $task->fetch (id => $readyTasks[0][0]);
        my $job = Job->new (dbh => $self->{db});
        my $jobInfo = $job->fetch (id => $taskInfo->{jobid});
[download]

Hi GrandFather,

I see you are not using the 'usual' way of doing classes. Usually, I use something like this:

package Job;

use base 'DBObject';

sub new {
    my ($class, %params) = @_;
    ...
    return $class->SUPER::new(...);
}
[download]

There's any special reason for not doing this way?

Igor 'izut' Sutton
your code, your rules.

Ignorance mostly! :) Thanks for the pointer.

I'm into my second year of writing Perl and until recently my use of OO has been pretty light. However most things seem to just work so I've only skimmed OO docs to solve immediate issues and have likely missed chunks of the big picture. I'll go back and do some of the required reading real soon now - promise.

---

DWIM is Perl's answer to Gödel

I'm pretty sure that DBI does pool connections, but i can't prove it. Update both adrianh in Re^2: How to share DBI connections between objects? and perrin in Re^2: How to share DBI connections between objects? suggest I made this up.

In your example, I'd have a My->Db method, that returned an already connected $dbh this way all the My:: classes and modules can get a database handle when they want it, and you only have to manage the connecting in once place. Some folks call this a factory

this also means that you can change how the connection is made etc in one place.

I'm pretty sure that DBI does pool connections, but i can't prove it.

The connect() method doesn't (unless you're using Apache::DBI). The connect_cached() method does.

Personally I don't like using this sort of thing since it makes it harder to inject things like test databases in a particular context. I prefer to pass in a configuration object to my application that points to the database, that way I can just build custom config objects for testing.

I'm not sure I understand. I presume you're objecting to My->Db, right?

So instead something more like this?

package My::Underling; our $Db;
sub import {
  my($class, $config_ref )=@_;
  $Db = DBI->connect( @{$config_ref->{connect_args} } );
}
# ... else where in the code
import My::Underling \%global_config;
[download]

@_=qw; ask f00li5h to appear and remain for a moment of pretend better than a lifetime;;s;;@_[map hex,split'',B204316D8C2A4516DE];;y/05/os/&print;

Calling it "pooling" might be overstating the case. When you call connect_cached, it will check to see if the current process already has a connection open with those parameters and return it to you if it does. There is no "pool" of connections and no sharing between processes (or threads).

It's a package for OO style handling of database connections and caching at the class level.

package My::Collection;

use base qw/Ima::DBI/;

__PACKAGE__->set_db('foo', "dbi:pg:foo", "user", "pass", {});
__PACKAGE__->set_db('bar', "dbi:pg:bar", "user", "pass", {});
[download]

my $foo_dbh = My::Collection->db_foo;
my $bar_dbh = My::Collection->db_bar;
[download]

You might want to check out Ima::DBI.

Rose::DB handles this sort of thing nicely too.