troubleshooting Question

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

Avatar of TruthHunter
TruthHunter asked on
PHP
2 Comments1 Solution3143 ViewsLast Modified:
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).
ASKER CERTIFIED SOLUTION
Marcus Bointon

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros