PHP PDO Query returning an empty array when using bindParam

Question

Kind of stumped by this one. I have been developing for about a week now, maybe two so it might be a noob mistake, but here is what I have:

<?php

$msDB = new PDO('odbc:Driver={SQL Server Native Client 11.0};Server=SOMESERVER;Trusted_Connection=yes;');
try{

//set values for query
$s="4";
$d1="'2014-10-01 00:00:00'";
$d2="'2014-10-31 23:59:59'";

//create query variable
$q1 = "SELECT ID FROM SURVEY_QUESTION_RESPONSE AS t1 WHERE EXISTS
(SELECT * FROM SURVEY_RESPONSE AS tN 
WHERE (tN.ID = t1.SURVEY_RESPONSE_ID) 
AND (t1.SELECTION = :s) 
AND (tN.RESPONSE_DATE BETWEEN :d1 AND :d2))";

//run prepare and bindParam
$tbe = $msDB->prepare($q1);
$tbe->bindParam(':s',$s, PDO::PARAM_INT);
$tbe->bindParam(':d1',$d1, PDO::PARAM_STR);
$tbe->bindParam(':d2',$d2, PDO::PARAM_STR);

//execute query
$tbe->execute();

//fetch resulting data
$res = $tbe->fetchAll(PDO::FETCH_ASSOC);}

//error handling
catch (PDOException $e) {
    throw new pdoDbException($e);
}
//print the resulting array
print_r($res);

//set initial count
$cnt=0;

//loop through and increment count
foreach($res as $key=>$value){
    foreach($value as $v2 ){
        $cnt++;
    }
}

//return count value
echo "Total:<br/>".$cnt."<br/>";

?>

I am expecting this to return a result set of the number 3. And when I specify the values in the query manually, everything works as expected and it returns the number 3.

If I however use the bindParam method it returns nothing and throws no errors of any sort. It simply returns an empty array.

I can also break up the query set in $q1 and concatenate the values into it, and it also works flawlessly. I have not really used bindParam before, but as far as I can tell, I am using it correctly.

Works:

//create query variable
$q1 = "SELECT ID FROM SURVEY_QUESTION_RESPONSE AS t1 WHERE EXISTS
(SELECT * FROM SURVEY_RESPONSE AS tN
WHERE (tN.ID = t1.SURVEY_RESPONSE_ID)
AND (t1.SELECTION = ".$s.")
AND (tN.RESPONSE_DATE BETWEEN ".$d1." AND ".$d2."))";

When I run the query in MSSQL Server Management Studio, it also returns the result set I expect.

Can anyone tell me what I am doing wrong?

Answer 1

I get this error today and take a lot of my hours. After upgrading PHP Version to PHP7, using sqlsrv:database driver instead of dblib:dbname make this problem occurs. To avoid this, still using dblib:dbname event PHP7 FPM already support with sqlsrv:database driver.

Answer 2

The default data type passed by PDO::bindParam and PDO::bindValue is ALWAYS text. Conversion from datatype text in MSSQL is only possible to CHAR, VARCHAR, NCHAR, and NVARCHAR. It is because of the datatype issue that the value has to be converted from text into CHAR or VARCHAR and then into DATETIME from there. This is however an implicit conversion, and depending on the value passed to the query, may result in rounding errors, truncation, or simply a failed conversion.

This, does NOT work:

//create query variable
$q1 = "SELECT ID FROM SURVEY_QUESTION_RESPONSE AS t1 WHERE EXISTS
(SELECT * FROM SURVEY_RESPONSE AS tN 
WHERE (tN.ID = t1.SURVEY_RESPONSE_ID) 
AND (tN.RESPONSE_DATE BETWEEN :d1 AND :d2))";

//run prepare and bindParam
$tbe = $msDB->prepare($q1);
$tbe->bindParam(':d1',$d1, PDO::PARAM_INT);
$tbe->bindParam(':d2',$d2, PDO::PARAM_INT);

This however, does work:

$q1 = 'SELECT ID FROM SURVEY_QUESTION_RESPONSE AS t1 WHERE EXISTS
    (SELECT * FROM SURVEY_RESPONSE AS tN
    WHERE (tN.ID = t1.SURVEY_RESPONSE_ID)
    AND (tN.RESPONSE_DATE BETWEEN CONVERT(datetime,CONVERT(VARCHAR(MAX),?)) 
    AND CONVERT(datetime,CONVERT(VARCHAR(MAX),?))))';

//run prepare and bindParam
$tbe = $msDB->prepare($q1);
$tbe->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$tbe->bindParam(1,$d1);
$tbe->bindParam(2,$d2);

Even if I casted the bound parameters as type INT, they were still passed to MSSQL as text causing the failure.

It would be my suggestion however to simply use the original workaround of just passing in the variables directly like so and have the variables be double quoted strings:

$q1 = "SELECT ID FROM SURVEY_QUESTION_RESPONSE AS t1 WHERE EXISTS
    (SELECT * FROM SURVEY_RESPONSE AS tN
    WHERE (tN.ID = t1.SURVEY_RESPONSE_ID)
    AND (t1.SELECTION = '$s')
    AND (tN.RESPONSE_DATE BETWEEN '$date1' AND '$date2'))";

The quoted string is much easier to handle, and far less prone to error because it need not be converted at all and is simply passed right along to MSSQL Server without issue.

I noticed a slight performance hit with the converts because of the extra processing by MSSQL Server. The query took about half a second longer to run with the conversion than without.

Cheers to @meda for helping with the resolution!