New year – new behaviour ๐
Everyone who had experience in Oracle and some frameworks /or at least ADO libs/ experienced the problem of ORA-01704: string literal too long. For the people who hadn’t pleasure to work with oracle – Oracle accept strings in SQL to be not longer than 4000 characters. I always had problems with this issue, because when the sting is too long you need to treat it with oracle variables and binding them in strange way /at least not with ordinary SQL/. The only way found so far is the solution of ADOdb library.
And because we need to work with Oracle all the time I gave a task to my colleagues Nedko Penev and Bobby Borisov to write this behaviour for CakePHP. So here is the code:
/**
* CakePHP Clob Behaviour
* Preparing and inserting the clob values in the database
*
* Authors: Bobby Borisov and Nedko Penev
*/
class ClobBehavior extends ModelBehavior
{
var $model;
var $time_end;
var $time_start;
var $saved = array(); // array to store clob values
/*
* Initialize method
*/
function setup(&$model)
{
$this->model = $model;
}
/*
* Keep all clob values and makes them null before insert
*/
function beforeSave()
{
$this->saved = $this->model->data[$this->model->name];
foreach($this->model->_schema->value as $key => $value)
{
//for cakephp clob fields is 'text'
if( $value["type"]=='text')
$this->model->data[$this->model->name][$key] = NULL;
}
return true;
}
/*
* Updates table with saved clob values
*/
function afterSave()
{
//get existing db connection
$db =& ConnectionManager::getDataSource($this->model->useDbConfig);
//id of the record to be upated
$id = (!$this->model->id) ? $this->model->getLastInsertId() : $this->model->id;
$fields = array();// array with the fields to be updated
foreach($this->model->_schema->value as $key => $value)
{
if( $value["type"]=='text')
{
$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
".$this->model->table."
SET
".$set_stmt."
WHERE
ID = ".$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 query\n");
$cnt = 0;
foreach($fields as $key => $value)
{
if ( !$mylob[$cnt++]->save($this->saved[$value]) )
{
OCIRollback($db->connection);
die("Unable to update clob\n");
}
}
}
OCICommit ($db->connection);
OCILogOff ($db->connection);
OCIFreeStatement($stmt);
}
}
?>
Short explanation what this behaviour doing:
The problem with long string exists only when we inserting or updating database records. select working just fine. So this behaviour works only for Insert and Update actions. To make our job easier we presumed that we can use only update statement, because this behaviour apply afterSave() , so even if it’s insert the second action will be always update.
The behaviour work with 2 actions beforeSave() and afterSave(). In beforeSave() the script set all fields in the database which are “text” /Basically in CakePHP text is indication for clob/ to null. This allowing normal execution of the statement. Second activity is afterSave – then the script construct the custom sql with all “text” fields and their values which fulfil the Oracle syntax for Clob save. In afterSave we using oci native php functions because this is the only way to implement it.
How to use it.
1. Save the code above in file clob.php and store it in /app/mocdels/behaviours/
2. It’s possible to set this only to specific controllers, but I prefer to set it globally at app_model.php
That’s it – now all Clobs will be properly stored.
Note: This code is tested and working for CakePHP 1.2
Happy Baking
Thanks for the great job with the behavior. We started using it and found that it had some issues.
I published our changes at http://www.thompsonbd.com/blog.php?title=CakePHP+Clob+Behavior
Actually, I have a working dbo_oracle.php if you’re interested (for cakePHP v1.1.19). It uses the same technique, bind variables, but directly in the driver, so no need for a behavior.
I posted a ticket a long time ago, but it didn’t go very far: https://trac.cakephp.org/ticket/2294
I’ll just add the file to the ticket if you want to download it.
here it is:
https://trac.cakephp.org/attachment/ticket/2294/dbo_oracle.php
@cedsav, – nice approach, but my philosophy is not to touch cake libs at all ๐ I would really like if CakePHP support this feature natively, but so far it isn’t and our way was to create behaviour.
I primary working in CakePHP 1.2 and because there are Behaviours as feature we decided to use it.
Anyway, there are many other problems with dbo_oracle which are not sorted so far. The first one which I can mention is not infinite IN() function. That function accept max 1000 elements and CakePHP use it a lot with the relations.
Hopefully one day we will have fully working Cake with Oracle. ๐
Pingback: CakePHP : signets remarquables du 06/01/2009 au 07/01/2009 | Cherry on the...