MrVimes MrVimes - 2 months ago 6
PHP Question

"The active result contains no fields" using PDO with MS SQL

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! :)

Answer

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

$statement->nextRowset();

And then using $statement->fetch(); as normal