Solved: PHP PDO with MySQL set transaction

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:

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

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

 

Hi Ent,
Thanks for the reply.

I tried your suggestion, but whether it's rollback or rollBack does not seem to matter.

I've re-read the PHP manual on the subject and also some tutorials and have made a very simple example without arrays (please see below). I think it is 100% according to Hoyle. But either the 100% is wishful thinking, or there is a parameter somewhere that needs to be set differently. In that vain I've checked php.ini and searched around for mysql configuration parameters but can find nothing that seems relevant.

Code:

<?php
 session_start(); 
 $GLOBALS['PROGRAM_NAME'] = "testDbCmds.php";
 $fctNm = "main";
        
   try {
         $dbHndl = new PDO("mysql:host=localhost; dbname=tobidb; charset=utf8", "tobi", "pw");
         $dbHndl->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $dbHndl->beginTransaction();

         $sqlCmd  = "insert into x1 (c1, c2) values (:v0, :v1)";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 10);
         $cmdHndl->bindValue(":v1", 'c1 is 10');
         $cmdHndl->execute();
         
         $sqlCmd  = "update x1 set c2 = 'x-action test' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 0);
         $cmdHndl->execute();
 
         $sqlCmd  = "update x1 set c2 = 'x-action test2' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 1);
         $cmdHndl->execute();
                       
         $sqlCmd  = "update x1xxxx set c2 = 'make it all fail' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 0);
         $cmdHndl->execute();
          
         $dbHndl->commit();
   }
   catch(PDOException $excptHndl) {
          $dbHndl->rollBack();  // ik  heb ook rollback() geprobeerd
          echo "ERR: Failed to execute sqlCmd = $sqlCmd<br />";
          echo "Exc msg:  " . $excptHndl->getMessage() . "<br />";
          echo "In file:  " . $excptHndl->getFile() . "<br />";
          echo "At liine: " . $excptHndl->getLine() . "<br />";          
   } 
?>

DB commands used from the mysql command line interface:
create table x1 (c1 integer, c2 varchar(30));
insert into x1 (c1, c2) values (0, 'c1 is 0');
insert into x1 (c1, c2) values (1, 'c1 is 1');

Thanks for your help.
Andynic

 

Hi Ent,

Thanks again for the reply. You hit the nail on the head. I did not realize that the default engine is MyISAM.
Explicitly specifying the engine in the create table command solved it:
create table x1 (c1 integer, c2 varchar(30)) ENGINE=InnoDB;

Andynic