php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #58870 bindParam() -> Exception: SQLSTATE[22001]: String data, right truncated on CLI
Submitted: 2009-10-01 06:03 UTC Modified: 2009-10-01 06:10 UTC
Votes:3
Avg. Score:5.0 ± 0.0
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:1 (33.3%)
From: matthias dot djihangiroff at persona dot de Assigned:
Status: Open Package: PDO_INFORMIX (PECL)
PHP Version: 5.2.9 OS: opensuse 10.3
Private report: No CVE-ID:
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-10-01 06:03 UTC] matthias dot djihangiroff at persona dot de
Description:
------------
The bindParam() throws an SQLSTATE[22001]: String data, right truncated exception at CLI only.

In an Apache context, all went fine.

Reproduce code:
---------------
$INFORMIXDIR = getenv("INFORMIXDIR");

$connectionString2 = "informix:host=ux10001; service=1528; database=db10001; server=tcp10001; protocol=onsoctcp; EnableScrollableCursors=1;TRANSLATIONDLL=$INFORMIXDIR/lib/esql/igo4a304.so";
$pdo2 = new PDO($connectionString2, "", "");
$pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$suchstring = "Matthias";

$stmt2 = $pdo2->prepare("SELECT nbwvorname from snbewer where nbwvorname like :name");
$stmt2->bindParam(':name', $suchstring, PDO::PARAM_STR);
$stmt2->execute();
print_r($stmt2->fetchAll());

Expected result:
----------------
Array
(
    [0] => Array
        (
            [NBWVORNAME] => Matthias                      
            [0] => Matthias                      
        )
}

Actual result:
--------------
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: -11023 [Informix][Informix ODBC Driver]String data right truncation. (SQLExecute[-11023] at /tmp/pear/download/PDO_INFORMIX-1.2.6/informix_statement.c:755)' in /srv/www/htdocs/django/test27.php:21
Stack trace:
#0 /srv/www/htdocs/django/test27.php(21): PDOStatement->execute()
#1 {main}
  thrown in /srv/www/htdocs/django/test27.php on line 21

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-10-01 06:10 UTC] matthias dot djihangiroff at persona dot de
Forgot to mention:

CSDK:
│2.90.FC4R1

IDS: 
Version 11.50.FC3 and Version 9.21.UC2
 [2009-10-02 02:14 UTC] matthias dot blaesing at persona dot de
Some additional information:

Assuming we created a table X with a column Y defined as char(5):

$stmt = $db->prepare("SELECT * FROM X WHERE Y like ?");
$stmt->bindParam(1, $Z);
$stmt->execute();

This works, as long, as len($Z) <= 5 - if its longer, than it breaks.

Another testcase:

$stmt = $db->prepare("SELECT * FROM X WHERE Y || Y like ?");
$stmt->bindParam(1, $Z);
$stmt->execute();

This breaks the moment len($Z) > 0 (any usable case).

So basicly I would have to fall back to:

