不能使用整数值多次执行准备好的语句 [英] 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)
);

作品:多个字符串

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

作品:单个整数

$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][ODBC Driver 13 for SQL Server][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)

我使用 适用于 SQL Server® 的 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:254 到 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.

1. bindParam 是单步
2. 设置绑定变量是一个可重复的步骤(循环)
3. 你必须为每次重复运行execute

我想，这样的事情会奏效:

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屋！