2. PHPRunner General questions
  3. Oracle CLOB and PHPR
This topic is locked

Oracle CLOB and PHPR

 		10/31/2008 8:53:57 AM
PHPRunner General questions
T
 Tane author

Hello,
Anybody have an idea on how to implement updating and inserting of Oracle database's CLOB columns with PHPRunner?
Text area field type in PHPR is capable of holding e.g. 5000 characters, which doesn't fit into an Oracle VARCHAR2 (maximum 4000 char). CLOB datatype in Oracle seems like the only possible option in this case. Fetching CLOB data into an text area is not a problem with PHPR, but if I try to update I receive an Oracle error (ORA-01704: string literal too long Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.).
In PHP language it is possible to handle CLOBs with OCI-Lob.
But how to incorporate this into PHPR? Do I have to modify the PHPR generated PHP code or is there a way to do this with events? Or with something else?
Tane
T
 		Tane author 11/3/2008

Some additional information:
When I look at the *_edit.php file generated by PHPR, I see that there IS code needed to process BLOB (CLOB?) data with Oracle bind variables.
However, if I run similar code from a PHP test script it works properly for CLOB values of, say 5000 characters, but from PHPR generated application I receive "ociexecute() [function.ociexecute]: ORA-01704: string literal too long" if the length of the text area exceeds 4000 chars. The Oracle column is defined as CLOB.
Am I missing something here...?
T
 		Tane author 11/3/2008

Here's a solution in case someone is interested. Seems to work, would be interested to hear about possible caveats :-)
PHPrunner only recognizes BLOBs, not CLOBs which are treated as any strings. Oracle, however, makes distinction between its normal string datatype (VARCHAR2), and longer than 4000 character strings which should be treated as CLOBs and processed via bind variables

  • define table column type as CLOB
  • generate PHPrunner application "normally" and define the corresponding field to be Text Area
    For the generated _edit.php file (and _add.php file?) the following edits are needed:
  • find the field processing part for the CLOB field (example below for field called BLOBDATA)
  • make the two changes that are commented in the example
    // processing BLOBDATA - start

    $value = postvalue("value_BLOBDATA");

    $type=postvalue("type_BLOBDATA");
    // Trick PHPrunner to think there is a BLOB even though we only have a CLOB.

    // The index of the array is the actual column name.

    $blobs['BLOBDATA'] = $value;



    if (in_assoc_array("type_BLOBDATA",$_POST) || in_assoc_array("value_BLOBDATA",$_POST) || in_assoc_array("value_BLOBDATA",$_FILES))

    {

    $value=prepare_for_db("BLOBDATA",$value,$type);

    }

    else

    $value=false;

    if(!($value===false))

    {

    // In place of the actual value of the CLOB we define an empty CLOB.

    // The real value will be bound in the generated SQL statement.

     $evalues["BLOBDATA"]= ' EMPTY_CLOB() ';

    }

    // processibng BLOBDATA - end
    Replace the line similar to

    OCIBindByName($stmt,":bnd".$idx,$locs[$ekey],-1,OCI_B_BLOB);

    by the following:

    oci_bind_by_name($stmt,":bnd".$idx,$locs[$ekey],-1,SQLT_CLOB);
    Note that the above mentioned edits are always lost when the application is regenerated. Therefore it might be a good idea to make edits last for all CLOB fields.