PHP KB: No records found when using SQL variables and PDO parameters

Issue

The PDO class for PHP throws an error when running queries against SQL Server and returning multiple results sets.


Cause

Without the benefit of the details around the implementation of SQL Server or the SQLSRV PDO driver, there's no way to know what's causing this issue.  However, the anecdotal evidence points to the the lack of support in the SQLSRV driver for SQL Server's ability to return multiple datasets.

One of the errors that has been encountered reported a question mark (?) placeholder in each place where an SQL variable was in the script that was passed to the PDO::prepare() method call.  This was taken to mean that the method was perceiving each SQL variable as a parameter that it should be looking for in the parameters array passed in the PDOStatement::execute() method.  In other words, it was interpreting SQL parameters (variables) as PDO parameters (?).

Ultimately, this issue is likely to come down to the use of the table data type or temporary tables in the query.


Solution

Use the NO COUNT flag as follows, and at the top of the script.

SET NO COUNT ON

Environment

SQL Server version	2008
PHP version	5.3 Windows
PDO driver version	SQLSRV 2.0
Apache version	2.4 Windows

Details

While it’s difficult to know for sure without extensive testing, the situation seems to be when using SQL variables (@VARIABLENAME) in conjunction with PDO parameters (using the ? placeholder).  There is also some expectation that the issue exists when a query returns multiple results sets.

The sample query below is an example of a case that will present this issue.

DECLARE @variableA INT
DECLARE @variableB TABLE (otherTableID INT)

SET @someID = ?

INSERT into @variableB (otherTableID)
SELECT otherTableID FROM sourceTable WHERE sourceTableID = @someID


SELECT *
FROM yetAnotherTable
WHERE yetAnotherTableID = findMeID

This query would run fine when run directly on SQL Server.  However, when used as the query in a PDO call, like the following, it fails.

$Query = $Conn->prepare("/* Query form example. */");
$Query->execute(array("someIDValue"));

$data = $Query->fetchAll(PDO::FETCH_ASSOC);

The following three (3) lines present the solution, which are really just the first two (2) lines with the NOCOUNT flag set as the first line.

DECLARE @variableA INT
DECLARE @variableB TABLE (otherTableID INT)

SET @someID = ?

INSERT into @variableB (otherTableID)
SELECT otherTableID FROM sourceTable WHERE sourceTableID = @someID


SELECT *
FROM yetAnotherTable
WHERE yetAnotherTableID = findMeID