#40787 [Opn->Fbk]: Error trying to insert into a CLOB column when using multi-byte charset.

[tony2001]

 ID:          40787
 Updated by:  [EMAIL PROTECTED]
 Reported By: jarismar at adplabs dot com dot br
-Status:      Open
+Status:      Feedback
 Bug Type:    PDO related
 PHP Version: 5.2.1
 New Comment:

Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.2-win32-latest.zip




Previous Comments:
------------------------------------------------------------------------

[2007-03-12 17:36:53] jarismar at adplabs dot com dot br

Description:
------------
I'm using pdo_oci and oracle 10g (10.2).

Trying to insert into a CLOB column using multi-byte charset (AL32UTF8)
results on the following error:

ORA-01461: can bind a LONG value only for insert into a LONG column.

The column datatype in not LONG it's a CLOB !

Changing the connection charset to use WE8ISO8859P1 or any other single
byte charset solve the problem (the insert command ends with no error),
but I loose all non ISO characters (my data gets corrupted).

Reproduce code:
---------------
try {
  $sDSN = "oci:dbname=$sConId;charset=AL32UTF8";
  $oPDO = new PDO($sDSN, $sUserName, $sPassword);
  $oPDO->beginTransaction();
  $oStmt = $oPDO->prepare("insert into test_clob (id, data) values
(:id, EMPTY_CLOB())");
  $iID = 1;
  $oStmt->bindParam(':id', $iID);
  if ($oStmt->execute()) {
    $oStmt = $oPDO->prepare("update test_clob set data=:value where
id=1");
    $sData = str_repeat('x', 65535);
    $oStmt->bindParam(':value', $sData);
    if ($oStmt->execute() === false) {
      throw new Exception('Error on update clob');
    }
  } else {
    throw new Exception('Error on insert EMPTY_CLOB');
  }

  $oStmt = $oPDO->prepare("select data from test_clob where id =
:id");
  $oStmt->bindParam('id', $iID);
  $oStmt->execute();
  $oResult = $oStmt->fetch();
  echo 'Read '.strlen(stream_get_contents($oResult['DATA'])).'
characters <br>';
  $oPDO->commit();

} catch (Exception $oE) {
  if ($oStmt) {
    echo '<pre>';print_r($oStmt->errorInfo());echo "</pre><br>\n";
  }
  echo $oE->getMessage()."<br>\n";
}
$oPDO = null;

Expected result:
----------------
Read 65535 characters

Actual result:
--------------
Array
(
    [0] => HY000
    [1] => 1461
    [2] => OCIStmtExecute: ORA-01461: can bind a LONG value only for
insert into a LONG column
 (ext\pdo_oci\oci_statement.c:142)
)


Error on update clob


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


-- 
Edit this bug report at http://bugs.php.net/?id=40787&edit=1