問題描述
注意:我已經將這個問題縮小到專門的 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 被正確定義.
我現在該怎么辦?
- 升級到 PHP 版本 >= 5.6
- 堅持使用
odbc_*函數作為可行的解決方案. - 使用提供的補丁編譯 PHP v5.5.9.
- 按照@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_ODBCon 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?
- 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！