I am facing a strange issue
The Zend_DB_Adapter's beginTrasaction() and commit() methods don't seem to be working as expected. I have INSERT statements (in a FOR LOOP) enclosed in beginTrasaction() and commit() methods. But even when error occurs like below, I still see some rows inserted already, while I was expecting the commiting not to happen since errors occured. I am not able to comprehend why. Can someone help . thanks.
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'
code block like:
**$localDB->beginTransaction();**
try{
echo $localDB->isConnected();
$localDB->query("TRUNCATE TABLE $this->dbTable");
**foreach ($rowSet as $row){**
foreach ($row as $key=>$value){
$localRow[$this->columnMap[$key]] =$value;
}
**$localDB->insert($this->dbTable,$localRow);**
}
$localDB->commit();
}
catch (Exception $e){
$localDB->rollBack();
echo $e->getMessage();
}
The TRUNCATE TABLE
will cause an implicit commit wich will end the current transaction.
1) Put TRUNCATE TABLE
before beginTransaction()
.
$localDB->query("TRUNCATE TABLE $this->dbTable");
$localDB->beginTransaction();
try {
...
$localDB->commit();
} catch (Exception $e){
$localDB->rollBack();
echo $e->getMessage();
}
2) DELETE FROM
should be transaction save (but slower)
$localDB->beginTransaction();
try {
$localDB->query("DELETE FROM $this->dbTable");
...
$localDB->commit();
} catch (Exception $e){
$localDB->rollBack();
echo $e->getMessage();
}