php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #40787 Error trying to insert into a CLOB column when using multi-byte charset.
Submitted: 2007-03-12 17:36 UTC Modified: 2021-07-18 04:22 UTC
Votes:41
Avg. Score:4.7 ± 0.6
Reproduced:35 of 37 (94.6%)
Same Version:8 (22.9%)
Same OS:7 (20.0%)
From: jarismar at adplabs dot com dot br Assigned: cmb (profile)
Status: No Feedback Package: PDO OCI
PHP Version: 5.2.1 OS:
Private report: No CVE-ID: None
View Developer Edit
 [2007-03-12 17:36 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-03-12 18:27 UTC] jarismar at adplabs dot com dot br 
Many thanks for the faster reply, I've tested with suggested snapshot
PHP Version: 5.2.2-dev
Build Date : Mar 12 2007 16:05:36

But the problem still occurs.

It seems that PDO get wrong data type for the CLOB column.
I can just insert up to 4000 bytes into the CLOB column, because the extension thinks its handling a LONG column.
 [2008-04-01 03:28 UTC] charles at crh-systems dot com 
I am having the same problem, 
OCIStmtExecute: ORA-01461: can bind a LONG value only for insert into a LONG column

I can only get 1333 chars to insert into a clob field no matter which connection charset I use.
4000 / 3 = 1333.333

...any fixes?
 [2009-01-09 16:41 UTC] inbox at trevorbramble dot com 
I am experiencing the same error as charles at crh-systems dot com, however I'm trying to enter a string into a varchar2(4000) column.

I've gone through a lot in the course of diagnosing this problem so I can't recall exactly what changed, but originally I was getting this error:

ORA-01460: unimplemented or unreasonable conversion requested

The resolution has been to define the length for that parameter:

$stmt->bindParam( ':notes', $this->notes, PDO::PARAM_STR, 4000 );

Why this fixes the problem is still a mystery.  None of the inserted data is lost, so there don't appear to have really been too many bytes in the string to insert.  I'm not experiencing the same problem with any of the smaller columns (a string of 4 or more characters will insert into a column defined varchar2(10), for example).
 [2009-04-25 14:44 UTC] jani@php.net 
Please try using this CVS snapshot:

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

  http://windows.php.net/snapshots/
 [2009-04-30 19:44 UTC] jarismar at gmail dot com 
Infortunately this error persists, it still occurs.

Tested with
  PHP Version : 5.2.10-dev
  Build Date : Apr 26 2009 23:39:22
 [2009-04-30 19:57 UTC] jarismar at adplabs dot com dot br 
Status changed to open again.
 [2009-11-02 20:06 UTC] markus at computino dot de 
Still a valid bug, cost me multiple hours trying to work it out before I found the bug report. Now using adodb/oci8.
 [2009-11-02 20:35 UTC] pajoye@php.net 
Assigned it to someone more active :)
 [2010-05-26 15:05 UTC] firegun at thehummels dot org 
I`m having the same problem described here, I´m using the Doctrine library, running on lastest php version (5.3+) and oracle XE, with the charset AL32UTF8. Any ideas on a working solution? Comeon guys this is a 2007 bug... ;/
 [2011-01-28 14:55 UTC] john dot doe at trash-mail dot com 
Is there any workaround or alternative for PHP 5.2.6 which is stable on Debian Lenny or is PDO not usable on this version? (I mean not usable because without fetching affected rows of INSERT, UPDATE, DELTE statements it's nearly impossible to write secure code).
 [2012-08-17 20:30 UTC] zulrang at gmail dot com 
What's the status on this bug?  I'm still experiencing the problem in 5.3.15!
 [2012-10-26 05:57 UTC] sixd@php.net
-Assigned To: sixd +Assigned To:
 [2014-01-01 12:53 UTC] felipe@php.net
-Package: PDO related +Package: PDO OCI
 [2021-07-09 16:30 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2021-07-09 16:30 UTC] cmb@php.net 
Is this still an issue with any of the actively supported PHP
versions[1]?

[1] <https://www.php.net/supported-versions.php>
 [2021-07-18 04:22 UTC] php-bugs at lists dot php dot net 
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun Dec 22 06:01:30 2024 UTC