php “活动结果不包含任何字段”使用 PDO 和 MS SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16735898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"The active result contains no fields" using PDO with MS SQL
提问by MrVimes
I am in the process of converting some old PHP pages to use PDO.
我正在将一些旧的 PHP 页面转换为使用 PDO。
Below are two simplified queries (not my actual queries) to aid understanding of the problem I'm having...
下面是两个简化的查询（不是我的实际查询），以帮助理解我遇到的问题......
SELECT afield INTO #temptable FROM atable WHERE anotherfield = 'somevalue';
SELECT afield,anotherfield,onemorefield FROM atable
WHERE afield NOT IN (SELECT * FROM #temptable);
The above query throws the error described in the title (more completely it throws "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: The active result for the query contains no fields.'")
上面的查询抛出了标题中描述的错误（更完整的是它抛出“致命错误：未捕获的异常 'PDOException'，消息为 'SQLSTATE[IMSSP]：查询的活动结果不包含字段。'”）
If I alter the query like this...
如果我像这样更改查询...
with (SELECT afield INTO #temptable FROM atable
WHERE anotherfield = 'somevalue') AS temptable;
SELECT afield,anotherfield,onemorefield FROM atable
where afield NOT IN (SELECT * FROM temptable);
This seems to get around the error, but this version of the query is horribly horribly inefficient because it appears to run the temptable query for every single field comparison in the other query.
这似乎解决了这个错误，但是这个版本的查询效率非常低，因为它似乎为另一个查询中的每个字段比较运行临时查询。
Is there a way to make the first form (which creates a temporary table once) work with PDO?
有没有办法使第一个表单（创建一次临时表）与 PDO 一起使用？
It worked fine on the old page which used mssql.
它在使用 mssql 的旧页面上运行良好。
EDIT:I know I can probably do this in a 'messy' way by creating a real table, run it in php, then run the second query (in a separate php call) , then run a third query to drop the first table. But I'd rather not have to resort to that! :)
编辑：我知道我可以通过创建一个真实的表以“凌乱”的方式做到这一点，在 php 中运行它，然后运行第二个查询（在单独的 php 调用中），然后运行第三个查询以删除第一个表。但我宁愿不必诉诸那个！:)
回答by user3228005
If you are using a stored procedure then use
如果您使用的是存储过程，则使用
SET NOCOUNT ON
The problem is that the stored procedure returns a result containing the number of rows affected as the first result.
问题是存储过程返回的结果包含受影响的行数作为第一个结果。
回答by MrVimes
The PDO engine sees this query as returning two result sets (the older mssql engine probably just ignored all but the last query in an overall query string). I have managed to make it work by skipping over the first result set (the temporary table) using the following command
PDO 引擎将此查询视为返回两个结果集（较旧的 mssql 引擎可能只是忽略了整个查询字符串中除最后一个查询之外的所有查询）。我设法通过使用以下命令跳过第一个结果集（临时表）使其工作
$statement->nextRowset();
And then using $statement->fetch();as normal
然后$statement->fetch();正常使用
回答by Rahul Gupta
If you are using a StoredProcedureand doing something as:
如果您正在使用 aStoredProcedure并执行以下操作：
DB::select("EXEC [storedprocedure] $param1,$param2;");
As per above, the PDOexpects the DB::selectstatement to return some data. But as your StoredProceduredoes not return any data, you can change DB::selectTO DB::updateas belows:
如上所述，PDO期望DB::select语句返回一些数据。但是由于您StoredProcedure没有返回任何数据，您可以将DB::selectTODB::update更改为如下：
DB::update("EXEC [storedprocedure] $param1,$param2;");
After this the error should no longer appear.
在此之后，错误不应再出现。
回答by Bryan
I ran into this issue and the above answers helped, but the answers here assume things:
我遇到了这个问题，上面的答案有所帮助，但这里的答案假设：
- You have access to modfy the sproc with
SET NOCOUNT ONand you want to do this - You're sure you need to move to the next rowset
$statement->nextRowset();
- 您可以使用修改 sproc
SET NOCOUNT ON并且想要执行此操作 - 您确定需要移动到下一个行集
$statement->nextRowset();
In my case I am using a generic method to pull back data from multiple sprocs where I may or may not have this issue and needed to check before incrementing the rowset.
在我的情况下，我使用通用方法从多个 sproc 中拉回数据，其中我可能有也可能没有这个问题，并且需要在增加行集之前进行检查。
I used the following:
我使用了以下内容：
while($stmt->columnCount() === 0 && $stmt->nextRowset()) {
// Advance rowset until we get to a rowset with data
}
if($stmt->columnCount() > 0) {
// We found something with data, do stuff.
// Code here
}
Hopefully this helps someone else running into a similar issue.
希望这可以帮助其他人遇到类似的问题。
回答by Kamaro Lambert
I am using laravel5 and this is how I fixed the issue;
我正在使用 laravel5，这就是我解决问题的方法；
DB::select("SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;EXEC [storedprocedure] $param1,$param2;");
回答by Pedro Sanchez
I am using Laravel5 in this version you should execute
我在这个版本中使用 Laravel5 你应该执行
DB::select('SET NOCOUNT ON; EXEC stored_procedure'. $param1.','...$paramN);
It worked perfect.
它工作完美。
