ID: 36561
Updated by: [EMAIL PROTECTED]
Reported By: travis at raybold dot com
Status: Assigned
Bug Type: PDO related
Operating System: Windows XP
PHP Version: 5.2.4
Assigned To: fmk
New Comment:
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..
Previous Comments:
------------------------------------------------------------------------
[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..
------------------------------------------------------------------------
[2007-08-30 16:20:54] travis at raybold dot com
the problem still occurs on:
PHP 5.2.4 (cli) (built: Aug 30 2007 07:06:31)
------------------------------------------------------------------------
[2007-06-22 17:50:11] blohr at triad dot rr dot com
This bug affects my app, too. I'm using PHP 5.2.3 on Windows XP Pro
SP2, under both IIS 5.1 and Apache 2.2, with SQL Server 2005 Express.
I don't know if it'll help or not, but here's some more reproduce code.
Just fix the PDO DSN string to something valid.
<?php
$db = new PDO("odbc:dsn=$odbcDsn;uid=$user;pwd=$pass");
$createTable =
'CREATE TABLE ##test (
intCol int,
textCol varchar(20)
)';
$createTable2 =
'CREATE TABLE ##test2 (
intCol2 int,
textCol2 varchar(20)
)';
$db->exec($createTable);
$db->exec($createTable2);
$ins = $db->prepare('INSERT INTO ##test (intCol, textCol) VALUES (:i,
:t)');
$ins->execute(array('i'=>1, 't'=>'A String'));
$ins2 = $db->prepare('INSERT INTO ##test2 (intCol2, textCol2) VALUES
(:i, :t)');
$ins2->execute(array('i'=>1, 't'=>'Another String'));
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->execute(array('t'=>'Another String')) or
var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->execute(array('i'=>1)) or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->bindValue('t', 'Another String');
$sel->execute() or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->bindValue('i', 1);
$sel->execute() or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE textCol2 = :t)');
$sel->bindValue('t', 'Another String', PDO::PARAM_STR);
$sel->execute() or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE intCol2 = :i)');
$sel->bindValue('i', 1, PDO::PARAM_INT);
$sel->execute() or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE textCol2 = :t)');
$t = 'Another String';
$sel->bindParam('t', $t, PDO::PARAM_STR);
$sel->execute() or var_dump($sel->errorInfo());
$sel = $db->prepare('SELECT * FROM ##test WHERE intCol = (SELECT
intCol2 FROM ##test2 WHERE intCol2 = :i)');
$i = 1;
$sel->bindParam('i', $i, PDO::PARAM_INT);
$sel->execute() or var_dump($sel->errorInfo());
?>
------------------------------------------------------------------------
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/36561
--
Edit this bug report at http://bugs.php.net/?id=36561&edit=1
