Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

adodb transactions with php. 500 points. PLEASE HELP!

Posted on 2007-04-11
10
Medium Priority
?
753 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 29

Accepted Solution

by:
TeRReF earned 672 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 664 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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