PDO和Microsoft SQL:必须声明表变量"@ P1" [英] PDO and Microsoft SQL: Must declare the table variable "@P1"

问题描述

我正在尝试在PDO中使用绑定从Microsoft SQL数据库中选择一些条目.我使用的代码看起来与文档中的代码相似.但是，当我运行它时，会收到以下警告:

I'm trying to use binding in PDO to select some entries from a Microsoft SQL database. The code I'm using looks like it's similar to what I've found in the documentation. However, when I run it, I get the warning below:

警告:PDOStatement :: execute()[pdostatement.execute]:SQLSTATE [42000]:语法错误或访问冲突:1087 [Microsoft] [SQL Native Client] [SQL Server]必须声明表变量"@ P1" . (第40行上的(长文件路径)中的(SQLExecute [1087]，位于ext \ pdo_odbc \ odbc_stmt.c:254处)

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1087 [Microsoft][SQL Native Client][SQL Server]Must declare the table variable "@P1". (SQLExecute[1087] at ext\pdo_odbc\odbc_stmt.c:254) in (long file path) on line 40

以下是相关代码:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM ? WHERE user='?'"; 
$sth = $db->prepare($sql);
$sth->bindValue(1,  $table, PDO::PARAM_STR);
$sth->bindValue(2, $user, PDO::PARAM_STR);
$sth->execute(); //                         <-- line 40
$data = $sth->fetch(PDO::FETCH_ASSOC);

这可能是相关的.当我尝试使用命名参数标记(:table，:user)而不是问号时，我得到了:

This may be related. When I try to use named parameter markers (:table, :user) instead of question marks, I get this:

警告:PDOStatement :: bindValue()[pdostatement.bindvalue]:SQLSTATE [HY093]:无效的参数编号:在第39行的(长文件路径)中未定义参数

Warning: PDOStatement::bindValue() [pdostatement.bindvalue]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in (long file path) on line 39

为什么不喜欢我准备好的陈述?

Why doesn't it like my prepared statement?

推荐答案

您无法在SQL中将参数绑定到表名.在任何语言，任何数据库中都是如此.

You can't bind parameters to table names in SQL. This is true in any language, any database.

您必须将表名插入到prepare()之前的查询字符串中.

You'll have to interpolate the table name into the query string before prepare().

即使您使用字符串或日期值，也不要将参数占位符放在引号中.引号内的参数占位符被解释为文字字符串.否则，您将如何输入文字问号?

Also you shouldn't put the parameter placeholder inside quotes, even if it's a string or date value. Parameter placeholders inside quotes are interpreted as literal strings. Otherwise how would you ever enter a literal question mark?

这是我的写法:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM $table WHERE user=?"; 
$sth = $db->prepare($sql);
$sth->execute(array($user));
$data = $sth->fetch(PDO::FETCH_ASSOC);

我不介意使用bindParam()或bindValue().通常，将数组参数中的参数值传递给execute()会更容易.

I don't bother with using bindParam() or bindValue(). It's usually easier to just pass the parameter values in an array argument to execute().

这篇关于PDO和Microsoft SQL:必须声明表变量"@ P1"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！