1

I try to call 3 SQL Server 2000 Stored Procedures one after the other using PDO DBLIB in PHP 5.4.4 (Linux) and I get an error at the second query : Fatal error: Call to a member function fetchAll() on a non-object

The first query works perfectly, returning results as expected. If I move query order, every time the first query succeeds and the others fail.

Also, when the exact same code is run on a PHP 5.3.14 server, everything works great.

Example code:

$dbh = new PDO ("dblib:host=myhost;dbname=mydb","user","pass");

$query = $dbh->query("EXEC dbo.storedProc1 'param1'");
$result = $query->fetchAll();
var_dump($result);

$query = $dbh->query("EXEC dbo.storedProc2 'param1'");
$result = $query->fetchAll(); // <-- Fails here
var_dump($result);

$query = $dbh->query("EXEC dbo.storedProc3 'param1'");
$result = $query->fetchAll();
var_dump($result);

Any clue to make this code run on PHP 5.4 ?

EDIT : PDO::errorInfo gives me that error : Attempt to initiate a new Adaptive Server operation with results pending [20019] (severity 7) [EXEC dbo.storedProc2 'param1']

Also, calling query with a SELECT (SELECT 1, SELECT 3 and SELECT 3 for example) gives the same result (first result is given, following are empty)

EDIT 2 : Looks like it's related to a PHP bug, as noticed by Capilé in the comments

MaxiWheat
  • 6,133
  • 6
  • 47
  • 76
  • By the way, I know that `$dbh->query` can return `false` if the query fails, and it does, but it should not as it worked fine in php 5.3.14 – MaxiWheat Jul 11 '12 at 20:11
  • You say it does return false, so why aren't you checking PDO::errorCode/PDO::errorInfo? PDO will tell you what's wrong, but only if you ask it to. – Marc B Jul 11 '12 at 20:24
  • Ok, calling `PDO::errorInfo` gives me that error : `Attempt to initiate a new Adaptive Server operation with results pending [20019] (severity 7) [EXEC dbo.storedProc2 'param1']` which is confusing, I don't get a lot of info about this on Google – MaxiWheat Jul 12 '12 at 01:08
  • Also, using prepared statements and calling `execute` makes everything work fine, but does not make me understand why calling `query` directly is failing – MaxiWheat Jul 12 '12 at 01:37
  • It's a bug on PHP 5.4 from the second query/exec onwards: https://bugs.php.net/bug.php?id=64522 – Capilé Apr 02 '13 at 18:35
  • Thank you for the notice Capilé – MaxiWheat Apr 02 '13 at 19:15

1 Answers1

3

I'm going to do out on a limb and say that your stored procedure returns more than one result set. Either that, or SQL Server 2000 is bloody-mindedly insisting that you close the cursor before next query when it's empty. Either way, this should fix the problem:

$dbh = new PDO ("dblib:host=myhost;dbname=mydb","user","pass");

$results = array();
$query = $dbh->query("EXEC dbo.storedProc1 'param1'");
do {
  $results[] = $query->fetchAll();
} while ($query->nextRowset());
$query->closeCursor();
var_dump($results);

$results = array();
$query = $dbh->query("EXEC dbo.storedProc2 'param1'");
do {
  $results[] = $query->fetchAll();
} while ($query->nextRowset());
$query->closeCursor();
var_dump($results);

$results = array();
$query = $dbh->query("EXEC dbo.storedProc3 'param1'");
do {
  $results[] = $query->fetchAll();
} while ($query->nextRowset());
$query->closeCursor();
var_dump($result);

Be aware that when actually using $results it will be one level deeper than you might expect, because it can store multiple result sets, and these would be stored in separate keys.

DaveRandom
  • 87,921
  • 11
  • 154
  • 174
  • You are totally right about the first Stored Procedure that returns more than one result set. Using your suggestion however makes following calls return empty results (SP 2 and SP 3) (no more exception or error) but the expected data is missing (the first call returns 2 resultsets as expected). How is this happening ? – MaxiWheat Jul 12 '12 at 01:20
  • If you call the SPs on the server with the parameter you use in the script, does get the results you expect? – DaveRandom Jul 12 '12 at 07:57
  • After executing the query, what does `$query->rowCount()` return? And what does `var_dump($results)` actually show? An empty array? – DaveRandom Jul 12 '12 at 11:12
  • On every call (even the first one that returns results) `$query->rowCount()` equals `-1`. And yes, an empty array is returned for second and third results, – MaxiWheat Jul 12 '12 at 12:45
  • How bizarre, never come across `-1` before. What do you see in `var_dump($query->errorInfo())` after the query has executed? – DaveRandom Jul 12 '12 at 12:55
  • `array (size=5) 0 => string '00000' (length=5) 1 => int 0 2 => string '(null) [0] (severity 0) [EXEC dbo.storedProc2 'param1']' (length=80) 3 => int 0 4 => int 0` – MaxiWheat Jul 12 '12 at 13:21
  • Right, not complaining about anything there - do you get results if you try and run SP2 first? – DaveRandom Jul 12 '12 at 13:43
  • Yes, if I run any of the 3 Stored Procedures first, I get the result for the first, and empty for the others. – MaxiWheat Jul 12 '12 at 13:55
  • Well, that's a new one on me, I must say. And you are calling `$query->closeCursor()` after each query? – DaveRandom Jul 12 '12 at 14:08
  • Yes, I used your code example with `$query->closeCursor()` in it – MaxiWheat Jul 12 '12 at 16:59