PDO bind/prepared statement problem with ODBC/MS SQL Server

TruthHunter
TruthHunter used Ask the Experts™
on
Hi,

I'm experiencing a strange problem using prepared statements in PDO and am wondering if anyone can tell me if I'm doing something wrong or if this could be a PDO bug.  At first I thought maybe it had to do with binding a string to a MS SQL DATETIME parameter, but as you will see below there is a case that works.  I'm now suspecting there's some kind of issue with subqueries.

The code below is pretty self-explanatory.  The first test-case fails, the second and third succeed.

PHP 5.2.0; using PDO ODBC to connect to MS SQL Server 2000 on Windows XP, IIS 5.1.

Max points assigned; thanks for any help.  If I can't get this to work it's back to PEAR/MDB2.

-----snip-----
<?php

$dsn = "odbc:Driver={SQL Server};Server=[removed];Database=[removed];Uid=[removed];Pwd=[removed];";

$conn = new PDO($dsn, "[removed]", "[removed]", null);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);

// Test case 1: Fails.
$sql = "SELECT CASE WHEN msg.Command IN ('U','D') THEN (SELECT sn.ReplyID FROM SecretNum AS sn WHERE sn.UnitID = msg.SecretNum) ELSE msg.SecretNum END AS RC, msg.Command AS CC FROM ULRTCInputLog AS msg WHERE (msg.Command IN ('U','D','7')) AND ((msg.State = ? AND msg.timeof >= ?) OR (msg.State = ? AND msg.timeof >= ?))";
$params = array ("hq", "2007-05-31 10:11:56", "WE", "2007-05-31 07:11:56");

// Test case 2: Works.  This is simply the first case with params substituted into the query.
//$sql = "SELECT CASE WHEN msg.Command IN ('U','D') THEN (SELECT sn.ReplyID FROM SecretNum AS sn WHERE sn.UnitID = msg.SecretNum) ELSE msg.SecretNum END AS RC, msg.Command AS CC FROM ULRTCInputLog AS msg WHERE (msg.Command IN ('U','D','7')) AND ((msg.State = 'hq' AND msg.timeof >= '2007-05-31 10:11:56') OR (msg.State = 'WE' AND msg.timeof >= '2007-05-31 07:11:56'))";
//$params = array ();

// Test case 3: Works.  Same params but a simpler query.
//$sql = "SELECT msg.Command AS CC FROM ULRTCInputLog AS msg WHERE (msg.Command IN ('U','D','7')) AND ((msg.State = ? AND msg.timeof >= ?) OR (msg.State = ? AND msg.timeof >= ?))";
//$params = array ("hq", "2007-05-31 10:11:56", "WE", "2007-05-31 07:11:56");

try
{
      $stmt = $conn->prepare($sql);
      $stmt->setFetchMode(PDO::FETCH_ASSOC);
}
catch (Exception $e)
{
      print("The preparation of the query failed. " . $e->getMessage());
      exit;
}

try
{
      $stmt->execute($params);
}
catch (Exception $e)
{
      print("The execution of the query failed. " . $e->getMessage());
      exit;
}

try
{
      $result = $stmt->fetchAll();
}
catch (Exception $e)
{
      print("The fetching of the query result failed. " . $e->getMessage());
      exit;
}

print_r($result);

?>
-----snip-----

Test case 1 output:

The execution of the query failed. SQLSTATE[42000]: Syntax error or access violation: 306 [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (SQLExecute[306] at ext\pdo_odbc\odbc_stmt.c:133)

Test case 2 and 3 produce expected results (array output).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Does PDO support subselects for prepared statements anyway? If you output the final generated statement from example 1, you should be able to see why it's failing - given that example 2 works ok, it must mean that PDO is doing something wrong in there.

Author

Commented:
Yep, looks like it's a known PDO bug:

http://bugs.php.net/bug.php?id=36561

Bug #36561 PDO_ODBC/MSSQL does not work with bound params in subquery

But it always pays to reply - enjoy the points... and thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial