2 Replies Latest reply: Apr 19, 2011 1:22 PM by cj RSS

    Read CLOB with ODBC

    633274
      I maintain a legacy application that runs on PHP/5.2.6 under Windows Server 2003 and connects to a remote Oracle 10g server through ODBC (not OCI8). I've been asked to write a new feature that requires storing arbitrarily large texts. The simplest place to do so is of course a CLOB column.

      I've learnt that there are two different ODBC drivers for Oracle provided by different vendors (Microsoft and Oracle itself). The app was using Microsoft's driver which apparently does not support CLOB columns at all. I've been able to switch to Oracle's driver with minor changes and I'm now able to write into CLOB columns:

      <pre><?php
      $sql = 'INSERT INTO FOO (FOO_ID, FOO_CLOB) VALUES (?, ?)';
      $stmt = odbc_prepare($conn, $sql);
      $res = odbc_execute($stmt, array(1, 'Very large string'));
      ?></pre>

      My question is how to read the CLOB back. The straightforward method triggers an error:

      <pre><?php
      $sql = 'SELECT FOO_ID, FOO_CLOB FROM FOO';
      $res = odbc_exec($conn, $sql);
      while($row = odbc_fetch_array($res)){ // ORA-01861: literal does not match format string
      // ...
      }
      ?></pre>

      The PHP ODBC extension does not seem to provide dedicated functions for CLOB columns. I can split the column with DBMS_LOB.SUBSTR() and read each record with a loop of SELECT queries but it's such an ugly hack.

      Is there a direct way to read a CLOB with PHP+ODBC?

      --------------

      Update

      My best solution so far:

      <pre><?php

      $sql = 'SELECT DBMS_LOB.GETLENGTH(FOO_CLOB) AS FOO_CLOB_LENGTH FROM FOO WHERE FOO_ID=?';
      $res = odbc_prepare($conn, $sql);
      $params = array(
           33,
      );
      odbc_execute($res, $params);
      $row = odbc_fetch_array($res);
      $foo_clob_length = $row['FOO_CLOB_LENGTH'];


      $sql = 'SELECT DBMS_LOB.SUBSTR(FOO_CLOB, ?, ?) AS FOO_CLOB_FRAGMENT FROM FOO HERE FOO_ID=?';
      $foo_clob = '';
      for($i=0; $i<$foo_clob_length; $i+=4000){
           // You can't prepare once and execute many: SQL error: Failed to fetch error message, SQL state HY000 in SQLExecute
           $res = odbc_prepare($conn, $sql);
           $params = array(
                4000,
                $i+1,
                33,
           );
           odbc_execute($res, $params);
           $row = odbc_fetch_array($res);
           $foo_clob .= $row['FOO_CLOB_FRAGMENT'];
      }

      ?></pre>

      Edited by: kAlvaro on Apr 15, 2011 10:07 AM
        • 1. Re: Read CLOB with ODBC
          633274
          I've finally found the answer:

          <pre><?php

          $all_items = array();

          $sql = 'SELECT FOO_ID, FOO_CLOB FROM FOO';
          $res = odbc_exec($conn, $sql);
          odbc_binmode($res, ODBC_BINMODE_RETURN); // Prevent errors when fetching rows
          while($row = odbc_fetch_array($res)){
               $item = array(
                    'FOO_ID' => $row['FOO_ID'],
                    'FOO_CLOB' => $row['FOO_CLOB'], // First 4,000 bytes
               );

               while( $tmp = odbc_result($res, 'FOO_CLOB') ){ // Rest of chunks
                    $item['FOO_CLOB'] .= $tmp;
               };
               
               $all_items[] = $item;
          }</pre>
          • 2. Re: Read CLOB with ODBC
            cj
            Thanks for sharing the solution.