带有消息“SQLSTATE[22001]"的“PDOException":字

问题描述

注意:我已经将这个问题缩小到专门的 PDO，因为我能够使用 odbc_* 函数.

为什么我不能将此参数绑定到 PDO 准备好的语句?

这有效:

$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 )

不起作用:

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

Web Server 在 Linux Ubuntu 14.04 上运行 PHP 5.5.9，使用 ODBC Driver 13 for SQL Server 并连接到 Windows Server 2012 上的 Microsoft SQL Server 2012

这是完整的错误:

致命错误:未捕获的异常PDOException"，消息为SQLSTATE[22001]":字符串数据，右截断:0[Microsoft][用于 SQL Server 的 ODBC 驱动程序 13]字符串数据，右截断(SQLExecute[0] at/build/buildd/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' 在/var/www/scratch.php:46堆栈跟踪:#0/var/www/scratch.php(46): PDOStatement->execute(Array)#1 {main} 在第 46 行的/var/www/scratch.php 中抛出

我也试过设置:

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

并使用命名参数:

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

即使有一个明确的冒号:

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

我也试过只使用 bindParam如这个答案所示:

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

以及命名参数:

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

如果我尝试明确设置长度:

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

我收到一个奖励错误:

致命错误:未捕获的异常 'PDOException' 带有消息'SQLSTATE[42000]:语法错误或访问冲突:102[Microsoft][用于 SQL Server 的 ODBC 驱动程序 13][SQL Server]'OUTPUT' 附近的语法不正确.

是的，所有这些都是一个没有表格的简单示例，因此您可以轻松重现它，但可以肯定的是，我实际上已经用真实的表格进行了尝试.

CREATE TABLE myTable (id INT 身份主键，val NVARCHAR(255));INSERT INTO myTable (val) VALUES ('hello world');

作品:

$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 )

不起作用:

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

所有路径都导致相同的错误:

字符串数据，右截断

很遗憾，

这是一个 PDO_ODBC 64 位不兼容问题(#61777、#64824) 并且毫无疑问你是 64-bit build 不允许你绑定参数.

幸运的是，

它有一个补丁首次包含在 5.6 版本中:

这个bug也参考了#61777 并且仍然存在在 5.5 分支的最新稳定版本中.我看到两张票这个问题已经存在，我只是提交这些更改通过 github 作为提醒，这对任何人来说都是一个严重的问题在 x64 版本上使用 PDO_ODBC.

您的 PHP 附带的 PDO_ODBC 有什么问题?

通过查看推荐的补丁之一:

diff --git a/ext/pdo_odbc/odbc_stmt.c b/ext/pdo_odbc/odbc_stmt.c索引 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;剑柱;- SDWORD colsize;+ SQLULEN colsize;SQLLEN 显示大小；

我们看到唯一改变的是 SDWORD(16 位有符号整数)，它被替换为新的 ODBC 类型 SQLULEN，即 64 位 ODBC 应用程序中的 64 位和 32 位 ODBC 应用程序中的 32 位.

我相信提交者不知道 colsize 数据类型只是因为在下一行 SQLLEN 被正确定义.

我现在该怎么办?

1. 升级到 PHP 版本 >= 5.6
2. 坚持使用 odbc_* 函数作为可行的解决方案.
3. 使用提供的补丁编译 PHP v5.5.9.
4. 按照@GordonM 的建议构建您自己的 PDO 包装器

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

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

这篇关于带有消息“SQLSTATE[22001]"的“PDOException":字符串数据，右截断:0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！