An ADOdb Question

Posted on September 5, 2006

Here’s a question for any of you out there who use the ADOdb database abstraction layer for PHP.

I have a block of code that looks like this:

$sql1 = “MySQL INSERT statement”;
$sql2 = “MySQL UPDATE statement”;

$dbconn -> StartTrans();

$dbconn -> Execute($sql1);
$dbconn -> Execute($sql2);

$dbconn -> CompleteTrans();

Now, here’s the thing. There’s a typo in the SQL statement I put into the $sql2 variable. It will never process successfully.

Given that, nothing should be committed to the database. Correct? Because ADOdb’s “smart transactions” means that it will catch the error being returned from the second Execute and automatically roll the transaction back.

Right?

That’s certainly how the docs read to me. Thing is, that’s not how ADOdb is behaving. Instead of rolling back the whole transaction, it simply fails the second Execute() call, and commits the first — which is pretty much the exact opposite of what anything calling itself “transactions” should do, since it leaves loose data hanging around.

Am I missing something regarding ADOdb’s transaction handling? Should I be using an alternate syntax, throwing in some manual rollback triggering of my own, or what? Frankly if I can’t rely on ADOdb to handle transactions reliably my impulse is just to throw the whole library out — but this seems like a basic enough use-case that I can’t imagine nobody’s run into it before, if it truly is a bug and not just me screwing up somewhere.

PHP gurus, whaddaya think?

UPDATE (Oct. 20, 2006): Problem solved!