Error when binding date parameter to prepared statement -- Using PHP PDO/ODBC with SQL Server

Question

I have a pdo connection with ODBC (v2000.86.359.00) connecting to an SQL Server (v8.00.2039 SP4 Standard Edition) database.

This works:

$id = 486;
$duedate = 'June 27, 2012';
$query ="INSERT into AssetHistory (AssetID, DateDue) Values($id, $duedate);";                   
$noParams = $db->exec($query);
$db->query($query);

But if I try to use a prepared statement like this:

$sql = 'INSERT into AssetHistory 
    (AssetID, DateDue)
    Values(:id, :duedate);';                

$input = array(':id'=>486, ':duedate'=>'June 27, 2012');
$smt = $db->prepare($sql);
$smt->execute($input);

I get this error (from $smt->errorInfo()):

 "[Microsoft][ODBC Driver Manager] Function sequence error (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:254)"

I've tried: 1) Enclosing :duedate in single quotes 2) Binding date to unix timestamp integer 3) Binding :duedate to php DateTime object 4) Inserting just $duedate into the sql statement before preparing

At this point I might be stuck using the $db->query method and sanitizing inputs as best I can, but I would really appreciate any suggestions.

Using PHP 5.38 on a Windows Server 2003 box.

Updates: I've removed some of the variables from a previous version of this question. The error message has changed, but the end result is the same.

I've also tried manually binding the parameters like so:

$smt->bindValue(':id', 486, PDO::PARAM_INT);
$smt->bindValue(':duedate', 'June 27, 2012', PDO::PARAM_STR);

Along with adding the convert function to the sql like so:

$sql = 'INSERT into AssetHistory 
   (AssetID, DateDue)
Values(:id, convert(datetime,:duedate, 100));';

Which returns: "COUNT field incorrect or syntax error "

And I've noticed that SQL Server doesn't really play nice with unix timestamps...

Answer 1

Finally resolved this by downloading and installing both the sqlsrv php drivers (I used version 2.0) and the SQL Server 2008 Native Client.

With a little tweak to the $dsn parameter, the rest of the code works like magic -- no binding, converting or casting required.

I ended up only using the php_pdo_sqlsrv_53_ts_vc9.dll, but make sure you select the correct thread-safe/ non-thread safe version of the driver, as the wrong dll in you php.ini will crash your server.