PHP PDO_SQLSRV bindparam() not functioning

Question

I am in PHP 5.6.16 on Windows, and previously was using MySQL for all queries with PDO. The only change I have made here is to use Microsoft SQL Server 2008 instead of MySQL as the DB. As such I have now installed the proper SQLSRV drivers, phpinfo() sees it and is happy, and life has continued on with no database connection errors.

After trying to load some pages with read queries, I saw no data coming through. I have discovered that after my $queryResult = $query->execute(); command, this $queryResult variable returns FALSE.

So I went one step higher to find that my actual query in $query still has my prepared variables and not the bound data. Here is where I bind the variables and execute:

$query->bindParam(':startDate', $startDate);
$query->bindParam(':endDate', $endDate);
$queryResult = $query->execute();

Below is a VarDump of $query after these:

object(PDOStatement)#16 (1) { ["queryString"]=> string(86) "SELECT * WHERE UNIX_TIMESTAMP(DATE) >= :startDate AND UNIX_TIMESTAMP(DATE) <= :endDate"}

It's my understanding that here, I should see real data, the full query, not the prepared variable. Is there something with MS SQL SRV that I failed to do during setup or initialization? I haven't found any issue similar to this on the web so far.

Thanks.

There's no `UNIX_TIMESTAMP()` function in SQL Server. See http://stackoverflow.com/questions/8837225/unix-timestamp-in-sql-server — Barmar, Feb 11 '16 at 01:12

RiggsFolly · Answer 1 · 2016-02-11T00:49:03.710

Things dont change when you move to SQL Server, you still have to check for errors like you should have been doing before. Getting a false returned from a prepare or an execute, normally means the query failed for some reason, or the connection is not correctly made

Add some error checking to all calls to the database access layer.

I am assuming you have not set Exceptions on the connection here

// add check for good result from prepare
if ( $query === FALSE ) {
    print_r($conn->errorInfo());
    exit;
}

$query->bindParam(':startDate', $startDate);
$query->bindParam(':endDate', $endDate);
$queryResult = $query->execute();

if ( $queryResult === FALSE ) {
    print_r($query->errorInfo());
    exit;
}

This code should then tell you whats wrong and you can solve it yourself

Oh and you should also be checking that the connection worked correctly and displaying an error if one occurs

Another useful debugging tool is to add these lines after the first <?php tag in a script, while you are debugging. You would not want this info being sent to a real user of course

<?php
   error_reporting(E_ALL); 
   ini_set('display_errors', 1);

Specially if you are working on a LIVE server where error reporting is most likely turned off

PHP PDO_SQLSRV bindparam() not functioning

1 Answers1