CODEWITHSUNDEEP
phpsql-serverpdo
MrVimes
MrVimes

Reputation: 3312

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

Upvotes: 28

Views: 51713

Answers (6)

Pedro Sanchez
Pedro Sanchez

Reputation: 59

I am using Laravel5 in this version you should execute 

DB::select('SET NOCOUNT ON; EXEC stored_procedure'. $param1.','...$paramN);

It worked perfect.

Upvotes: 5

Bryan
Bryan

Reputation: 3494

I ran into this issue and the above answers helped, but the answers here assume things:

  1. You have access to modfy the sproc with SET NOCOUNT ON and you want to do this
  2. You're sure you need to move to the next rowset $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.

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.

Upvotes: 9

Rahul Gupta
Rahul Gupta

Reputation: 10141

If you are using a StoredProcedure and doing something as:

DB::select("EXEC [storedprocedure] $param1,$param2;");

As per above, the PDO expects the DB::select statement to return some data. But as your StoredProcedure does not return any data, you can change DB::select TO DB::update as belows:

DB::update("EXEC [storedprocedure] $param1,$param2;");

After this the error should no longer appear.

Upvotes: 11

Kamaro
Kamaro

Reputation: 1015

I am using laravel5 and this is how I fixed the issue;

DB::select("SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;EXEC [storedprocedure] $param1,$param2;");

Upvotes: 1

user3228005
user3228005

Reputation: 816

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

Upvotes: 80

MrVimes
MrVimes

Reputation: 3312

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

Upvotes: 17

Related Questions