Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

DBI Connect Error Trapping

by Zo (Scribe)
on Nov 08, 2002 at 17:25 UTC ( #211480=perlquestion: print w/replies, xml ) Need Help??

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

Hello all,
I've searched perlmonks and cpan and the perl cookbook, now I'm here asking. I successfully trap errors from my 'prepare' statement, but I'm having trouble trapping error messages from my 'connect' statement. My script runs overnight, connecting to an Oracle db, but if the db is down, I would like to trap a message when the connection fails. I will attach my existing code. Oh, I also implemented the attributes of '{RaiseError=>1,AutoCommit=>0}' but I still can't acheive what I want. Thank you in advance for all help.

# connection to database my $dbh = DBI->connect("dbi:Oracle:CSCD001", "user", "password",{Raise +Error=>1,AutoCommit=>0}); # new code to catch DB return error $dbh->{RaiseError}=0; my $sth = $dbh->prepare("Select $fields From $table Where exe_c = 'N' +AND rownum < 151 "); $sth->execute(); # new code to catch DB return error print BATCH $dbh->errstr."\n"; @results{@fields} = (); $sth->bind_columns(map{\$results{$_} } @fields); # generates DBI log file DBI->trace( 2 , './dbi_trace.txt'); while($sth->fetch()) { # prints to screen to view process running print "$results{batch_line} <$results{rowid}>\n";

Replies are listed 'Best First'.
Re: DBI Connect Error Trapping
by strider corinth (Friar) on Nov 08, 2002 at 17:52 UTC
    The answer is in the documentation for DBI, under the connect statement:
    $dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr; $dbh = DBI->connect($data_source, $username, $password, \%attr) or die $DBI::errstr;
    Love justice; desire mercy.
Re: DBI Connect Error Trapping
by shemp (Deacon) on Nov 08, 2002 at 17:45 UTC
    If I understand correctly, you want to trap DBI connect errors. So, you should eval() the DBI->connect() statement as follows:
    ... my $dbh; eval { $dbh = DBI->connect(...); }; if ( $@ ) { # do something with the error }
    Hope this is what you were asking for!
      Shemp is right - the only thing you're missing in your code is the "eval". RaiseError causes dbi statements to *die* when they have an error(so that you don't have to put a "die" with a message along with each dbi statement), and the eval puts the die message into the special $@ variable. So after the eval, you just test the $@ variable - if it has something in it, then something within the eval failed. If you've just got the connect inside the eval, then you know your connect failed. Again, the the error *message* is in the $@(dollar - at-sign) variable.

      Read about RaiseError and "eval" by doing
      perldoc DBI
      at a command prompt

Re: DBI Connect Error Trapping
by CubicSpline (Friar) on Nov 08, 2002 at 18:25 UTC
    You've been given a couple of good options by some of the other monks. I personally prefer the explicit die call, myself.

    You didn't say this explicitly, but if you'd like to really "trap" those error messages so they don't get spit out to the screen when your script runs, use the PrintError attribute with your connect statement. Eg:

    my $dbh = DBI->connect("dbi:Oracle:DB", "user", "pwd", {PrintError => +0}) || die("Unable to connect to DB. Error was: $DBI->errstr\n");

    "No one tosses a Dwarf!"

Re: DBI Connect Error Trapping
by erasei (Pilgrim) on Nov 08, 2002 at 20:44 UTC
    Also, instead of just dieing outright, you could call a subroutine. I have some high priority scripts that run overnight as well, and if they fail, I _need_ to know about it. Something like this works nicely:
    my $dbh = DBI->connect(...) or handle_connect_fail($DBI::errstr); sub handle_connect_fail { my ($error) = shift; # print loud warnings, # maybe sleep 300 and retry. # Or use mail send module, and have your script page you. }
    You can do a lot of fun things with or sub().

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://211480]
Approved by Daruma
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2019-05-25 09:54 GMT
Find Nodes?
    Voting Booth?
    Do you enjoy 3D movies?

    Results (152 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!