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

Hello Perl Monks:
I would like to trap the oracle database error. I also want my program to proceed further even when there is a problem while inserting records into the database.

The code below always satisfies the "if" condtion. It does not get into to "else" condtion when there are any errors while inserting the records. Am i missing something. Or else please let me know the better code
sample code: 
 $sth = $dbh->prepare($sqla);
 eval { local $SIG{'__DIE__'}; $answer = $sth->execute(); };

 if ($@)
 {
     $mailbody .= "Cannot insert into $database \n";
 }
 else
 {
    $sth->finish() if defined $sth;
 }

[download]

Replies are listed 'Best First'.
Re: trapping database errors
by Abigail-II (Bishop) on Feb 12, 2003 at 16:06 UTC
    What I typically do is: 
    my $dbh = DBI -> connect ($dsn, $user, $pwd, 
                          {RaiseError => 1, PrintError => 0, AutoCommi
+t => 0})
    or die "Failed to connect: $DBI::errstr\n";

die "Unsetting autocommit failed\n" if $dbh -> {AutoCommit};

eval {
    my $sth = $dbh -> prepare ($sql);
    $result = $sth -> execute (@bind);
}
if ($@) {
    $dbh -> rollback;
    die "Database action failed: $@\n";
}
else {
    $dbh -> commit;
}

    [download]

    This is for a simple transaction, but if you need to do more actions inside a transaction, you would do all of them inside the eval. Of course, if you want to continue after a failure, you shouldn't do the die.

    Abigail

[reply]
[d/l]
Re: trapping database errors
by pfaut (Priest) on Feb 12, 2003 at 15:51 UTC

    It looks like you have RaiseError set. If you set RaiseError to a false value, DBI will return on error conditions rather than dieing. You can then look at the returned value and $DBI::errstr or $dbh->errstr to figure out if it worked.

    my $dbh = DBI->connect($connect_string,$user,$password,{ RaiseError=>0
+ });

    [download]
    ---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';

    [download]
[reply]
[d/l]
[select]
Re: trapping database errors
by perrin (Chancellor) on Feb 12, 2003 at 16:11 UTC
    Get rid of that local $SIG{__DIE__} stuff -- it serves no purpose here. Set RaiseError and then just do this: 
    my $mailbody;
my $sth = $dbh->prepare($sqla);
eval {
    $sth->execute();
};

if ($@) {
    $mailbody .= "Cannot insert into $database \n";
} else {
    $sth->finish(); # but why?  probably not needed
}

    [download]
[reply]
[d/l]
Re: trapping database errors
by dws (Chancellor) on Feb 12, 2003 at 17:08 UTC
    In addition to perrin's advice, I'd be specific about what you're looking for in $@. There are many reasons why an INSERT could fail. Some you might be able to proceed past (e.g., duplicate key errors), and some are show stoppers (e.g., database or transaction log full).

    Change 

      if ( $@ ) { ...

    [download]
    to 
      if ( $@ =~ /specific error text/ ) { ...

    [download]
    You might need several tests to handle different error conditions.

[reply]
[d/l]
[select]
Re: trapping database errors
by jobber (Sexton) on Feb 12, 2003 at 15:55 UTC
    Hello try do something like this to catch your errors 
     $sth->execute($sqla)           
            or failover($sth->errstr);

sub failover()
{
  my $error = shift;
  $dbh->rollback # If needed;
  $dbh->disconnect;
  $mailbody .= "Cannot insert into $database : $error\n";

}

    [download]
    Hope this helps
[reply]
[d/l]
Re: trapping database errors
by jasonk (Parson) on Feb 12, 2003 at 15:53 UTC 
    $answer = $sth->execute();
if($answer) {
    $sth->finish if $sth;
} else {
    $mailbody .= "Cannot insert: ".$dbh->errstr."\n";
}

    [download]
[reply]
[d/l]
      no this does work. It seems always $answer is undefined
[reply]

Back to Seekers of Perl Wisdom