Re: [fw-db] Problem with transactions

Hey Olivier,

the row is written to the database because you are commiting the transaction, even though you have catched an exception!

This means, you have to put the $db->commit(); at the end of try-Block, sth. like this:

<pre>
$db = Zend_Registry::get("db");
// Start a transaction
$db->beginTransaction();
try {
   // Run a query that works
   $db->query("CREATE TABLE test ("
      . "test_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
      . "PRIMARY KEY (test_id)");
   // Run a wrong query on purpose
   $db->query("This is not a valid SQL query!");

// No exception was caught while doing the transition, we can
// commit the transaction.
$db->commit();
} catch (Exception $e) {
   $db->rollBack();
   print "An exception occured!\n";
   exit(1);
}
</pre>

Kind regards, Dennis Winter.

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

Dennis Winter
WebDeveloper
communero.com

Olivier Ricordeau schrieb:

Hi list,

I'm trying to do a transaction with ZF (for the first time) and I can't make it work...

Here is my code:

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

$db = Zend_Registry::get("db");
// Start a transaction
$db->beginTransaction();
try {
   // Run a query that works
   $db->query("CREATE TABLE test ("
      . "test_id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
      . "PRIMARY KEY (test_id)");
   // Run a wrong query on purpose
   $db->query("This is not a valid SQL query!");
} catch (Exception $e) {
   $db->rollBack();
   print "An exception occured!\n";
   exit(1);
}
// No exception was caught while doing the transition, we can
// commit the transaction.
$db->commit();

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

This code catches the exception correctly and displays "An exception occured!", but the "test" table is present in the DB after the script's execution...

I'm new to SQL transactions, so what did I miss here?

Cheers,
Olivier

PS: I'm using the pdo_mysql adapter. I tried with mysqli and it leads to the same result.