Updated CakePHP Clob Behavior

3:07pm March 19, 2008

Updated: I've released a working version of the oracle connection class that does not need behaviors to utilize bind variables.

We ran into some more issues using the CakePHP Clob behavior that Bobby Borisov and Nedko Penev wrote. Basically, the issue was that the original file used a reference variable to the model that was supposed to be using the behavior. However, it seemed to always doing something very by expanding the model array everytime the behavior was called.

It made it impossible to have multiple models using the clob behavior or doing multiple saves in the same controller. Downloads: CakePHP Clob Behavior, CakePHP Blob Behavior


<?php
/**
 * CakePHP Clob Behaviour
 * This will take all models that have a 'text' field in them, which is an Oracle
 * clob field and saves the value until after the save has been completed.  It
 * then saves the clob field using a bind variables.
 *
 * Author: Chris Thompson
 *         www.thompsonbd.com
 *
 * Original Authors: Bobby Borisov and Nedko Penev
 *                   http://nik.chankov.net/2008/01/03/cakephp-and-oracle-handling-clob-fields/
 *
 */

class ClobBehavior extends ModelBehavior
{
    var $time_end;
    var $time_start;
    var $saved = array(); // array to store clob values

   /**
    * Initialize method
    */
    function setup(&$model) {

    }
/**
 * Takes the model object, locates all text fields and saves the value for later.
 *
 * @param object $model The model that is attempting to save data.
 * @return null nothing is returned
 * @access protected
 */
    function beforeSave(&$model) {
        $this->saved = $model->data[$model->name];
        
        foreach($model->_schema as $key => $value) {
            //for cakephp clob fields is 'text'
            if( $value["type"]=='text' && isset($this->saved[$key])) {
                $model->data[$model->name][$key] = null;
            }
        }
    }
/**
 * Takes the model object, uses the last inserted id, and binds the saved data to the clob fields.
 *
 * @param object $model The model that is attempting to save data.
 * @return null nothing is returned
 * @access protected
 */
    function afterSave(&$model) {
        //get existing db connection
        $db =& ConnectionManager::getDataSource($model->useDbConfig);

        //id of the record to be updated
        $id = (!$model->id) ? $model->getLastInsertId() : $model->id;
        $fields   = array();// array with the fields to be updated
        foreach($model->_schema as $key => $value) {      
            if( $value["type"]=='text' && isset($this->saved[$key])) {
                $fields = am($key, $fields);
            }
        }
            
        if(!empty($fields)) {
            $set  = array(); // set clause in the sql
            $into = array(); // into clause in the sql
                
            foreach($fields as $key => $value){
                $set  = am( $value . " = EMPTY_CLOB()" , $set );
                $into = am(":muclob" . $key            , $into);
            }
                
            $set_stmt  = implode(", ", $set     ); // array to string to fill 'set' clause in the sql
            $returning = implode(", ", $fields  ); // array to string to fill 'returning' clause in the sql
            $into_stmt = implode(", ", $into    ); // array to string to fill 'into' clause in the sql

            $sql = "
                UPDATE
                    ".$model->tablePrefix . $model->table."
                SET
                    ".$set_stmt."
                WHERE
                    " . $model->primaryKey . " = " . $id . "
                RETURNING
                    " . $returning . "
                INTO
                    " . $into_stmt;

            $stmt = OCIParse($db->connection, $sql);
            $cnt  = 0;
            // just see http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_lobs.html
            // you'll get it i'm sure
            foreach($into as $key => $value) {
                $mylob[$cnt] = OCINewDescriptor($db->connection,OCI_D_LOB);
                OCIBindByName($stmt,$value,$mylob[$cnt++], -1, OCI_B_CLOB);
            }
                
            OCIExecute($stmt, OCI_DEFAULT) or die ("Unable to execute queryn");
            $cnt = 0;
            foreach($fields as $key => $value) {
                if (!$mylob[$cnt++]->save($this->saved[$value])) {
                    OCIRollback($db->connection);
                    debug("Unable to update clob");
                }
            } 

            OCIFreeStatement($stmt);  
        }
        
        OCICommit ($db->connection); 
    }
}
?>

Share This

Views 726

Comments 1

Filed under: CakePHP, Oracle, Clob, Behavior

Add Comment

Chris

Hi, You're updated clob behaviour works great. Thanks for your contribution! Regards, Chris