Skip to main content

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

Comments

Popular posts from this blog

Skype Now Supports Emergency Calls In the US

With the latest round of updates to Skype , it was noticed today that the instant messaging and VoIP phone service now supports emergency calling in the United States. The release notes for the most recent update to the Android version of Skype indicated that the service now allows calls to be made to US emergency calling systems via 911.  Telephony services like Skype, Google Voice , and Vonage typically don't offer this emergency calling ability without some sort of intermediate step like deferring to the phone's default dialer, as is the case with Voice, or setting up a physical address location ahead of time which is how Vonage handles it. The indication is that Skype will handle the 911 calls natively, but when verifying with an account that does not have a Skype phone number assigned to it, it was found that the instructions in the FAQ explaining how to turn on emergency calling are not accurate. Emergency calling support in Skype for the United States Interestingly, th...

Allow Windows authentication using SQL Server driver with DBeaver

DBeaver will allow Microsoft Windows single sign on access when connecting to Microsoft SQL Server using the SQL Server driver (rather than jTDS ).  From the driver properties settings, set the integratedSecurity flag to true . Open the Connection configuration panel and choose the Driver properties section. Set the integratedSecurity flag to true . A subtle, but important step is to not provide username and password credentials to the connection.

Chrome Itself Logs Into Google When Logged Into Company's Services

Google has taken the liberty of logging Chrome into the user's account whenever logged in to any Google service. Some time ago, Google introduced the ability to have its browser, Chrome, log into the user's Google account whenever the user authenticated for any Google service.  That is, log in to Google Docs, for example, and the browser would be logged in as well. The pitch was that this relationship between being logged in to a service and the browser also being logged in, can help mitigate confusion when a user logs out of a Google service but fails to realize the browser is still logged in.  The mystery can be eliminated if the browser keeps the two in the same state automatically. There is the added benefit that the browser would be better able to synchronize the user experience across each browser that was also logged into the user's account. For example, Chrome on a mobile device could have access to the history from Chrome on a laptop. The idea is not nove...