RE: dbi transactions error handling

[Ian Harisay]

I think you would be better off to write it like this.

$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 0;
$dbh->{AutoCommit} = 0;

my @record = ($row_id, $file_id, $offset, $country, $date_day, $campaign, 
$leaf_id, $status_id);

my $rv;
SWITCH: {
  last unless($rv = $sql_live->execute(@record));
  last unless($rv = $sql_dwh->execute(@record));
};

if($rv){
  $dbh->commit();
}
else{
  warn "Transaction failed, rolling back. Error was:\n $dbh->errstr\n";
  $dbh->rollback();
}


-----Original Message-----
From: Paolo Lunazzi [mailto:pluna...@gmail.com] 
Sent: Monday, September 20, 2010 3:25 AM
To: dbi-users@perl.org
Subject: dbi transactions error handling

Hi all,

I'm working on a script that should manage transactions.  My code is
something like this:

--- CODE ---

$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 0;
$dbh->{AutoCommit} = 0;
eval {
            $sql_live->execute(
                $row_id,     $file_id, $offset, $country,  $date_day,
                $campaign, $leaf_id,   $status_id
            );
            $sql_dwh->execute(
                $row_id,     $file_id, $offset, $country,  $date_day,
                $campaign, $leaf_id,   $status_id
            );
            $dbh->commit();
};

if ($@) {
            warn "Transaction failed, rolling back. Error was:\...@\n";
            eval { $dbh->rollback ( ); };
}
$dbh->{AutoCommit} = 1;
$dbh->{PrintError} = 1;
$dbh->{RaiseError} = 0;

--- CODE ----

Testing the script I noticed that errors on the execute are not
catched by eval and $@ is not set. For example duplicate key errors
and calls with a wrong number of bind variables are printed on the
screen (event with PrintError = 0) and the transaction is not
rollbacked. If I do the same statment with a $dbh->do() the
transaction fails and do the rollback correctly.
Note that $sql_dwh and $sql_live are prepared using $dbh handle.
With some debug I can see that DBD::mysql::st execute fails are not
catched by eval while DBD::mysql::db do fails are handled correctly.
Am I doing something wrong or this behaviour is correct?

TIA,
PL