Avatar of TruthHunter
TruthHunter
 asked on

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

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

Avatar of undefined
Last Comment
TruthHunter

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Marcus Bointon

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TruthHunter

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes