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] [email protected]
Not PHP bug -> bogus.
------------------------------------------------------------------------
[2008-03-03 20:50:31] [email protected]
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] [email protected]
Assigned to the maintainer.
------------------------------------------------------------------------
[2007-08-31 07:33:12] [email protected]
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
