Solved

adodb transactions with php. 500 points. PLEASE HELP!

Posted on 2007-04-11
10
723 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

773 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