Problem creating a singleton wrapper around DBI

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to create a singleton wrapper around the Perl DBI class, but getting problems.

I think I'm missing something to specify the correct class.

Here is the code i'm using:

package Singleton::DBI; 

use conf::SiteConfig;
use DBI qw/ :sql_types / ;      # Database interface

use vars qw( @ISA);
@ISA = qw(DBI);
  
my $oneTrueSelf;
  
sub instance 
{
    $oneTrueSelf ||= (shift)->new();
}
  
sub new 
{
    my $type = shift;

    # get necessary config info
    my $dbuser = conf::SiteConfig::get_conf ( 'dbuser' );
    my $dbpass = conf::SiteConfig::get_conf ( 'dbpass' );
    my $dbname = conf::SiteConfig::get_conf ( 'dbname' );
    my $dbserv = conf::SiteConfig::get_conf ( 'dbserv' );
    
    # Build up DBI connection string.
    my $datasource = 'dbi:mysql:'.$dbname;
    $datasource .= "\;host=$dbserv" if ( $dbserv );

    # Connect
    my $this  = DBI->connect ( $datasource, $dbuser, $dbpass );
    return bless $this, $type;
}


1;
[download]

The error I see is:

Can't locate auto/Singleton/DBI/selectrow_a.al  ...
[download]

I suspect that I'm not modify the ISA type properly, so that the returned instance of Singleton::DBI is not treated identically as a DBI object, but I'm a little unclear on how inheritance works in Perl.

Comment on Problem creating a singleton wrapper around DBI Select or Download Code

Replies are listed 'Best First'.
Re: Problem creating a singleton wrapper around DBI by chromatic (Archbishop) on Aug 23, 2005 at 00:55 UTC
Your immediate problem is that `DBI::connect()` returns a `DBI::db` object, not a `DBI` object, so your subclass does not inherit from the right class. You don't need inheritance for this to work though. Take out all of the `@ISA` code and change the last few lines of `new()` to: `# Connect return DBI->connect( $datasource, $dbuser, $dbpass );` [download] If you do that, the caching will still work as long as all of your code calls the `instance()` method. (If it were my code, I would rename `new()` to `connect()` or something similar to make it more clear that this is a singleton factory.)	[reply] [d/l] [select]
Re^2: Problem creating a singleton wrapper around DBI by brian_d_foy (Abbot) on Aug 23, 2005 at 03:56 UTC
Before you return the object, you have to store it in the singleton holder: `$oneTrueSelf = DBI->connect ( $datasource, $dbuser, $dbpass ); # some error checking to ensure you have an object ... return $oneTrueSelf;` [download] If you run into other problems, check out how Apache::DBI does it. -- brian d foy <brian@stonehenge.com>	[reply] [d/l]
Re^3: Problem creating a singleton wrapper around DBI by skx (Parson) on Aug 23, 2005 at 04:23 UTC
Good followup, thanks. I've updated the code to test for errors as you suggest, and work as directed. All is well now. Steve --	[reply]
Re: Problem creating a singleton wrapper around DBI by diotalevi (Canon) on Aug 23, 2005 at 02:58 UTC
chromatic has it exactly. I'd like to add that if you wish to add other functions, "transparently," to the returned object, you probably want to check out delegation as a design. Class::Delegation has some things to make things simple but it isn't required for that.	[reply]
Re^2: Problem creating a singleton wrapper around DBI by skx (Parson) on Aug 23, 2005 at 04:24 UTC
Not immediately relevant, but it's worth bearing mind for the future. Thanks for the pointer! Steve --	[reply]
Re: Problem creating a singleton wrapper around DBI by adrianh (Chancellor) on Aug 23, 2005 at 08:54 UTC
As an alternative to caching the DBI connection yourself - get DBI to do it with `connect_cached`. This has the additional advantage of reconnecting if the old database handle has been disconnected or a ping fails. `sub new { # get necessary config info my $dbuser = conf::SiteConfig::get_conf ( 'dbuser' ); my $dbpass = conf::SiteConfig::get_conf ( 'dbpass' ); my $dbname = conf::SiteConfig::get_conf ( 'dbname' ); my $dbserv = conf::SiteConfig::get_conf ( 'dbserv' ); # Build up DBI connection string. my $datasource = 'dbi:mysql:'.$dbname; $datasource .= "\;host=$dbserv" if ( $dbserv ); # Connect return DBI->connect_cached( $datasource, $dbuser, $dbpass ); }` [download]	[reply] [d/l] [select]
Re^2: Problem creating a singleton wrapper around DBI by eric256 (Parson) on Aug 23, 2005 at 20:37 UTC
I just wanted to second this. If your script does anything long term then this is a must. I've been bitten by randomly closing database handles on my singleton in the past and it was well worth the additional _cached to fix it. I beleive this also adds value in mod_perl environments as it will cache across requests which is a big ++. ___________ Eric Hodges	[reply]
Re: Problem creating a singleton wrapper around DBI by danmcb (Monk) on Aug 23, 2005 at 11:37 UTC
I guess your intention is to make a single connection for use by other modules during a CGI call? I use a piece of code like this `{ package Common; my $dbh = 0; sub getDbh { if ($dbh == 0) { # do all the stuff you said above to connect ... } return $dbh; } sub END { # print "disconnecting .."; $dbh->disconnect(); } } # other stuff .... 1;` [download] The $dbh is now privately scoped and cannot be accessed excpet by the method calls provided. You also don't have to worry about disconnecting, it happens at the end of the script automatiocally. (Knowing Perl, it would clean up the filehandle safely when done anyhow, but it's good practice to do it yourself.)	[reply] [d/l]
Re^2: Problem creating a singleton wrapper around DBI by danmcb (Monk) on Aug 23, 2005 at 11:40 UTC
My error - the package statment should have been outside the block, of course ...	[reply]