“活动结果不包含任何字段"在 MS SQL 中使用 PDO

我正在将一些旧的 PHP 页面转换为使用 PDO.

下面是两个简化的查询(不是我的实际查询)，以帮助理解我遇到的问题...

SELECT afield INTO #temptable FROM atable WHERE anotherfield = 'somevalue';SELECT afield,anotherfield,onemorefield FROM atableWHERE afield NOT IN (SELECT * FROM #temptable);

上面的查询抛出标题中描述的错误(更完整的是它抛出致命错误:未捕获异常'PDOException'，消息为'SQLSTATE[IMSSP]:查询的活动结果不包含字段.'")

如果我像这样更改查询...

with (SELECT afield INTO #temptable FROM atableWHERE anotherfield = 'somevalue') AS temptable;SELECT afield,anotherfield,onemorefield FROM atablewhere afield NOT IN (SELECT * FROM temptable);

这似乎解决了错误，但这个版本的查询效率低得可怕，因为它似乎对另一个查询中的每个字段比较运行临时查询.

有没有办法让第一个表单(创建一次临时表)与 PDO 一起工作?

它在使用 mssql 的旧页面上运行良好.

我知道我可以通过创建一个真实的表以凌乱"的方式做到这一点，在 php 中运行它，然后运行第二个查询(在单独的 php 调用中)，然后运行第三个查询以删除第一个表.但我宁愿不必诉诸那个！:)

解决方案

如果您使用的是存储过程，请使用

SET NOCOUNT ON

问题是存储过程返回的结果包含受影响的行数作为第一个结果.

微软文档

I am in the process of converting some old PHP pages to use 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.'")

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?

It worked fine on the old page which used 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! :)

解决方案

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.

Microsoft Documentation