Gossamer Forum: General: Databases and SQL: DBI help, on transactions and "eval"

Jun 21, 2003, 1:47 AM

robyone

User (119 posts)

Jun 21, 2003, 1:47 AM

Post #1 of 5

Shortcut

DBI help, on transactions and "eval"

Hi!

Is this correct for transactions?

---------------------------------

my $sth = $dbh->prepare("INSERT INTO global VALUES('12','no','0.00')");

my $sth2 = $dbh->prepare("INSERT INTO global2 VALUES('12','no','0.00')");

eval {

$sth->execute();

$sth2->execute();

$dbh->commit();

}

if( $@ ) {

#log this error or email the admin

$dbh->rollback();

}

$sth->finish();

---------------------------------------

Jun 22, 2003, 10:55 AM

Alex

Administrator (9387 posts)

Jun 22, 2003, 10:55 AM

Post #2 of 5

Shortcut

Re: [robyone] DBI help, on transactions and "eval" In reply to

Close, make sure you have AutoCommit and RaiseError set properly. i.e.:

Code:
         $dbh->{AutoCommit} = 0;  # enable transactions, if possible 
         $dbh->{RaiseError} = 1; 
         eval { 
             foo(...)        # do lots of work here 
             bar(...)        # including inserts 
             baz(...)        # and updates 
             $dbh->commit;   # commit the changes if we get this far 
         }; 
         if ($@) { 
             warn "Transaction aborted because $@"; 
             $dbh->rollback; # undo the incomplete changes 
             # add other application on-error-clean-up code here 
         }

(straight from DBI man page).

Cheers,

Alex
--
Gossamer Threads Inc.

Jul 25, 2003, 5:07 AM

robyone

User (119 posts)

Jul 25, 2003, 5:07 AM

Post #3 of 5

Shortcut

Re: [Alex] DBI help, on transactions and "eval" In reply to

That looks perfect but I can't make a rollback. Can you try this and see that sth is executed even if sth2 is wrong. I get the error but the roolback is not done for sth transaction.

I think that AutoCommit => 0 is not used. Ideas?

my $dbh = DBI->connect($db,$db_user, $db_pass, { AutoCommit => 0, RaiseError => 1, } ) or die print "connection failed";

eval {

my $sth = $dbh->prepare("INSERT INTO test1 values('1','2','3')");

$sth->execute;

my $sth2 = $dbh->prepare("INSERT INTO test2 ('3','4','5')");

$sth2->execute;

$dbh->commit;

};

if ($@) {

print $@;

$dbh->rollback; # undo the incomplete changes

}

$dbh->disconnect;