douliaodun9153
douliaodun9153
2011-04-05 08:31
浏览 51

使用PHP-ODBC处理CLOB列

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. I'm writing a new feature that needs a CLOB column so it can store arbitrarily large texts but I can't find the way to manipulate it via good old ODBC functions.

For writing, I've tried this approach:

<?php

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

?>

But as soon as the string is larger than 4,000 bytes:

SQL error: [Microsoft][ODBC driver for Oracle]Error en el parámetro 2: Datos truncados, SQL state 01004 in SQLExecute

... which roughly means "Truncated data". I have a similar problem when reading: it works fine until I receive more than 4,000 bytes.

Is it possible to manipulate a CLOB column with more than 4,000 bytes using PHP ODBC functions?

(My alternative is to store stuff in files and only save the file path on DB but I'd like to avoid that if possible.)


Update: Following Justin Cave's advice, I've dumped Microsoft's ODBC Driver in favour of Oracle's and my code inserts large strings without changes. My question now is how to read the CLOB column. Reading it as-is:

$sql = 'SELECT FOO_ID, FOO_CLOB FROM FOO';
$res = odbc_exec($conn, $sql);
while($row = odbc_fetch_array($res)){
    // ...
}

... triggers this when running odbc_fetch_array():

ORA-01861: literal does not match format string

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doufei16736
    doufei16736 2011-04-05 08:51
    已采纳

    Do you have to use the Microsoft ODBC Driver for Oracle? That driver does not support the CLOB data type among various other issues. If you use the Oracle ODBC driver instead, the driver will support CLOBs.

    点赞 评论
  • doulongsi1831
    doulongsi1831 2011-04-15 12:56

    In order to read you , basically, have to chunk you way through the data.

    Something like --

    define ("MAX_LONGREAD_LENGTH", 4000);
    
    $res = odbc_exec($con, $sql);
    
    odbc_binmode($res, ODBC_BINMODE_RETURN);
    
    $oflen = odbc_field_len($res, 1);
    
    if ( $oflen >= MAX_LONGREAD_LENGTH)
    {
      $out = '';
    
      do
      {
        $temp = odbc_result($res, 1);
    
        if ($temp != null) $out .= $temp;
      }
    
      while ($temp != null);
    }
    
    else
    
    {
      $out = odbc_result($res, 1);
    }
    

    -- which, basically, either -

    1. Reads all the data if <6000 chars, or
    2. Repeatedly, reads and appends chunks of data until no more is returned.
    点赞 评论

相关推荐