带整数值的多次准备语句不能执行 [英] Prepared statement cannot be executed multiple times with integer values
问题描述
如何使用不同的整数值正确地重新执行已准备好的语句?
How do I properly re-execute a prepared statement using different integer values?
在重用ODBC准备语句时,显式和隐式绑定PDO::PARAM_INT
出现致命错误.
There's something deathly wrong with explicit and implicit binding PDO::PARAM_INT
when reusing an ODBC prepared statement.
CREATE TABLE mytab (
col INT,
something VARCHAR(20)
);
Works:多个字符串
$pdoDB = new PDO('odbc:Driver=ODBC Driver 13 for SQL Server;
Server='.DATABASE_SERVER.';
Database='.DATABASE_NAME,
DATABASE_USERNAME,
DATABASE_PASSWORD
);
$pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$values = ['here','are','some','values'];
$sql = "INSERT INTO mytab (something) VALUES (:something)";
$stmt = $pdoDB->prepare($sql);
foreach ($values as $value)
$stmt->execute(['something'=>$value]);
Works:单个整数
$values = [42];
$sql = "INSERT INTO mytab (col) VALUES (:col)";
$stmt = $pdoDB->prepare($sql);
foreach ($values as $value)
$stmt->execute(['col'=>$value]);
不起作用:多个整数
$values = [1,3,5,7,11];
$sql = "INSERT INTO mytab (col) VALUES (:col)";
$stmt = $pdoDB->prepare($sql);
foreach ($values as $value)
$stmt->execute(['col'=>$value]);
它实际上成功地插入了第一条记录1
,但是当它尝试在下一次执行时重用该语句时失败了.
It actually successfully inserts the first record 1
but fails when it tries to reuse the statement on the next execute.
PHP致命错误:未捕获的PDOException:SQLSTATE [22018]:转换规范的字符值无效:206 [Microsoft] [用于SQL Server的ODBC驱动程序13] [SQL Server]操作冲突:文本与int不兼容(SQLExecute [ 206],位于/build/php7.0-lPMnpS/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)
PHP Fatal error: Uncaught PDOException: SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at /build/php7.0-lPMnpS/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)
我正在使用用于SQLServer®的Microsoft®ODBC驱动程序13(预览)
我尝试将整个内容包装在 PDO::beginTransaction
和 PDO::commit
I have tried wrapping the whole thing in PDO::beginTransaction
and PDO::commit
我也尝试使用 PDOStatement::bindParam
,但是它抛出了完全一样的错误.
I've also tried using PDOStatement::bindParam
but it throws the exact same error.
$values = [1];
$sql = "INSERT INTO mytab (col) VALUES (:col)";
$stmt = $pdoDB->prepare($sql);
foreach ($values as $value){
$stmt->bindParam('col', $value, PDO::PARAM_INT);
$stmt->execute();
}
不起作用
$values = [1,2];
$sql = "INSERT INTO mytab (col) VALUES (:col)";
$stmt = $pdoDB->prepare($sql);
foreach ($values as $value){
$stmt->bindParam('col', $value, PDO::PARAM_INT);
$stmt->execute();
}
我认为我注意到与使用PHP 5.6.9的此未回答的问题完全相同的错误非常有趣. .但是,他们甚至不能执行一条语句,所以我想知道是否存在部分补丁,因为抛出错误的确切行已经从 odbc_stmt.c:260
I think it's interesting to note that I am getting the exact same error as this unanswered question using PHP 5.6.9. However, they are not able to execute even one statement, so I'm wondering if there's been a partial patch considering the exact line throwing the error has moved from odbc_stmt.c:254
to odbc_stmt.c:260
如果我在循环内准备 语句,那么就可以了.但是我读到这效率很低,我应该可以重用陈述.我特别担心将其与海量数据集一起使用.这个可以吗?我能做些更好的事情吗?
If I prepare the statement inside the loop, then it works just fine. But I've read that this is very inefficient and I should be able to reuse the statement. I'm particularly worried about using this with massive datasets. Is this OK? Is there something better that I can do?
$values = [1,3,5,7,9,11];
$sql = "INSERT INTO mytab (col) VALUES (:col)";
foreach ($values as $value){
$stmt = $pdoDB->prepare($sql);
$stmt->execute(['col'=>$value]);
}
推荐答案
对于准备好的语句,通常必须在循环外使用bindParam
.
In case of prepared statements you have to use bindParam
outside of loop, usually.
-
bindParam
是一个步骤 - 设置绑定变量是可重复的步骤(循环)
- 每次重复都必须运行
execute
bindParam
is a single step- setting bound variables is a repeatable step (loop)
- you have to run
execute
for each repetition
我想,类似的方法会起作用:
I guess, something like that would work:
$stmt = $pdoDB->prepare("INSERT INTO mytab (col, key) VALUES (:col, :key)");
// bind params (by reference)
$stmt->bindParams(":col", $col, PDO::PARAM_STR); //bind variable $col
$stmt->bindParams(":key", $key, PDO::PARAM_INT); //bind variable $key
$values = ['here','are','some','values'];
foreach ($values as $i => $value) {
$col = $value; //set col
$key = $i; //set key
$stmt->execute();
}
这篇关于带整数值的多次准备语句不能执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!