3

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();
    }   
Benjamin Cremer
  • 4,842
  • 1
  • 24
  • 30
krishna
  • 3,547
  • 5
  • 27
  • 29
  • I feel you have a primary key which is trying to enter same value. In this case 0. Give the table details and also from where the columnMap comes. Does it contains any values ? – Hari K T May 20 '11 at 06:33
  • I think his primary problem is that the transaction is not working as expected. – Benjamin Cremer May 20 '11 at 06:53

1 Answers1

9

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();
} 
Community
  • 1
  • 1
Benjamin Cremer
  • 4,842
  • 1
  • 24
  • 30