HI,
Please keep in mind that I'm new to PDO.
I've developed the following method to execute a series of SQL commands in a single transaction. If any one of the commands fails, the transaction should rollback. However, the results that I am getting show that if the last command in the transaction fails, the other three are committed rather than rolled back.
The method:
This is the calling test code:
This is the starting state of table X1, before the code executes:
mysql> select * from x1;
+------+---------+
| c1 | c2 |
+------+---------+
| 0 | c1 is 0 |
| 1 | c1 is 1 |
+------+---------+
2 rows in set (0.00 sec)
This is the state after the code has failed and returned the error message just below:
(If the code were working correctly, this state should be the same as the original. So it looks as if autocommit is still enabled.)
mysql> select * from x1;
+------+----------------+
| c1 | c2 |
+------+----------------+
| 0 | x-action test2 |
| 1 | x-action test |
| 10 | c1 is 10 |
+------+----------------+
3 rows in set (0.00 sec)
The error message returned by the catch clause:
PHP EXCEPTION:
Script..........: testDbCmds.php
Functie.........: execXaction
Geroepen door...: main
Meelding........: ERR: Failed to execute sqlCmd = update x1xxxx set c2 = 'make it all fail' where c1 = :v0
with bindArr = 1
Exception.......: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tobivroegh_tobi_live.x1xxxx' doesn't exist
Can anyone see what I might be doing wrong?
Thanks for your help.
Andynic
Please keep in mind that I'm new to PDO.
I've developed the following method to execute a series of SQL commands in a single transaction. If any one of the commands fails, the transaction should rollback. However, the results that I am getting show that if the last command in the transaction fails, the other three are committed rather than rolled back.
The method:
Code:
public function execXaction ($frmFct, $dbObj, $sqlCmdArr) {
$fctNm = "execXaction";
$dbHndl = $dbObj->dbConnect($fctNm);
// $dbHndl->exec('SET AUTOCOMMIT=0'); // tried with and without this. Makes no difference
$dbHndl->beginTransaction();
for ($i=0; $i<sizeOf($sqlCmdArr); $i++) {
if ( $i % 2 == 0 ) {
$sqlCmd = $sqlCmdArr["sqlCmd" . $i/2];
$bindArr = $sqlCmdArr["bindArr" . $i/2];
try {
$sqlCmdHndl = $dbHndl->prepare($sqlCmd);
$sqlCmdHndl->execute($bindArr);
}
catch(PDOException $excptHndl) {
$dbHndl->rollback();
$msgObj = new msgClass();
$msgObj->rptExcptDtls ($fctNm, $frmFct, "ERR: Failed to execute sqlCmd = $sqlCmd<br />with bindArr = <pre>" . print_r($bindArr) . "</pre><br />" , $excptHndl);
unset($msgObj);
}
} // End if
} // End for
$dbHndl->commit();
} // End execXaction
This is the calling test code:
Code:
$sqlCmdArr = array("sqlCmd0"=>"insert into x1 (c1, c2) values (:v0, :v1)",
'bindArr0'=>array(':v0' => 10,
':v1' => 'c1 is 10'),
"sqlCmd1"=>"update x1 set c2 = 'x-action test' where c1 = :v0",
'bindArr1'=>array(':v0' => 1),
"sqlCmd2"=>"update x1 set c2 = 'x-action test2' where c1 = :v0",
'bindArr2'=>array(':v0' => 0),
"sqlCmd3"=>"update x1xxxx set c2 = 'make it all fail' where c1 = :v0",
'bindArr3'=>array(':v0' => 0)
);
$dbObj = new dbClass();
$dbObj->execXaction($fctNm, $dbObj, $sqlCmdArr);
mysql> select * from x1;
+------+---------+
| c1 | c2 |
+------+---------+
| 0 | c1 is 0 |
| 1 | c1 is 1 |
+------+---------+
2 rows in set (0.00 sec)
This is the state after the code has failed and returned the error message just below:
(If the code were working correctly, this state should be the same as the original. So it looks as if autocommit is still enabled.)
mysql> select * from x1;
+------+----------------+
| c1 | c2 |
+------+----------------+
| 0 | x-action test2 |
| 1 | x-action test |
| 10 | c1 is 10 |
+------+----------------+
3 rows in set (0.00 sec)
The error message returned by the catch clause:
PHP EXCEPTION:
Script..........: testDbCmds.php
Functie.........: execXaction
Geroepen door...: main
Meelding........: ERR: Failed to execute sqlCmd = update x1xxxx set c2 = 'make it all fail' where c1 = :v0
with bindArr = 1
Exception.......: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tobivroegh_tobi_live.x1xxxx' doesn't exist
Can anyone see what I might be doing wrong?
Thanks for your help.
Andynic