Posted by & 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.

One Response to “Workaround for the nasty oracle clob bug in symfony”

  1. Gris

    Hi, i try this code, the getter works fine, but the setter do the same as it i use the default setter and when i put strings longer than 4000 chars it crashes with the error:

    500 | Internal Server Error | Doctrine_Connection_Oracle_Exception
    SQLSTATE[HY000]: General error: 1461 OCIStmtExecute: ORA-01461: can bind a LONG value only for insert into a LONG column
    (/tmp/PDO_OCI-1.0/oci_statement.c:142)

    i write the code below from examples in the web, and executed from a php file out of symfony it works, but when i put this in the setClob() method and i reference from the save method of the object my page keeps loading and freezes…. do you have some clue to help me?

    /*
    $conn = oci_connect(‘USER’, ‘PASSWORD’, ‘SERVER’);
    $pk_name = $doctrineObj->getTable()->getIdentifier();
    $table = $doctrineObj->getTable()->getTableName();
    $pk = $doctrineObj->getPrimaryKey();

    $query = ‘update ‘.$table.’ set ‘.$field.’ = empty_clob() where ‘.$pk_name.’=:id returning ‘.$field.’ into :myclob';

    $stid = oci_parse($conn, $query);
    $clob = oci_new_descriptor($conn, OCI_D_LOB);
    oci_bind_by_name($stid, “:id”, $pk);
    oci_bind_by_name($stid, “:myclob”, $clob, -1, OCI_B_CLOB);
    oci_execute($stid, OCI_DEFAULT);
    $clob->save($data);
    oci_commit($conn);
    oci_free_statement($stid);
    $clob->free();

    oci_close($conn);
    */

    thanks!

Leave a Reply

  • (will not be published)