1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: PHP PDO with MySQL set transaction

Discussion in 'Software Development' started by andynic, Feb 25, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. andynic

    andynic Thread Starter

    Joined:
    May 25, 2007
    Messages:
    385
    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
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    I've only touched on PDO, so I don't know whether you're doing it right.
    The one thing that does strike me is you have rollback where the docs have rollBack (capital B). That could certainly cause issues.
     
  3. andynic

    andynic Thread Starter

    Joined:
    May 25, 2007
    Messages:
    385
    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
     
  4. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Since rolling back is an ability of the database engine and not of PHP, I can't see how variations in the precise PHP should make a difference. I'm afraid I don't have time to look at it in the detail it needs right now.
    (That said, there are some things that would make your original code easier to understand and possibly less error prone. The whole index by halfs thing, and indeed concatenating to make a string index, is kind of messy. Passing two arrays, or an array of 2-tuples/arrays, would be cleaner and less abuse of PHPy.)

    I assume you've checked, as per the many comments in the doc, that it's not a MyISAM table.
     
  5. andynic

    andynic Thread Starter

    Joined:
    May 25, 2007
    Messages:
    385
    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
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved MySQL transaction
  1. riiiiiya
    Replies:
    0
    Views:
    252
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1143774

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice