adodb transactions with php. 500 points. PLEASE HELP!

hi experts

has anyone any experience with using adodb transactions? i have three innodb tables that i need to make changes on as a transaction. i read the adodb manual, and have done what they said, which is basically using:

$db->StartTrans();
$db->Execute($sql_1);
$db->Execute($sql_2);
$db->Execute($sql_3);
$db->CompleteTrans();

but whenever i deliberately mess up a sql statement, nothing is rolled back. in fact, it's no different to writting the above code without the starttrans() line. i'm using a peristant connection (PConnect) and have set autorollback just in case, like this:

$db = &NewADOConnection('mysql');
$db->autoRollback = true;
$db->PConnect($server, $userid, $password, $database);

but nothing works. i've done a search on here, and a google, but have found nothing. HELP!
LVL 7
geordie007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TeRReFCommented:
Check what the Execute method returns when a query fails and use that something like (this example assumes it returns false):

$complete = true;
$db->StartTrans();
$complete = $db->Execute($sql_1);
if ($complete)
  $complete = $db->Execute($sql_2);
if ($complete)
  $db->Execute($sql_3);
if ($complete)
  $db->CompleteTrans();
else
  // perform a rollback
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
netmunkyCommented:
try:
$db->StartTrans();
if( ! $db->HasFailedTrans() && ! $db->Execute( $sql_1 ) ) { $db->FailTrans(); }
if( ! $db->HasFailedTrans() && ! $db->Execute( $sql_2 ) ) { $db->FailTrans(); }
if( ! $db->HasFailedTrans() && ! $db->Execute( $sql_3 ) ) { $db->FailTrans(); }
$db->CompleteTrans();
0
geordie007Author Commented:

thanks guys, but two things:

firstly, aren't both your methods missing the point of what the new 'smart' transactions are supposed to be able to do in adodb? from what the manual suggests, the code that i wrote in my question should work and commit/rollback as necessary, without any error checking. or is the manual stretching the truth a little bit and i'm being a bit naive?

secondly, if my first point is wrong, and actually you do have to do your own error checking, your methods don't suggest a rollback. if anything i would have to create a further three statements, executing each one of the corresponding initial statement failed. surely that can't be right?

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

netmunkyCommented:
i have not use adodb myself, but reading further in the manual, it appears you are correct. it *should* be working. (http://phplens.com/lens/adodb/docs-adodb.htm#ex11)

when an execution fails, is it die()ing php at that statement, or continuing on to the completetrans?
0
geordie007Author Commented:

it's completing. so if i'm executing three statements, it carrys out all three statements, regardless if one or more error, and regardless of where that error/s occurs.

in other words, my code may as well be:

// $db->StartTrans();
$db->Execute($sql_1);
$db->Execute($sql_2);
$db->Execute($sql_3);
// $db->CompleteTrans();

0
netmunkyCommented:
have you checked what the return value is of StartTrans?
according to comments in the source code, the function returns false if the database (not the table) does not support transactions. under what conditions a database would not support transactions i'm not sure of, but that can be further investigated if needed.
0
rhyspCommented:
Try this for debugging:

$db->debug = true;
$db->StartTrans();
$db->Execute($sql_1);
$db->Execute($sql_2);
$db->Execute($sql_3);
$db->CompleteTrans();

See what it outputs. Depending on what you are deliberately doing to make it fail it might not actually be failing, just affecting 0 rows.

Also, make sure you are using the mysqli driver. According to the ADOdb docs, you need to use mysqlt as the database driver (mysql with transaction support). If you aren't using the right driver it will ignore the transactional stuff anyway.
0
rhyspCommented:
You could also try mysqli as the driver (instead of mysql or mysqlt) as this already includes transaction support and has the improved functionality for MySQL 4.1+.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.