perl DBD::ODBC rollback ineffective with AutoCommi

Question

I am currently having a block like below. So with this we set autocommit off and and do a commit/rollback. Now at the rollback line , we are getting a failure saying that "rollback ineffective with AutoCommit enabled at" . How could this happen since AutoCommit was indeed disabled by the begin_work. This problem was not there for a long time and it is suddenly occuring.

On investigating further , i found that the update_sql1 created a #temp table , and update_sql2,update_sql3,update_sql4 query the same #temp table, and are failing with Invalid object name '#temp' error. Immediately control flows to if($@) where $dbh->{AutoCommit} is set to 1. First of all its really wierd as to why update_sql2 and onwards count not find object #temp , when update_sql1 was indeed successful.

Any pointers ?

====

$dbh->db_Main()->begin_work;

eval {

 $dbh->do($update_sql1);
 $dbh->do($update_sql2);
 $dbh->do($update_sql3);
 $dbh->do($update_sql4);
 $dbh->commit;
 1;
}

if ($@) {
 $logger->info("inside catch");
 $logger->info("autocommit is $dbh->{AutoCommit}");
 $dbh->rollback;

}

===

Here is the full error message

Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::db handle ..
rollback ineffective with AutoCommit enabled ...

Answer 1

Under autocommit, the begin starts a transaction, which is automatically committed. You have to turn off AutoCommit to get a transaction.