问题描述
注意::由于我能够使用 odbc_ * 函数.
NOTE: I have narrowed this problem down to specifically PDO because I am able to successfully prepare and execute statements using the odbc_* functions.
为什么不能将此参数绑定到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服务器在 Linux Ubuntu 14.04 上具有用于SQL Server的ODBC驱动程序13上运行PHP 5.5.9，并在Windows Server 2012上连接到 Microsoft SQL Server 2012
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
这是完整的错误:
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
我也尝试过设置:
$pdoDB->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
并使用命名参数:
$sql = "SELECT 'value' AS col where 'this' = :myVal";
$stmt = $pdoDB->prepare($sql);
$params = ['myVal' => 'this'];
$stmt->execute($params);
print_r($stmt->fetch());
即使有明确的冒号:
$params = [':myVal' => 'this'];
$sql = "SELECT 'value' AS col where 'this' = ?";
$stmt = $pdoDB->prepare($sql);
$param = 'this';
$stmt->bindParam(1, $param);
$stmt->execute();
print_r($stmt->fetch());
以及命名参数:
$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);
我收到奖金错误:
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');
作品:
$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 = ['hello world'];
$stmt->execute($params);
print_r($stmt->fetch());
所有路径均导致相同的错误:
All paths lead to the same error:
推荐答案
不幸的是，
这是一个PDO_ODBC 64位不兼容问题(#61777 ， #64824 )，毫无疑问，您使用的是64位版本不允许您绑定参数.
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.
它有一个补丁最初包含在5.6版本中:
It has a patch that was first included in the 5.6 release:
PHP随附的PDO_ODBC有什么问题?
通过查看推荐的补丁之一:
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;
我们看到唯一更改的是SDWORD(16位带符号整数)，它被新的ODBC类型SQLULEN替换为在64位ODBC应用程序中为64位，在32位ODBC应用程序中为32位.
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.
我相信提交者并不知道colsize数据类型，因为在下一行中SQLLEN的定义正确.
I believe committer wasn't aware of colsize data type only since in the very next line SQLLEN is defined properly.
- 升级到PHP版本> = 5.6
- 带有
odbc_*的棍子可作为有效的解决方案. - 使用提供的补丁程序编译PHP v5.5.9.
- 按照@GordonM的建议构建自己的PDO包装器
- Upgrade to PHP version >= 5.6
- Stick with
odbc_*functions as a working solution. - Compile a PHP v5.5.9 with provided patches.
- Build your own PDO wrapper as recommended by @GordonM
这篇关于消息为"SQLSTATE [22001]"的"PDOException":字符串数据，右截断:0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！