PHP Tips - PDO Transactions |Tech Lull

To do DB transactions in PDO, first make sure your DB engine supports transactions. If you are using MySQL, then you need to alter your tables from the default engine to something like InnoDB. ALTER TABLE table_name ENGINE = innodb; let $db be your database handle with the PEAR API, you would of done something like:


try{
    $db->autoCommit(false);
    ...statements....
    $db->commit();
}catch(Exception $e){
    $db->rollback();
}
$db-><strong>autoCommit(true);</strong>

With PDO, it's even easier:


try{ 
    $db->beginTransaction();
    ...statements...
    $db->commit();
}catch(PDOException $e){
    $db->rollback();  
}

The annoying, Gotcha! So, I had done this but kept getting the following error There is no active transaction The problem is that my statements contain references to different database handles and this is not permissible. Even though, in my case, I was using a singleton pattern to get (what I believed to be) one reference to the database, but was not. (I later found the bug and corrected it) My workaround was to optionally pass the existing $db handle to my other statements. Here PDO_DB_Connect is my PDO singleton class.


    SampleTable::updateRecord($dbh);
    SampleLogTable::insertRecord($dbh);
    ..
    ..
    
    public static function updateRecord($dbh = null){
        if(!$dbh){
            $dbh = PDO_DB_Connect::getHandle ( 'my_db_name' ) ;
         }
    }

After I pass in the handle, I no longer get the error. Of course, now that the singleton class is fixed, I don't need to worry about this. But, if you get this error, check that it is the same db connection throughout.

back to PHP tips...

Similar entries

PDO Transactions