$stmt->query(
     sprintf("SELECT * FROM X WHERE Y || Y like %s",
             $db->quote($Z)
);

This could be also either done in the driver or corretly solved (prefered).
 [2009-10-05 05:06 UTC] matthias dot blaesing at persona dot de
A possible work around (if it is caused by the SDK) could be to implement PDO::ATTR_EMULATE_PREPARES. This could work, as I currently work around the problem by replacing the Parameters by their quoted value, prior to sending them into the Informix Bindings. This would hurt performance, but in the current state performance is one of my lesser concerns.
 [2009-10-14 20:06 UTC] matthias dot blaesing at persona dot de
Ok - now I reproduced the problem with the Informix Demo-Appliance - I changed the most basic example to use the feature(?) bindValue: 

 ids1150srvr:/opt/IBM # diff -u ./informix/FirstSteps/03_PHP/HelloWorld.php ./OpenAdmin/Apache_2.2.4/htdocs/HelloWorld.php 
--- ./informix/FirstSteps/03_PHP/HelloWorld.php	2009-04-29 21:26:02.000000000 -0400
+++ ./OpenAdmin/Apache_2.2.4/htdocs/HelloWorld.php	2009-10-14 19:59:57.000000000 -0400
@@ -4,10 +4,13 @@
 
 try {
 $db = new PDO("informix:host=ids1150srvr; service=9088; database=stores; server=demo_on; protocol=onsoctcp; EnableScrollableCursors=1;", "informix", "informix");
+$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 print "Hello World!</br></br>";
 print "Connection Established!</br></br>";
 
-$stmt = $db->query("select * from customer");
+$stmt = $db->prepare("select * from customer WHERE fname || fname like ?");
+$stmt->bindValue(1, '%Carole%');
+$stmt->execute();
 $res = $stmt->fetch( PDO::FETCH_BOTH );
 $rows = $res[0];
 echo "Table contents: $rows.</br>";
ids1150srvr:/opt/IBM # 

This produces an exception. The same happens then fname colum concatenation is not done, but a string with a length 16 or greater is used.in the bind.
 [2010-02-19 08:26 UTC] ihabunek at gmail dot com
Description:
------------
I have a very similar problem when calling stored procedures. 

Server is IDS 11.50uc4 running on Debian. The problem is reproducable on both Windows and Linux clients running PHP 5.2.x (both CLI and Apache), PDO_INFORMIX 1.2.6 and Informix Client SDK 3.50.

The problem is when calling the following stored procedure:

CREATE PROCEDURE test_proc(
	param varchar(10)
)
END PROCEDURE;

Reproduce code:
---------------
<?php
$db_url = "informix:host=tstdb; service=1526; database=buba; server=tstdb; CLIENT_LOCALE=hr_hr.1250; DB_LOCALE=hr_hr.1250";
$db = new PDO($db_url, '<user>', '<pass>');

$query = "EXECUTE PROCEDURE test_proc(:param)";
$args = array('param' => 'abc');

$db = new PDO($db_url, $username, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare($query);
$stmt->execute($args);
?>

Expected result:
----------------
Statement is executed.

Actual result:
--------------
PDO Exception thrown with the following message:
SQLSTATE[22001]: String data, right truncated: -11023 [Informix][Informix ODBC Driver]String data right truncation. (SQLExecute[-11023] at /root/PDO_INFORMIX-1.2.6/informix_statement.c:757)

Notes
-----
* The same happend when using bindValue or bindParam methods to pass the value of ':param'. 
* The same happend when using unnamed parameters ('?' instead of ':param'). 
* The exception is always thrown from execute(), not bindValue or bindParam as described by matthias.
* The error does NOT appear if the ':param' argument value is an empty string. All other values trigger the above error.
 [2010-02-19 11:14 UTC] ihabunek at gmail dot com
We have tracked the problem down to the stmt_bind_parameter() function in informix_statement.c (rev 268707). When processing a string, the value param_res->param_size is passed to SQLBindParameter function on line 422. In some cases, this value is set to 0 regardless of the length of the string.

param_size is initialized by SQLDescribeParam function called on line 203. In our case (described in the above comment) this function sets param_size to 0. So, when SQLBindParameter is called in line 422, length of the string does not correspond to param_size. This produces the "String data right truncation" error.

We are not sure if this is a bug in SQLDescribeParam (from the SDK), but a quick fix would be to initialize this value to the length of input parameter. This can be done by inserting the following line after line 405 (param_res->transfer_length = 0;):

param_res->param_size = Z_STRLEN_P(curr->parameter);

Hope this helps.
 [2011-07-07 21:32 UTC] support at globalheritagefund dot org
Great atircle, thank you again for writing.
 [2011-12-07 21:00 UTC] bavo at janss dot nl
I was facing the same problems and I thought I found the solution here.
But even with the fix, no data gets to the database using bindValue.
I finally discovered PDO_Informix only works with informix 10 not with 9 and not 
with 11. For 11 you should use PDO_Ibm.

regards

Bavo
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Sat Apr 19 14:01:50 2014 UTC