DBI Connect Error Trapping

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";
[download]

Comment on DBI Connect Error Trapping Download Code

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;` [download] -- Love justice; desire mercy.	[reply] [d/l]
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 }` [download] Hope this is what you were asking for!	[reply] [d/l]
Re: Re: DBI Connect Error Trapping by hmerrill (Friar) on Nov 08, 2002 at 18:18 UTC
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` [download] at a command prompt HTH.	[reply] [d/l]
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");` [download] ~CubicSpline "No one tosses a Dwarf!"	[reply] [d/l] [select]
Re: DBI Connect Error Trapping by erasei (Pilgrim) on Nov 08, 2002 at 20:44 UTC
Also, instead of just `die`ing 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. }` [download] You can do a lot of fun things with `or sub()`.	[reply] [d/l] [select]