PDO, transactions and try - catch exceptions


Add / read comments

Once I wrote application which was using simple mysql functions to query database. But my friend asked me to use PostgreSQL database instead MySQL. And that was a problem because it meant to rewrite all the queries. But I didn't rewrote them, I discovered database abstract layers. I used MDB2 that time but today I am using PDO because some hosting companies doesn't support PEAR.

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

The second problem which I was dealing with was the consistency of data in database. It means if you are inserting for example informations about some object to one database and then it's necessary to insert other information to the second table and tie them with foreign key. If error occurs during the process it may happen that we have information in first table but not in the second one. The way how to avoid such scenario is to use transactions.

So I discovered very usefull to use combination of data-access abstraction layer and transactions. I usually do it this way: first I create instance of PDO object in my application's base abstract class which represent a connection to database:

abstract class MyBaseClass {
  // private variables
  private $pdoAbstractDb = null;
  private $server_db = 'your.server.com'; 
  private $database = 'database_name';
  private $user_db = 'user';     
  private $password_db = 'password';	
  // array of attributes (persistent connection, utf-8 coding)
  private $array_opt = array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"); 
  // protected function
  protected function getPDO()
    // create a new database connection
    try {

      // instantiate a PDO instance	    	    
      $this->pdoAbstractDb = new PDO("mysql:dbname=$this->database; host=$this->server_db", 
			             $this->user_db, $this->password_db, $this->array_opt );
      // set creation of "exceptions" instead errors
      $this->pdoAbstractDb -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      return $this->pdoAbstractDb;
    catch (PDOException $e)
     throw new Exception('Creation of PDO layer has failed: '.$e->getMessage());

Then I inherit from private method in class which extends parent class:

final class MyClass extends MyBaseClass {
  // private variable
  private $pdo_db = null;
  // constructor of object instance 
  public function __construct(){
    // insert inherited instance of PDO object into variable
    $this->pdo_db = parent::getPDO();

And then in some object's method I use transaction and try - catch construction for exceptions:

public function myFunction() {
  // try - catch method
    // begin a transaction
    $this->pdo_db -> beginTransaction();
    // first SQL query
    $this->pdo_db->exec("INSERT INTO table (col1, col2) VALUES ('1','2')");
    // ... other queries
    // last SQL query
    $this->pdo_db->exec("DELETE FROM table WHERE column = 'something'");
    // no query has failed => commit the transaction
    $this->pdo_db -> commit();
  catch(PDOException $e) {
    // must rollback the transaction
    $this->pdo_db -> rollback();        
    throw new Exception($e->getMessage());

So I hope this helps someone. If you have other experiences, please share with us at article's discussion.


Add / read comments


Share, follow or connect with me.