PHPBuilder - Saving and Retrieve LOBs in Oracle



RSS Twitter
Tips Databases

Saving and Retrieve LOBs in Oracle

by: Steven Sutton
|
October 5, 2000

 
/*
  This is code that I altered from a project I am working on.  
  I have simplified it by taking out a confusing while loop and switch
  statements to build my query strings.  I have also changed the database names
  and field names.

  I don't actually have this table in my database and my DBA won't let me create
  tables.  So I don't guarantee that this code will work as is, it may need some minor
  bug fixes.  

  My comments about the limitations of saving LOB data are my observations on how
  I got it to work.  So when I say you can't save a null string or that you must 
  save each LOB field individualy it is because I am ignorant of how to do it.  If 
  someone does know how to do either of these please let me know.

  This should be the table layout as I imagined it to work with this script:

  CREATE TABLE session
 (
  session_id                 VARCHAR2(32) NOT NULL,
  session_timestamp          DATE,
  customer_name              VARCHAR2(64),
  customer_age               NUMBER(2),
  address_note               CLOB,
  sales_note                 CLOB
 )

  I hope this helps, if you have any questions feel free to e-mail me.  
  
  Steven Sutton
  meabhair@yahoo.com

*/
  function parseQuery($conn, $query)
  {
    $stmt = OCIparse($conn,$query);
    if (! $stmt)
    {
      echo "Error parsing SQL statment:
"; $error = OCIError(); echo "$error\n"; exit(); } return $stmt; } function saveSessionData($conn, $SessionId, $CustomerName, $CustomerAge, $AddressNote, $SalesNote) { //This function assumes you already have a row in the database //If not make the first query an insert statement then do an update //for your LOBs //Save all the fields that are not LOBs $query = "UPDATE session SET customer_name = '$CustomerName', "; $query .= " customer_age = $CustomerAge, session_timestamp = SYSDATE"; $query .= " where session_id = '$SessionId'"; $stmt = parseQuery($conn,$query); OCIExecute($stmt); OCIFreeStatement($stmt); //Save the LOB Fields //I wasn't able to make the LOB save a null string //so if the field should be empty I save a space //and trim the results when I use them later if (!($AddressNote) ) { $AddressNote = " "; } $clobQuery = "UPDATE session SET "; $clobQuery .= "address_note = EMPTY_CLOB() WHERE session_id = '$SessionId' "; $clobQuery .= "returning address_note into :the_clob"; $clob = OCINewDescriptor($conn, OCI_D_LOB); $clobStmt = parseQuery($conn,$clobQuery); OCIBindByName($clobStmt, ':the_clob', &$clob, -1, OCI_B_CLOB); OCIExecute($clobStmt, OCI_DEFAULT); //This is where the data is actually getting saved into the database if ($clob->save($AddressNote)) { OCICommit($conn); } else { print ("Problems updating address_note"); OCIFreeDescriptor($clob); OCIFreeStatement($clobStmt); exit; } OCIFreeDescriptor($clob); OCIFreeStatement($clobStmt); //The code below is exactly the same as the last LOB except for the field names //I only show it so that you understand that each LOB has to be saved seperatly if (!($SalesNote) ) { $SalesNote = " "; } $clobQuery = "UPDATE session SET "; $clobQuery .= "sales_note = EMPTY_CLOB() WHERE session_id = '$SessionId' "; $clobQuery .= "returning address_note into :the_clob"; $clob = OCINewDescriptor($conn, OCI_D_LOB); $clobStmt = parseQuery($conn,$clobQuery); OCIBindByName($clobStmt, ':the_clob', &$clob, -1, OCI_B_CLOB); OCIExecute($clobStmt, OCI_DEFAULT); //This is where the data is actually getting saved into the database if ($clob->save($SalesNote)) { OCICommit($conn); } else { print ("Problems updating sales_note"); OCIFreeDescriptor($clob); OCIFreeStatement($clobStmt); exit; } OCIFreeDescriptor($clob); OCIFreeStatement($clobStmt); } function getSessionData($conn, $SessionId) { $query = "select session_id, session_timestamp, customer_name, customer_age, address_note, sales_note"; $query .= " from session "; $query .= " where session_id='".$SessionId."'"; $stmt = parseQuery($conn,$query); $NewData = array(); OCIDefineByName($stmt,"session_id",&$NewData["session_id"]); OCIDefineByName($stmt,"session_timestamp",&$NewData["session_timestamp"]); OCIDefineByName($stmt,"customer_name",&$NewData["customer_name"]); OCIDefineByName($stmt,"customer_age",&$NewData["customer_age"]); OCIDefineByName($stmt,"address_note",&$NewData["address_note"]); OCIDefineByName($stmt,"sales_note",&$NewData["sales_note"]); OCIExecute($stmt); OCIFetch($stmt); if (is_object($NewData["address_note"])) { $NewData["address_note"] = $NewData["address_note"]->load(); $NewData["address_note"] = trim($NewData["address_note"]); } if (is_object($NewData["sales_note"])) { $NewData["sales_note"] = $NewData["sales_note"]->load(); $NewData["sales_note"] = trim($NewData["sales_note"]); } OCIFreeStatement($stmt); return $NewData; }

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Steven Sutton

Comment:



Comment:

(Maximum characters: 1200). You have characters left.