Edit report at http://bugs.php.net/bug.php?id=36561&edit=1
ID: 36561 Comment by: kraven at kraven dot org Reported by: travis at raybold dot com Summary: PDO_ODBC/MSSQL does not work with bound params in subquery Status: Bogus Type: Bug Package: PDO related Operating System: Windows XP PHP Version: 5.2.4 Assigned To: fmk Block user comment: N New Comment: This is caused by a long standing MS SQL ODBC Client bug. http://connect.microsoft.com/SQLServer/feedback/details/521409/odbc-client-mssql-does-not-work-with-bound-parameters-in-subquery Last update on 8/3/2010 was that it would be resolved in a future release of the SQL Server Native Access Client. Previous Comments: ------------------------------------------------------------------------ [2009-04-25 14:37:25] j...@php.net Not PHP bug -> bogus. ------------------------------------------------------------------------ [2008-03-03 20:50:31] auroraeosr...@php.net This appears to be a bug with prepared statements in the underlying microsoft client driver implementation... Even the microsoft SQL Server 2005 Driver for php suffers from this bug <?php $iTestID=1; $connection = sqlsrv_connect($dsn); $statement = sqlsrv_prepare($connection, 'SELECT TestID FROM zTest_TBL WHERE TestID IN (SELECT TestID FROM zTest_TBL WHERE TestID = ?)', array($iTestID)); $value = sqlsrv_execute($statement); print_r(sqlsrv_errors()); ?> so it appears the issue is upstream and we have to wait for microsoft.. ------------------------------------------------------------------------ [2008-01-06 23:02:41] emil at troxy dot net I was able to reproduce this error using the 2008-01-05 snapshot of PDO_ODBC. Reproduce code: --------------- <?php $db = new PDO('odbc:Driver={SQL Server}; Server=localhost; Uid=test; Pwd=test; Database=test;'); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->exec('CREATE TABLE #foo (id INT NOT NULL)'); $db->exec('INSERT INTO #foo VALUES(1)'); $stmt = $db->prepare('SELECT id FROM #foo WHERE id IN (SELECT id FROM #foo WHERE id = ?)'); $stmt->bindValue(1, 1, PDO::PARAM_INT); $stmt->execute(); var_dump($stmt->fetch(PDO::FETCH_ASSOC)); ?> Expected result: ---------------- array(1) { ["id"]=> string(1) "1" } Actual result: -------------- PHP Fatal error: Uncaught exception 'PDOException' with message '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 ..\pecl_5_2\pdo_odbc\odbc_stmt.c:133)' A trace using the Profiler tool shows that the bound integer value is incorrectly interpreted as text: CREATE TABLE #foo (id INT NOT NULL) go INSERT INTO #foo VALUES(1) go declare @P1 int set @P1=NULL exec sp_prepare @P1 output, N'@P1 text', N'SELECT id FROM #foo WHERE id IN (SELECT id FROM #foo WHERE id = @P1)', 1 select @P1 go Incorrect: N'@P1 text' It should be: N'@P1 int' ------------------------------------------------------------------------ [2007-08-31 07:35:18] j...@php.net Assigned to the maintainer. ------------------------------------------------------------------------ [2007-08-31 07:33:12] j...@php.net Very nice that you didn't bother trying with the RCs.. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/bug.php?id=36561 -- Edit this bug report at http://bugs.php.net/bug.php?id=36561&edit=1