Using multiple databases

There are cases when you have the same schema in multiple databases and would like to access two or more databases from the same script without reconnecting every time.

Here is an example of what I use with 0.96. If you know of a better solution, please add it here.

This one caches the connections in a Class hash and returns the current one after the myinit routine is called.

-- gabor@pti.co.il

package My::DBI; use base 'Class::DBI'; use DBI; my %dbh; my $current_dbh; sub myinit { my ($dsn) = @_; if ($dbh{$dsn}) { $current_dbh = $dbh{$dsn}; } else { $current_dbh = $dbh{$dsn} = DBI->connect_cached(            $dsn, $username, $password,             {                My::DBI->_default_attributes, # defaults of Class::DBI             }         ); if (not $current_dbh) { warn "Could not connect to '$dsn' $DBI::errstr"; return 0; }    }     return 1; } sub db_Main { my $self = shift; return $current_dbh; } 1;

My::DBI::myinit($dsn_a); My::DBI->create(....) My::DBI::myinit($dsn_b); My::DBI->create(....)
 * 1) and in the nearby script:

9/22/04 -- Question: scenario: Two websites on same server set up with virtual hosts. Modperl 2 Apache 2. CDBI::Customer.pm class and Apache::!NewCstmr module to use it to create new customers. Customer.pm. !NewCstmr.pm generates dynamic db connection at run time.

So if you go to https://OneComany.net/new_customer or https://TwoCompny.net/new_customer, then same copy of Apache::NewCstmr and CDBI::Customer do work.

My question is if OneCompany/new_customer is invoked and NewCstmr initializes to OneCompany's database but before its done with its DB transactions, TwoCompany/new_customer is invoked and inits to TwoCompany</tt>'s database, is OneCompany</tt>'s database handle changed?

Since the dbh is class data, I think it is. If it were global to NewCstmr</tt>, it would be fine. Also, if these two requests happened to be handled by different child processes, it would work, but there is no way to ensure that happen.

Note that if you use multiple databases you should override dbi_commit and dbi_rollback to do the right thing, since the default Class::DBI implementation won't. I.e.

sub dbi_commit { my $proto = shift; $current_dbh->commit(@_); } sub dbi_rollback { my $proto = shift; $current_dbh->rollback(@_); }

--StepanRiha

Note: The other way to fix dbi_commit</tt> and dbi_rollback</tt> when overriding db_Main is to call:

__PACKAGE__->_remember_handle( 'Main' );

Which lets Ima::DBI know to fetch the $dbh</tt> by calling $class->db_Main</tt>.

-- Bill Moseley - 23 May 2007

Also, note that in order to have identical behavior to Class::DBI defaults you need to set ChopBlanks => 1</tt> in your connection. It took me a while to figure that one out....

--brian@glassbrian.com

2/2/2005 - This should solve the issue from the 9/22/04 post. We have a case where we're extending Class::DBI and the db_Main issue causes problems due to its class data nature. We have several Class::DBI subclasses hitting different databases/schemas from websites in the same Apache instance, but the connection for one site will show up in another sites log unless we make db_Main instance based (as recommended in the pod). I took the idea from gabor@pti.co.il's post above and tweaked it to solve this problem (forgive me for the semantic changes), but no need for an init routine as first call will do just that.

package My::Package; use base 'Class::DBI'; my %g_database_handles; my $db_options = { __PACKAGE__->_default_attributes }; { # default db connection # this could still be dsn, # but passwords should be elsewhere my $_connection_key = 'TESTDB_A'; sub get_connection_key { return $_connection_key } sub set_connection_key { my ($self, $key) = @_; $_connection_key = $key; return; } } sub db_Main { my ($self) = @_; my $connection_key = $self->get_connection_key; unless ($g_database_handles{$connection_key}) { my ($database,$username,$password) = $self->getDatabaseConfig($connection_key); #see below $g_database_handles{$connection_key} = DBI->connect_cached( $database                                       , $username                                        , $password                                        , $db_options                                        ); # see brian@glassbrian.com comments # about attributes and ChopBlanks=>1 unless (exists $g_database_handles{$connection_key}) { warn ref($self). ": Could not connect to '$username\@$database' $DBI::errstr"; return undef; }   }    return $g_database_handles{$connection_key}; } 1;
 * 1) global hash for database handles
 * 1) Get Class::DBI's default dbh options

my %db_connect_info = ('TESTDB_A' => ['dbi:Oracle:some_database','user','password'],                       'TESTDB_B' => ['dbi:Oracle:some_other_database','user','password']); sub getDatabaseConfig { my ($self, $key) = @_; return @{$db_connect_info{$key}}; }
 * 1) in some other file
 * 2) use whatever you want here to get the stored connection info,
 * 3) this is just a rough example, but easy to throw into a separate module

--scottsweep@yahoo.com

06/06/2005:

Fixed the typo ($db_group</tt> should have been $connection_key</tt>).

Added class variable and functions to set which db connection to use.

Just call My::Package->set_connection_key('TESTDB_B');</tt> after use My::Package;</tt> to change the db connection.

If getDatabaseConfig</tt> is in the same class and loads username/password e.g. with Config::Simple</tt> from a file, change $self</tt> to __PACKAGE__</tt> to avoid the <tt>perl -c</tt> errors.

--alexander.hartmaier@t-systems.at

29 June 2006

We found that with the <tt>db_Main</tt> above we would get errors if the database connection was lost. We now just call <tt>DBI->connect_cached</tt> every time because <tt>DBI->connect_cached</tt> provides caching anyway and will also reconnect if the connection is lost. That is we don't maintain a <tt>%g_database_handles</tt> hash.

--Jed Soane

Update: the problem with calling <tt>connect_cached</tt> every time is that it will reset attributes to their default state. So if AutoCommit is set on by default but is turned off to start a transaction then calling <tt>connect_cached</tt> again will re-enable AutoCommit.

I use the method used by Ima::DBI, that is check Active and ping. Ping hits the database, so might be wise to limit the ping frequency. my %database_registry; my $current_database;

# method to save dsn, etc. in registry by a key sub register_database { }

# method to set $current_database by key sub select_database { }

sub db_Main { my $self = shift; my $connection = $database_registry{$current_database}; die "db_Main failed to find the connection settings for database key [$current_database]" unless ref $connection eq 'HASH'; my $dbh = $connection->{dbh}; # Return active dbh return $dbh if $dbh && $dbh->FETCH( 'Active' ) && $dbh->ping; # Create a new connection my %attr = (           $self->_default_attributes,            %{ $connection->{attr} },            AutoCommit     => 1,            pg_enable_utf8 => 1,     # see DBD::Pg            private_pid    => $$,    # protect against forks        ); # RaiseError is on so this should die on failures $connection->{dbh} = DBI->connect_cached( @{$connection}{qw/ dsn user pass /}, \%attr ); return $connection->{dbh}; }

I also have <tt>register_database</tt> method that saves the connection info in the hash, and <tt>select_database</tt> method.

-- Bill Moseley - 23 May 2007