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

Dear Monks, How can I prevent my script from bombing if I cannot initiate a Database Handle? For example: 
my $dsn = "DBI:mysql:host=somehost:database=test";
my $dbh = DBI->connect($dsn, $user_name, $password) || warn "Cannot co
+nnect to server #!\n";

[download]
If this bombs I want it to either try to connect to another host or read from a text file. I've read "perldoc DBI" and I'm still a bit lost! :) Do you have any examples you can provide? Thanks much!!!

Replies are listed 'Best First'.
Re: Perl DBI Question
by pg (Canon) on Jan 21, 2004 at 20:57 UTC

    This should help you, just check return code from connect(): (Tested and worked, I have web, but not weeb)

    use DBI;
use strict;
use warnings;

my $dbi;

my @databases = ('dbi:ODBC:weeb', 'dbi:ODBC:web');
for (@databases) {
    print "attempt connect to database $_\n";
    if ($dbi = DBI->connect($_,"","",{AutoCommit=>1})) {
        print "connected to database $_\n";
        last;
    }
}

    [download]
[reply]
[d/l]
      Or to be a little bit more general for the original poster: 
      use DBI;
use strict;
use warnings;

my $dbh;
my $dsn = 'dbi:ODBC:web';
my $user = 'some';
my $pass = 'thing';

if ($dbi = DBI->connect($dsn,
                        $user,
                        $pass,
                        {AutoCommit=>1})) {

    print "connected to database $_\n";
}
else {
    print "did not connect to database\n";
    print "try connecting to another database here??\n";
}

      [download]
      HTH.
[reply]
[d/l]
Re: Perl DBI Question
by twerq (Deacon) on Jan 21, 2004 at 21:48 UTC
    You might want to look at the "RaiseError" section of the DBI manual, I think this is what you're looking for. 

    $dbh = DBI->connect($dsn, $user, $password,
     { RaiseError => 0, AutoCommit => 0 });

    [download]

    --twerq
[reply]
[d/l]

      Close, but not what the OP was asking for. That will automatically die if the connection fails. Either what pg provided or something like the following would be more of a workable solution: 

      #!c:/perl/bin/perl -w
$|++;

use strict;
use DBI;

my $table = 'table_name';
my @db = (
  ['localhost',        'user1', 'pass1'],
  ['other.server.com', 'user2', 'pass2'],
  ['last.attempt.com', 'user3', 'pass3'],
);

my $dbh;
for my $db (@db) {
  eval {
    $dbh = DBI->connect(
      'dbi:mysql:database=' . $table . ';host=' . $db->[0],
      $db->[1], $db->[2],
      { AutoCommit => 1, RaiseError => 1 }
    );
  };
  next if $@;
}

die "Unable to connect to any database!\n"
  if not defined $dbh;

      [download]

[reply]
[d/l]

Back to Seekers of Perl Wisdom