If you set up a DBI connection handle like this, the connection will not be made until the handle is first used. The connection persists after that, and it will try to reconnect if ping fails. No connection is made to the database server if the handle goes unused.

This demonstrates Tie::Constrained as a factoring tool for real code. The alternative is to find the code branches which use the database and hard-code the test and connection in each.

DBD::Mock is used as a stand-in for the real connection because it has an off switch ("mock_can_connect").

(Added) Thanks, jZed++, for suggesting $DBI::errstr as the message when things come apart.

use DBI;
use Tie::Constrained;

our $dbh;
tie $dbh, 'Tie::Constrained' => {
    test => sub {
            and $_[0]->ping;
    value =>  DBI->connect('DBI:Mock:','','', \%attr),
    fail => sub {
        # warn  "Connecting $dsn . . .";
        $_[1] =
            DBI->connect($dsn, $user, $passwd, \%attr)
                or die $DBI::errstr;

$dbh->{'mock_can_connect'} = 0;
$Tie::Constrained::STRICT = 1;
Replies are listed 'Best First'.
Re: Lazy DBI Connection Handles
by Corion (Pope) on Feb 09, 2005 at 14:11 UTC

    Another way to do lazy DBI connections is to base your code on Ima::DBI or on Class::DBI (which is in turn based on Ima::DBI). Ima::DBI does a lazy connect and also tries to reconnect once your connection goes stale. But if you already have an existing infrastructure, Ima::DBI might not be a feasible way to do this.

Re: Lazy DBI Connection Handles
by dragonchild (Archbishop) on Feb 09, 2005 at 13:50 UTC
    An alternative:
    use Class::LazyLoad [ qw/ DBI connect / ]; my $dbh = DBI->connect( @your_params_here ); # Right now, the $dbh hasn't actually connected or done DBI-ish things +. $dbh->do_whatever(). # Now, it does.

    Update: This won't do the reconnect, but you're also not tying a tied variable. :-)

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Lazy DBI Connection Handles
by The Mad Hatter (Priest) on Feb 09, 2005 at 02:30 UTC
    Very cool and neat use of Tie::Constrained. This could come in handy sometime.
Re: Lazy DBI Connection Handles
by Limbic~Region (Chancellor) on Feb 09, 2005 at 15:09 UTC
    One problem with Perl5's references is that there is no auto-dereferencing. Otherwise, you wouldn't have to resort to tying a variable
    my $dbh = lazy_dbh( $dsn, $user, $pass, \%attr ); $dbh->()->ping(); sub lazy_dbh { my @args = @_; my $dbh; return sub { require DBI; if ( ! defined $dbh || ! $dbh->ping() ) { $dbh = DBI->connect( @args ) or die $DBI::errstr; } return $dbh; }; }
    It just gets to be a pain to use $dbh->() every where. You could also use my Multi-Method closures technique, but you need to remember to put {} around the method names. There's also the issue of keeping the hash keys synced with DBI's methods. You could tie the underlying hash so that it looked up each method on first invoke and kept it around for later. Then we are back to square one - tying tied variables.

    I guess what I am saying is that I think this is a nice use of Tie::Constrained, but that there is no such thing as a free lunch.

    Cheers - L~R