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

Hi, I've been looking around a bit for some code to handle DBI connect() failures nicely, I want my program to continue running, doing something alternate because of a connection failure flag somehow being set? 
$dbh = DBI->connect('dbi:Oracle:', qq{$bu_username/$bu_passwd@(DESCRIP
+TION=
    (ADDRESS=(PROTOCOL=TCP)(HOST= $bu_host)(PORT=$bu_port))
    (CONNECT_DATA=(SID=$bu_sid)))}, "");
  
 if ( undef $dbh ) {
  print LF "Cannot connect to database!\n";
  &log_send_mail;
  exit;
 }
 else {
  print "Connection to $bu_sid successful\n";
 }

[download]
How can I do this gracefully? How do I set {RaiseError => 1} with my connect string above? I certainly don't want a string of errors and die happening. I've also unsuccessfully tried by checking the value of a parameter being set via || on the connect statement. I've also checked out: http://labs.redhat.com/cturner/DBI_AdvancedTalk_2002/

Replies are listed 'Best First'.
Re: Graceful handling of DBI connect errors'
by blokhead (Monsignor) on Nov 15, 2002 at 15:40 UTC
    I generally wrap all DBI-related procedures in eval blocks. This includes statement prepares & executes. Here's an example for DBI connections: 
    my $dbh;
eval {
  $dbh = DBI->connect($dsn, $stuff, { RaiseError => 1 });
  # will die if it doesn't work
}
if ($@) {
  # you can process the error string in $@ and clean it up 
  # for output
  print $error;
}

    [download]
    Of course, if you connect to the DB with RaiseError set, you will have to wrap all DB-related methods in eval blocks to avoid die'ing.

    Also, your code has if (undef $dbh) to check for connection errors. Yikes! This will set the value of $dbh to undef each time, and will always return undef, so the top part of your if-statement will never be executed, only the else-block. This may be why you're having problems. You probably meant to say if (not defined $dbh).

    blokhead

[reply]
[d/l]
Re: Graceful handling of DBI connect errors'
by Chief of Chaos (Friar) on Nov 15, 2002 at 12:33 UTC
    hi,
    this is a code snippet from a bigger source.
    But it will show you how might handle errors.
    # ...
# --------------------
# create db connection
# --------------------
  $dbase = DBI->connect("DBI:Oracle:$dbsid", $dbuser, $dbpassw, { Auto
+Commit => 0 });
  if (!(defined($dbase))){
# do error handling
}
#
# ----------
# do the sql
# ----------
# ...
  $dbase->{PrintError} = 0;
  $dbsth = $dbase->prepare( $sql );
  $error = $dbase->errstr;
  if (defined ($error) && $error ne "") {
    # error handling
    # errormsg is stored in $error
    # ...
    $dbase->{set_err}=(0, "");
    $dbase->{PrintError} = 1;
    # exit function or so
    # ...
  } else {
    # no error
    $dbase->{set_err}=(0, "");
    $dbase->{PrintError} = 1;
    # ...
  }
# ...

    [download]
    I hope this can help you.
[reply]
[d/l]
      Carefull with auto commit turned off that causes its own fun... 
      our $dbh = DBI->connect("DBI:Oracle:$dbsid", $dbuser, $dbpassw,{'AutoC
+ommit'=>0});

our ($dbh_start, $dbh_error); # Global flags mutate during use of the 
+module

sub start_trans() {
   my $proc = (caller(1))[3];
   my $error;
   
   warn "Starting $proc\n";
   if (not defined $dbh_start) {
      $dbh_start = $proc;
   }

}

sub end_trans() {
   my $proc = (caller(1))[3];

   warn "Exiting $proc\n";
   if ($dbh_start eq $proc) {
      if (not defined $dbh_error) {
         warn "Commited Transaction in $proc\n";
         $dbh->commit;
         $dbh_error = undef;
      } else {
         warn "Rolled back Transaction in $proc\n";
         warn "because $dbh_error\n";
         $dbh->rollback;
         $dbh_error = undef;
      }
      $dbh_start = undef;
   }

}

      [download]
      Wrapper each atomic transaction in a sub procedure with :- 
      start_trans();
#####
# SQL
#####
end_trans()

      [download]
[reply]
[d/l]
[select]
Re: Graceful handling of DBI connect errors'
by ColtsFoot (Chaplain) on Nov 15, 2002 at 11:39 UTC
    You could try somethig like 
        my $dbh = DBI->connect('dbi:Oracle:',
                qq{$bu_username/$bu_passwd@DESCRIPTION=
                 (ADDRESS=(PROTOCOL=TCP)
                 (HOST= $bu_host)(PORT$bu_port))
                 (CONNECT_DATA=(SID=$bu_sid)))}, "",
                 {RaiseError=>1});

    [download]
    RaiseError is used in conjunction with eval {...} to catch the exception that is being thrown

    perldoc DBI at the command prompt and a search for RaiseError
    will get a fuller explanation
    Hope this helps
[reply]
[d/l]
Re: Graceful handling of DBI connect errors'
by rob_au (Abbot) on Nov 15, 2002 at 22:12 UTC
    There is an excellent presentation that covers the graceful handling of DBI errors (and transaction handling) that was given at a Melbourne PM meeting by pjf earlier this year. The notes for this talk can be found in PDF format on the Perl Training Australia web page here.

     

    perl -e 'print+unpack("N",pack("B32","00000000000000000000000111100001")),"\n"'

[reply]

Back to Seekers of Perl Wisdom