An ADOdb Question

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.


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!


Sandy Smith

September 6, 2006
7:31 am

Are you using tables that support transactions, i.e., innoDB?
If you don’t need that kind of support (row-level locking, true transactional support), then I’d ditch ADODB, unless you really are going to be swapping out DB engines underneath on a regular basis. ADODB seems to be the best of the abstraction libraries out there (at least for PHP4), but it is still roughly twice as expensive as plain mysql_* functions, in our experience.

Jason Lefkowitz

September 6, 2006
9:26 am

I am using InnoDB — bitter experiences that shall not be recounted here have convinced me that using MyISAM is just setting yourself up for pain down the road…
Here’s a question, then: any of you used the new mysqli_ and PDO libraries in PHP5? Any reason to use them over the standard old mysql_ libraries?


September 6, 2006
11:15 am

I haven’t used mysqli or PDO yet. Doesn’t adodb have a driver for mysqli and PDO. You could give that a try.


September 6, 2006
2:22 pm

You could always swallow all pride and dignity and do this:



October 19, 2006
9:44 am

Hi there … I have run into the same problem here. Using the standard mysql extension in PHP and I am using InnoDB for the table that needs to be rolled back. But nothing happens. No rollback.
Have you found a solution to your problem yet?
By the way … when I run a simple test using transactions on the MySQL commandline … rollback works as expected … so MySQL should not be the problem.