'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0

Question

NOTE: I have narrowed this problem down to specifically PDO because I am able to successfully prepare and execute statements using the odbc_* functions.

Why can't I bind this parameter to the PDO prepared statement?

This works:

$mssqldriver = 'ODBC Driver 13 for SQL Server'; $pdoDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password); $pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  $sql = "SELECT 'value' AS col where 'this' = 'this'"; $stmt = $pdoDB->prepare($sql); $params = []; $stmt->execute($params); print_r($stmt->fetch()); 

Array ( [col] => value [0] => value ) 

Does not work:

$sql = "SELECT 'value' AS col where 'this' = ?"; $stmt = $pdoDB->prepare($sql); $params = ['this']; $stmt->execute($params); print_r($stmt->fetch()); 

Web Server is running PHP 5.5.9 on Linux Ubuntu 14.04 with ODBC Driver 13 for SQL Server and connecting to Microsoft SQL Server 2012 on Windows Server 2012

Here's the full error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0 [Microsoft][ODBC Driver 13 for SQL Server] String data, right truncation (SQLExecute[0] at /build/buildd/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' in /var/www/scratch.php:46 Stack trace: #0 /var/www/scratch.php(46): PDOStatement->execute(Array) #1 {main} thrown in /var/www/scratch.php on line 46 

I have also tried setting:

$pdoDB->setAttribute( PDO::ATTR_EMULATE_PREPARES, true ); 

And using named parameters:

$sql = "SELECT 'value' AS col where 'this' = :myVal"; $stmt = $pdoDB->prepare($sql); $params = ['myVal' => 'this']; $stmt->execute($params); print_r($stmt->fetch()); 

Even with an explicit colon:

$params = [':myVal' => 'this']; 

I also tried just using bindParam as demonstrated in this answer:

$sql = "SELECT 'value' AS col where 'this' = ?"; $stmt = $pdoDB->prepare($sql); $param = 'this'; $stmt->bindParam(1, $param); $stmt->execute(); print_r($stmt->fetch()); 

As well as with named parameters:

$sql = "SELECT 'value' AS col where 'this' = :myVal"; $stmt = $pdoDB->prepare($sql); $param = 'this'; $stmt->bindParam(':myVal', $param, PDO::PARAM_STR); $stmt->execute(); print_r($stmt->fetch()); 

If I try to explicitly set the length:

$stmt->bindParam(':myVal', $param, PDO::PARAM_STR, 4); 

I get a bonus error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Incorrect syntax near 'OUTPUT'. 

And yes, all this is a trivialized example without tables so that you can easily reproduce it, but just to be sure, I have actually tried this with a real table.

CREATE TABLE myTable (     id INT IDENTITY PRIMARY KEY,     val NVARCHAR(255) ); INSERT INTO myTable (val) VALUES ('hello world'); 

Works:

$sql = "SELECT * FROM myTable WHERE val = 'hello world'"; $stmt = $pdoDB->prepare($sql); $params = []; $stmt->execute($params); print_r($stmt->fetch()); 

Array ( [id] => 1 [0] => 1 [val] => hello world [1] => hello world ) 

Does not work:

$sql = "SELECT * FROM myTable WHERE val = ?"; $stmt = $pdoDB->prepare($sql); $params = ['hello world']; $stmt->execute($params); print_r($stmt->fetch()); 

All paths lead to the same error:

String data, right truncated

Answer 1

Unfortunately,

It's a PDO_ODBC 64-bit incompatibility problem (#61777, #64824) and without any doubts you are on a 64-bit build which doesn't allow you to bind parameters.

Fortunately,

It has a patch that was first included in the 5.6 release:

This bug is also referenced in #61777 and is still present in the latest stable release of the 5.5 branch. I see two tickets exist for this problem already, and I'm just submitting these changes via github as a reminder that this is a serious problem for anyone using PDO_ODBC on the x64 builds.

What is wrong with your PHP's shipped PDO_ODBC?

By looking at one of those recommended patches:

diff --git a/ext/pdo_odbc/odbc_stmt.c b/ext/pdo_odbc/odbc_stmt.c index 8b0ccf3..1d275cd 100644 --- a/ext/pdo_odbc/odbc_stmt.c +++ b/ext/pdo_odbc/odbc_stmt.c @@ -551,7 +551,7 @@ static int odbc_stmt_describe(pdo_stmt_t *stmt, int colno TSRMLS_DC)     struct pdo_column_data *col = &stmt->columns[colno];     RETCODE rc;     SWORD   colnamelen; -   SDWORD  colsize; +   SQLULEN colsize;     SQLLEN displaysize; 

We see the only thing that's changed is SDWORD (16-bit signed integer) which is substituted with new ODBC type SQLULEN that is 64 bits in a 64-bit ODBC application and 32 bits in a 32-bit ODBC application.

I believe committer wasn't aware of colsize data type only since in the very next line SQLLEN is defined properly.

What should I do now?

1. Upgrade to PHP version >= 5.6
2. Stick with odbc_* functions as a working solution.
3. Compile a PHP v5.5.9 with provided patches.
4. Build your own PDO wrapper as recommended by @GordonM