Re: dbi transactions error handling

Ok, now I understand where is my mistake.
I set $dbh->{RaiseError} = 1 after declaring the statements $sql_live
and $sql_dwh so these have the original value of RaiseError. In this
case I have to modify the property of the single statements.

Thanks,
PL

On Sep 20, 7:11 pm, [email protected] (Ian Harisay) wrote:
> 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:[email protected]]
> Sent: Monday, September 20, 2010 3:25 AM
> To: [email protected]
> 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$@\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