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, imhar...@nuskin.com (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:pluna...@gmail.com]
> Sent: Monday, September 20, 2010 3:25 AM
> To: dbi-us...@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
