“活动结果不包含任何字段"在MS SQL中使用PDO [英] "The active result contains no fields" using PDO with MS SQL

问题描述

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

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

SELECT afield INTO #temptable FROM atable WHERE anotherfield = 'somevalue';

SELECT afield,anotherfield,onemorefield FROM atable 
WHERE afield NOT IN (SELECT * FROM #temptable);

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

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

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

这似乎可以解决该错误，但是此版本的查询效率极低，因为它似乎为另一个查询中的每个字段比较都运行了临时表查询.

有没有一种方法可以使第一个表单(一次创建一个临时表)与PDO一起使用?

在使用mssql的旧页面上运行正常.

我知道我可以通过创建真实表，在php中运行，然后运行第二个查询(在单独的php调用中)，以混乱"的方式进行操作，然后运行第三个查询以删除第一个表.但是我宁愿不必求助于此！ :)

解决方案

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

SET NOCOUNT ON 

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

Microsoft文档

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

这篇关于“活动结果不包含任何字段"在MS SQL中使用PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！