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).
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.