Solved

adodb transactions with php. 500 points. PLEASE HELP!

Posted on 2007-04-11
10
721 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
ID: 18892302
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
ID: 18892626
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
ID: 18892748

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
ID: 18892928
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 7

Author Comment

by:geordie007
ID: 18910330

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
ID: 18910838
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
ID: 19450969
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
ID: 19469217
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

16 Experts available now in Live!

Get 1:1 Help Now