in reply to Re: Singletons, Apache::DBI and MySQL
in thread Singletons, Apache::DBI and MySQL

I'm not sure if it's a bug in Apache::DBI. the code i used is rather simple, but maybe wrong

package DBH;
use strict;
use DBI;
@ISA    = qw(Exporter); 
@EXPORT_OK = qw(&DBHOBJ);

my $dbhobject = new();

sub new {
    my $classname = shift;
    my $self={};
    
    if ($dbhobject) {
        $self=$dbhobject
    } else {
        $self = bless {};
                my $dbh = DBI->connect("DBI:mysql:blah) or
                  die DBI::errstr;

        $self->{"dbh"} = $dbh;
    }
    $self;
}

sub DBHOBJ {
    return $dbhobject->{"dbh"};
}

sub DESTROY {
    my $self=shift;
    if ($dbhobject) {
        $dbhobject = undef;
    }    
}

[download]
Then i can call it using: 
   use strict;
   use DBH qw(&DBHOBJ);
   my $sth = DBHOBJ()->prepare('select blah from table;');
    $sth->execute();

[download]

Replies are listed 'Best First'.
Re^3: Singletons, Apache::DBI and MySQL
by Thilosophy (Curate) on Jan 03, 2005 at 09:20 UTC
    So you are not using any kind of ping at all now? And this works? If you just want to turn off ping completely, I think Apache::DBI has an option for this. But was not your problem that the connection broke and you wanted automatic reconnects? Your code does not seem to do that. 
    sub DESTROY {
    my $self=shift;
    if ($dbhobject) {
        $dbhobject = undef;
    }    
}

    [download]
    I doubt that this DESTROY method is called ever. Your $dbhobject is not a blessed reference (just a plain hash ref containing the DBI handle), so this is not really OOP and $classname, $self and so on are a little confusing.

    Update: Okay, maybe I misunderstood. This is the old code you were using (with Apache::DBI)? And it did not reconnect? If so, you were not using Apache::DBI correctly. You have to use "connect" to get a connection, and Apache::DBI will either make a really new connection or just return an alreay open connection. That is transparent to you. What you cannot do is cache the connection Apache::DBI give you in your own singleton object. You have to "connect" every time you want a connection: So instead of 

    sub new {
    my $classname = shift;
    my $self={};
    
    if ($dbhobject) {
        $self=$dbhobject
    } else {
        $self = bless {};
                my $dbh = DBI->connect("DBI:mysql:blah) or
                  die DBI::errstr;

        $self->{"dbh"} = $dbh;
    }
    $self;
}

sub DBHOBJ {
    return $dbhobject->{"dbh"};
}

    [download]
    use 
    use Apache::DBI;
sub DBHOBJ {
    DBI->connect("DBI:mysql:blah) or
                  die DBI::errstr;
}

    [download]
[reply]
[d/l]
[select]
      Thanks. I'll try this.
      Done! you're right, this works. Thanks.
[reply]
      Nope, we totally rewrote it... Not using Apache::DBI anymore...
      package DBH;
use strict;
use Init;
use DBI ();

sub connect {
    if (defined $DBH::conn) {#die ref( $DBH::conn);
        my $ret;
    eval {
            $ret = DBH->ping;
        };
        if (!$@ && $ret) {
            return $DBH::conn;
        }
    }
  
    $debug && print STDERR "DBH.pm: making new connection\n";
    $DBH::conn = DBI->connect(
        "DBI:mysql:blah,
    {
            PrintError => 1,
            RaiseError => 0,
        }
    ) || die $DBI::errstr; #Assume application handles this
      return $DBH::conn;
  }

sub ping {
    my $ret = 0;

    if (time - $DBI::lastPing < 10) {    
         return 1;
    }
    
    eval {
        local $SIG{__DIE__}  = sub { return (0); };
        local $SIG{__WARN__} = sub { return (0); };
        # adapt the select statement to your database:
        $ret = $DBH::conn->do('select 1');
        $DBI::lastPing = time;    #record time in seconds
        };
    $debug && print STDERR "DBH.pm: pinging DB handle: $ret\n";
    return ($@) ? 0 : $ret;
    }

      [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom