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

[jarismar at adplabs dot com dot br]

From: jarismar at adplabs dot com dot br
Operating system:
PHP version: 5.2.1
PHP Bug Type: PDO related
Bug description: Error trying to insert into a CLOB column when using multi-byte charset.

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 bug report at [url]http://bugs.php.net/?id=40787&edit=1[/url]
--
Try a CVS snapshot (PHP 4.4): [url]http://bugs.php.net/fix.php?id=40787&r=trysnapshot44[/url]
Try a CVS snapshot (PHP 5.2): [url]http://bugs.php.net/fix.php?id=40787&r=trysnapshot52[/url]
Try a CVS snapshot (PHP 6.0): [url]http://bugs.php.net/fix.php?id=40787&r=trysnapshot60[/url]
Fixed in CVS: [url]http://bugs.php.net/fix.php?id=40787&r=fixedcvs[/url]
Fixed in release: [url]http://bugs.php.net/fix.php?id=40787&r=alreadyfixed[/url]
Need backtrace: [url]http://bugs.php.net/fix.php?id=40787&r=needtrace[/url]
Need Reproduce Script: [url]http://bugs.php.net/fix.php?id=40787&r=needscript[/url]
Try newer version: [url]http://bugs.php.net/fix.php?id=40787&r=oldversion[/url]
Not developer issue: [url]http://bugs.php.net/fix.php?id=40787&r=support[/url]
Expected behavior: [url]http://bugs.php.net/fix.php?id=40787&r=notwrong[/url]
Not enough info: [url]http://bugs.php.net/fix.php?id=40787&r=notenoughinfo[/url]
Submitted twice: [url]http://bugs.php.net/fix.php?id=40787&r=submittedtwice[/url]
register_globals: [url]http://bugs.php.net/fix.php?id=40787&r=globals[/url]
PHP 3 support discontinued: [url]http://bugs.php.net/fix.php?id=40787&r=php3[/url]
Daylight Savings: [url]http://bugs.php.net/fix.php?id=40787&r=dst[/url]
IIS Stability: [url]http://bugs.php.net/fix.php?id=40787&r=isapi[/url]
Install GNU Sed: [url]http://bugs.php.net/fix.php?id=40787&r=gnused[/url]
Floating point limitations: [url]http://bugs.php.net/fix.php?id=40787&r=float[/url]
No Zend Extensions: [url]http://bugs.php.net/fix.php?id=40787&r=nozend[/url]
MySQL Configuration Error: [url]http://bugs.php.net/fix.php?id=40787&r=mysqlcfg[/url]

[tony2001@php.net]

ID: 40787
Updated by: [email]tony2001@php.net[/email]
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:

[url]http://snaps.php.net/php5.2-latest.tar.gz[/url]

For Windows:

[url]http://snaps.php.net/win32/php5.2-win32-latest.zip[/url]




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 [url]http://bugs.php.net/?id=40787&edit=1[/url]

[jarismar at adplabs dot com dot br]

ID: 40787
User updated by: jarismar at adplabs dot com dot br
Reported By: jarismar at adplabs dot com dot br
-Status: Feedback
+Status: Open
Bug Type: PDO related
PHP Version: 5.2.1
New Comment:

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.


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

[2007-03-12 18:03:41] [email]tony2001@php.net[/email]

Please try using this CVS snapshot:

[url]http://snaps.php.net/php5.2-latest.tar.gz[/url]

For Windows:

[url]http://snaps.php.net/win32/php5.2-win32-latest.zip[/url]



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

[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 [url]http://bugs.php.net/?id=40787&edit=1[/url]

[tony2001@php.net]

ID: 40787
Updated by: [email]tony2001@php.net[/email]
Reported By: jarismar at adplabs dot com dot br
-Status: Open
+Status: Assigned
Bug Type: PDO related
PHP Version: 5.2.1
-Assigned To:
+Assigned To: wez


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

[2007-03-12 18:27:14] 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.

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

[2007-03-12 18:03:41] [email]tony2001@php.net[/email]

Please try using this CVS snapshot:

[url]http://snaps.php.net/php5.2-latest.tar.gz[/url]

For Windows:

[url]http://snaps.php.net/win32/php5.2-win32-latest.zip[/url]



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

[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 [url]http://bugs.php.net/?id=40787&edit=1[/url]