Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Connecting to Multiple Databases

by antonybr (Initiate)
on Jun 03, 2004 at 00:45 UTC ( #359857=perlquestion: print w/replies, xml ) Need Help??
antonybr has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I have two sybase Databases which syncs between them and they are geographicaly located in x and y city. Now I want my perl script which queries the database to be fault tolerent. My script connects to the database in location x and I want to connect to the database y in case if the database server x goes down. The script runs some simple select statements, not a complex one. I use DBI to connect to the database. Any quick and easy solutions greatly apreciated. Please post if you have any code samples or url for the websites where I can find more info Thanks

Replies are listed 'Best First'.
Re: Connecting to Multiple Databases
by Zaxo (Archbishop) on Jun 03, 2004 at 01:40 UTC

    I don't know if the Sybase driver supports it, but flaky servers suggest the DBI $dbh->ping method. You can open a handle to each server, say $dbx and $dby, and use the trinary op to select my $sth = ($dbx->ping ? $dbx : $dby)->prepare('select 1+1');
    and so on. That will get awkward fast.

    Another approach is to first try to connect to x. If that fails, connect to y. That isn't what you want for a long running process, but for a one-shot script it is probably ok (you wanted tolerant, not immune, right?)

    my $dbh = connect($x_string, $x_nam, $x_pass) || connect($y_string, $y_nam, $y_pass) or die 'No db!';
    In any case, you should be meticulous about error checking with each DBI call.

    After Compline,

Re: Connecting to Multiple Databases
by dragonchild (Archbishop) on Jun 03, 2004 at 01:30 UTC
    You're going to want to write a DBI wrapper. I have something like this in every project I work on. Something like:
    package DBI_Wrapper; sub new { my $class = shift; my $self = bless { dbh => [], active => 0, }, $class; } sub add_dbhs { my $self = shift; push @{$self->{dbh}}, @_; } sub AUTOLOAD { my $meth = our $AUTOLOAD; (my $func = $meth) =~ s/.*:://; my $self = shift; my $start = $self->{active}; my $dbh; while (1) { $dbh = $self->{dbh}->[$self->{active}]; eval { $dbh->ping }; last unless $@; $self->{active}++; $self->{active} %= @{$self->{dbh}}; die "No valid DBH found\n" if $self->{active} == $start; } return $dbh->$meth( @_ ); }

    Or, something like that. :-)

    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

Re: Connecting to Multiple Databases
by Ryszard (Priest) on Jun 03, 2004 at 12:07 UTC
    I wrote this module to do connect to n databases (of the same type) at the same time. You'll just have to change the connect string/driver.
Re: Connecting to Multiple Databases
by simonm (Vicar) on Jun 03, 2004 at 17:15 UTC
    You may want to look at three modules from CPAN which address DBI failover: DBD::Multiplex, DBIx::DBCluster, and DBIx::HA.

    You'll need to spend some time to evaluate each one to see if it addresses your purposes, but depending on your requirements, this may be much easier than writing your own from scratch.

Re: Connecting to Multiple Databases
by mpeppler (Vicar) on Jun 04, 2004 at 14:02 UTC
    After looking at the modules suggested by simonm I would recommend using DBIx::HA - it appears to map quite well to your requirements.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://359857]
Approved by Paladin
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2018-06-19 22:53 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (115 votes). Check out past polls.