Saving and Retrieve LOBs in Oracle

 
/*
  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
  [email protected]

*/
  function parseQuery($conn, $query)
  {
    $stmt = OCIparse($conn,$query);
    if (! $stmt)
    {
      echo "Error parsing SQL statment:
";
  	  $error = OCIError();
  	  echo "$errorn";
  	  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;
  }