Blog | Lars Pohlmann - Webdevelopment

Posted April 14, 2011 by admin & filed under symfony.

There is a nasty bug in symfony (or better pdo_oci) that prevents symfony from working properly with clob fields in oracle when using doctrine.

Here is what I’ve done to make working with clob fields possible:

Imagine you have a table like this, where “nasty_clobfield” is a clob:

NastyTable:
  connection: doctrine
  tableName: NASTY_TABLE
  columns:
    id_nasty_table:
      type: integer(16)
      fixed: false
      unsigned: false
      primary: true
      sequence: SEQ_NASTY_TABLE
    nice_stringfield:
      type: string(255)
    nasty_clobfield:
      type: string(4000)

First you need to remove the field from the yaml file. So symfony doesn’t generate the getters and setters, as we want to write them by hand.

We need a class that enables us to use oci8 instead of pdo_oci to access this field. We need to take care of the connection parameters by ourselves. I haven’t found a way to extract them from symfony. You could either define them in your app.yml for each enviroment, but this is far from being optimal. I decided to parse the connection string and extract the parameters from it, but this will only work if your connection string is build like this:

all:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn:        oci://databasename:password@192.168.0.1/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = SOMESERVICENAME)))
      encoding:   utf8
      persistent: true
      pooling:    true

And here is the important part of the class to access the database.

class DB {
   private static $_conn;
 
   static function getOciConnection()
    {
      if( !isset( self::$_conn ) )
      {
        $options = Doctrine_Manager::getInstance()->getCurrentConnection()->getOptions();
 
        $mydsn = preg_match( "/dbname=(.+)$/" , $options['dsn'] , $matches );
 
        self::$_conn = oci_connect( $options['username'] , $options['password'] , $matches[1] );
      }
      return self::$_conn;
    }
 
    static function getClob( Doctrine_Record $doctrineObj , $field )
    {
      if( !$doctrineObj->getPrimaryKey() )
      {
        return null;
      }
      $t = $doctrineObj->getTable();
 
      $pk_name  = $t->getIdentifier();
      $table    = $t->getTableName();
      $pk       = $doctrineObj->getPrimaryKey();
      $return   = '';
 
      $conn = self::getOciConnection();
 
      $sql = "SELECT $field FROM $table WHERE $pk_name=:pk";
 
      try{
        $stmt = oci_parse( $conn , $sql );
        oci_bind_by_name( $stmt , ":pk" , $pk );
        oci_execute( $stmt );
        oci_fetch( $stmt );
 
        $bdatalob = oci_result( $stmt, strtoupper( $field ) );
 
        if( is_object( $bdatalob ) )
        {
          $return = @$bdatalob->load();
        }
        else
        {
          $return = '';
        }
      }
      catch(exception $e)
      {
        oci_free_statement( $stmt );
        return null;
      }
      oci_free_statement( $stmt );
      return $return;
    }
}

Now we can write our getter in “NastyTable.class.php”:

  function getNastyClobfield()
  {
    if( !$this->_clobs['nasty_clobfield']  )
    {
      $this->_clobs['nasty_clobfield'] = db::getClob( $this , 'nasty_clobfield' );
    }
    return $this->_clobs['nasty_clobfield'];
  }

The setter is a tad more complicated as we don’t want to save the value in the database before the “save” method was called. So we save the value in a variable and write it to the database in the save method:

  function setNastyClobfield( $data )
  {
    if( $this->_clobs['nasty_clobfield'] != $data )
    {
      $this->_clobs['nasty_clobfield'] = $data;
      $this->_edited_clobs = array_unique( array_merge( $this->_edited_clobs , array( 'nasty_clobfield' ) ) );
    }
  }
 
  function save(Doctrine_Connection $conn = null)
  {
    $conn = $conn ? $conn : $this->getTable()->getConnection();
    $conn->beginTransaction();
    try
    {
      parent::save($conn);
 
      foreach( $this->edited_clobs as $fieldname )
      {
        db::setClob( $this , $fieldname , $this->_clobs[$fieldname] , $conn );
      }
      $conn->commit();
      return $this;
    }
    catch (Exception $e)
    {
      $conn->rollBack();
      throw $e;
    }
}

And here is the method to save our clob (in the class “DB”). Saving clobs actually works with pdo, so I’ll use that instead of OCI8:

    static function setClob( $doctrineObj , $field , $data , $conn = null )
    {
      $pk_name  = $doctrineObj->getTable()->getIdentifier();
      $table    = $doctrineObj->getTable()->getTableName();
      $pk       = $doctrineObj->getPrimaryKey();
 
      if( $conn )
      {
        $db = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
      }
      else{
        $db = $conn->getDbh();
      }
 
      $sql = "UPDATE $table SET $field = ? WHERE $pk_name=$pk";
 
      $stmt = $db->prepare( $sql );
      $stmt->bindParam(1, $data, PDO::PARAM_STR);
 
 
      $stmt->execute();
      $stmt->closeCursor();
    }

That’s it. Now we can access the clob fields throughout our symfony application in the same way we access the other fields.

Posted November 2, 2010 by admin & filed under symfony.

Dies ist ein Testpost.

Schauen wir mal, ob Syntaxhighlighting funktioniert. Hier ein kleiner Codeschnipsel aus meinem aktuellen Projekt:

public function getComments( $orderby = 'ASC' )
    {
      $q = Doctrine_Query::create()
          ->from( 'RatingComment rc' )
          ->where( 'rc.rating_id = ' . $this->getId() )
          ->orderBy( 'rc.created_at ' . $orderby );
      return $q;
    }