Solved

adodb transactions with php. 500 points. PLEASE HELP!

Posted on 2007-04-11
10
715 Views
Last Modified: 2013-12-12
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!
0
Comment
Question by:geordie007
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 29

Accepted Solution

by:
TeRReF earned 168 total points
Comment Utility
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
 
LVL 8

Assisted Solution

by:netmunky
netmunky earned 166 total points
Comment Utility
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
 
LVL 7

Author Comment

by:geordie007
Comment Utility

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
 
LVL 8

Expert Comment

by:netmunky
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Author Comment

by:geordie007
Comment Utility

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
 
LVL 8

Expert Comment

by:netmunky
Comment Utility
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
 
LVL 5

Assisted Solution

by:rhysp
rhysp earned 166 total points
Comment Utility
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
 
LVL 5

Expert Comment

by:rhysp
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now