CODEWITHSUNDEEP
phpsql-serverstored-procedurespdo
DJB
DJB

Reputation: 267

php pdo fetchAll returns SQL error 'no active fields' when executing stored procedure

My stored procedure executes 100% fine from the Management Studio, but when running through PDO with a try catch block I get the following exception message: SQLSTATE[IMSSP]: The active result for the query contains no fields.

I have tried the classic SET NOCOUNT ON (this caught me out previously) to stop it returning row counts, and I've done various tests by removing sections of the SP until I have found which section the error lies in. I've also tried the PHP PDO nextRowset() with no luck.

The Stored Procedure: I declare a cursor (shock, horror I know!) and iterate over some results, which itself caused no issues - but in reality this cursor must run various stored procedures itself for each fetch in the cursor, and when I introduce these stored procedures that is when the issues appear.

I have gone through the SPs inside the cursor and SET NOCOUNT ON on them in case that might be the issue, but no luck. One or two of these SPs have OUTPUTS but these are captured in variables accordingly.

Does anyone have any ideas? I don't wish to post any code of the project but some scenarios of commands I perform in the cursor block:

SELECT @varName = columnName FROM dbo.tableName

SET @varName = (SELECT columnName FROM dbo.tableName)

EXEC dbo.storedProcedure @outputVar OUTPUT

My best guess is the top example is the problem, but I am not knowledgeable to know. I would like to locate the error without removing these one by one as the actions performed by the procedure as a whole are difficult to roll-back on my test database and each line is important to getting correct output.

Thanks in advance for any help provided!

Upvotes: 0

Views: 1231

Answers (1)

DJB
DJB

Reputation: 267

I have now managed to solve this! Hopefully the solution will help others who run into this error or similar.

The issue was that nested cursors (cursor inside cursor) were in use for the SQL Server stored procedure. My main procedure (called from PHP by PDO) opened a cursor and then ran other stored procedures inside that cursor that opened a cursor of their own.

This method works fine when running the query in SQL Server Management Studio, but calling from PHP via PDO fails.

While I know that using cursors is considered bad practice by most SQL buffs, unfortunately I inherited these stored procedures so I'm removing all blame from myself!

The actual solution was to replace the cursor in the originating stored procedure (the one called by PHP which in turn calls the other SPs) with a while loop using code like this:

DECLARE @loopTable table (id int IDENTITY(1,1), dataColumn)
DECLARE @id int
DECLARE @rows int
DECLARE @data int -- var to hold targeted data in the loop

INSERT INTO @loopTable (dataColumn)
SELECT dataColumn FROM dataTable

SELECT @rows = COUNT(1) FROM @loopTable
WHILE (@rows > 0)
BEGIN
    SELECT TOP 1 @data = dataColumn, @id = id FROM @loopTable

    // Do stuff with @data variable here

    DELETE FROM @loopTable where id = @id
    SELECT @rows = COUNT(1) from @loopTable
END

Problem solved, nightmare to find!

Upvotes: 1

Related Questions