Why can't I request a column alias when using PDO on SQL Server?

Question

Note. The only difference in the following examples is the ORDER BY clause .

Good code:

$sql = 'SELECT [date], ? AS [name] FROM [transactions] WHERE [category_id] = 10 GROUP BY [date] ORDER BY [date] ASC'; $stmt = $db->prepare($sql); $stmt->bindValue(1, 'Test', PDO::PARAM_STR); $stmt->execute(); $data = $stmt->fetchAll(); //returns rows in $data 

Bad code:

 $sql = 'SELECT [date], ? AS [name] FROM [transactions] WHERE [category_id] = 10 GROUP BY [date] ORDER BY [date] ASC, [name] ASC'; $stmt = $db->prepare($sql); $stmt->bindValue(1, 'Test', PDO::PARAM_STR); $stmt->execute(); $data = $stmt->fetchAll(); //returns an empty array 

Why is my second block of code not working? If I run any version of this query directly (in SQL Management Studio), it works anyway. And if I get rid of the question mark in PHP and the hardcode value in the request (and not bind it), this also works! What's going on here?

Update: Here is an example PHP script that better illustrates the problem: http://snipt.org/ALhd1 . In this related code sample, I include 5 "tests." Tests # 1, 2, and 4 all return results, while tests # 3 and 5 do not show and should illustrate the problem.

Answer 1

I was able to reproduce the problem with PHP 5.4 and SQL Server 2012.

The problem seems to be related to the ODBC driver for PDO. Successful tests give the same result using both drivers, but test3 is used below as a sample.

Using the built-in SQL Server PHP driver from Microsoft (3.0) gives the correct result;

 $db = new PDO('sqlsrv:server=.\\SQLEXPRESS'); array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL } array(1) { [0]=> array(4) { ["date"]=> string(23) "2013-07-23 10:34:24.497" [0]=> string(23) "2013-07-23 10:34:24.497" ["name"]=> string(4) "Test" [1]=> string(4) "Test" } } 

... when running the same code using ODBC gives your exact unsuccessful result;

 $db = new PDO('odbc:driver={SQL Server Native Client 11.0};server=.\SQLEXPRESS;Trusted_Connection=yes'); array(4) { [0]=>string(5) "00000" [1]=> int(0) [2]=> string(24) " ((null)[0] at (null):0)" [3]=> string(0) "" } array(0) { } 

In other words, this is not a limitation of PDO or SQL Server itself, it is a limitation / error in the ODBC driver.

Answer 2

I tried to reproduce the problem and I can not. However, my suspicion is that “prepare” means identifying the constant in order by , and this is a mistake. You can easily see the error using an explicit constant:

 select * from information_schema.tables t order by 'a' 

Error with error:

 Msg 408, Level 16, State 1, Line 3 A constant expression was encountered in the ORDER BY list, position 1. 

However, this works:

 select *, 'a' as name from information_schema.tables t order by name; 

Here is a suggestion to fix the problem. Try using a subquery:

 SELECT [date], (select ?) AS [name] FROM [transactions] WHERE [category_id] = 10 GROUP BY [date] ORDER BY [date] ASC, [name] ASC; 

The extra select level should convince something, somewhere your value is not a constant (and still assigns it one value).