Re: [fw-db] Problem with transactions

As long as i remember, DDL commands are not covered with transactions (create, drop, alter...), but only SELECT, INSERT, ...

I now ini PostgreSQL this is possible to do, but i'm not sure about MySQL.

2008/12/18 David Penner <pennedav@gmail.com>

But he's calling exit(1) before exiting the catch block so the
commit() call never happens in the event of an exception being thrown.

don't you need to do $db->query('SET AUTOCOMMIT=0') before executing
any other queries. Try that and see what happens.

On Wed, Dec 17, 2008 at 11:19 AM, Dennis Winter
<dennis.winter.83@googlemail.com> wrote:
> 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.
>
>

--
Xavier Vidal Piera
Director Tècnic de Citrusparadis.com - Grupo Intercom
Enginyer Tècnic Informàtic de Gestió
Tècnic Especialista Informàtic d'equips
xavividal@gmail.com
http://web.xaviervidal.net
610.68.41.78