Edit report at http://bugs.php.net/bug.php?id=44643&edit=1
ID: 44643 Comment by: kraven at kraven dot org Reported by: ethan dot nelson at ltd dot org Summary: bound parameters ignore explicit type definitions Status: Open Type: Bug Package: PDO related Operating System: * PHP Version: 5.2CVS-2009-03-20 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-05-21 21:49:40] ethan dot nelson at ltd dot org This also happens for the new Native Client 10.0 driver for SQL 2008. SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2 Array ( [0] => 42000 [1] => 402 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types text and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133) [3] => 42000 ) PHP Version 5.2.9-2 System Windows NT LTD-PWWW2 5.2 build 3790 Build Date Apr 9 2009 08:22:37 Configure Command cscript /nologo configure.js "--enable-snapshot- build" "--enable-debug-pack" "--with-snapshot-template=d:\php- sdk\snap_5_2\vc6\x86\template" "--with-php-build=d:\php- sdk\snap_5_2\vc6\x86\php_build" "--with-pdo-oci=D:\php- sdk\oracle\instantclient10\sdk,shared" "--with-oci8=D:\php- sdk\oracle\instantclient10\sdk,shared" Server API ISAPI ------------------------------------------------------------------------ [2009-03-20 17:12:18] phpbugs at matthewboehm dot com RHEL-5.3 php5.2-200903201530 Invalid character value for cast specification: 206 [FreeTDS][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at /usr/src/php5.2- 200903201530/ext/pdo_odbc/odbc_stmt.c:133 ------------------------------------------------------------------------ [2008-10-28 17:37:00] ethan dot nelson at ltd dot org <?php ini_set("display_errors","yes"); $poo = new PDO("odbc:DEVELOPMENT"); $query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2"; $stm = $poo->prepare($query); echo $query."<br>\n"; $id = 1; $stm->bindParam(':id',$id,PDO::PARAM_INT); $id2 = 1; $stm->bindParam(':id2',$id2,PDO::PARAM_INT); echo "<pre>\n"; if ($stm->execute()) print_r($stm->fetchAll(PDO::FETCH_ASSOC)); else print_r( $stm->errorInfo()); echo "</pre>\n"; phpinfo(); ?> -----------The above returns the below result: SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2 Array ( [0] => 42000 [1] => 402 [2] => [Microsoft][SQL Native Client][SQL Server]The data types text and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:133) [3] => 42000 ) PHP Version 5.2.7RC2-dev ------------------------------------------------------------------------ [2008-04-05 00:19:04] ethan dot nelson at ltd dot org Description: ------------ There is a type switching problem with bound parameters in PDO when the query contains a WHERE clause: works - "SELECT * FROM (SELECT 'test' = 1) a" works - "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1" fails - "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1" works - "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id" fails - "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2" Reproduce code: --------------- $con = new PDO('odbc:Brokerage EPC Database'); $query = "SELECT * FROM (SELECT 'test' = 1) a"; $stm = $con->prepare($query); echo $query."<br>\n"; if ($stm->execute()) echo "Yea!<p>\n"; else echo "Boo!<p>\n"; $query = "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1"; $stm = $con->prepare($query); echo $query."<br>\n"; if ($stm->execute()) echo "Yea!<p>\n"; else echo "Boo!<p>\n"; $query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1"; $stm = $con->prepare($query); echo $query."<br>\n"; $id = 1; $stm->bindParam(':id',$id,PDO::PARAM_INT); if ($stm->execute()) echo "Yea!<p>\n"; else echo "Boo!<p>\n"; $query = "SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2"; $stm = $con->prepare($query); echo $query."<br>\n"; $id2 = 1; $stm->bindParam(':id2',$id2,PDO::PARAM_INT); if ($stm->execute()) echo "Yea!<p>\n"; else echo "Boo!<p>\n"; $query = "SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2"; $stm = $con->prepare($query); echo $query."<br>\n"; $id = 1; $stm->bindParam(':id',$id,PDO::PARAM_INT); $id2 = 1; $stm->bindParam(':id2',$id2,PDO::PARAM_INT); if ($stm->execute()) echo "Yea!<p>\n"; else echo "Boo!<p>\n"; Expected result: ---------------- SELECT * FROM (SELECT 'test' = 1) a Yea! SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1 Yea! SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1 Yea! SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2 Yea! SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2 Yea! Actual result: -------------- SELECT * FROM (SELECT 'test' = 1) a Yea! SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = 1 Yea! SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = 1 Boo! SELECT * FROM (SELECT 'test' = 1) a WHERE a.test = :id2 Yea! SELECT * FROM (SELECT 'test' = :id) a WHERE a.test = :id2 Boo! Clearly, by the time the bound parameters hit the database, they've been turned into text type parameters even though they were explicitly bound as int parameters. Error message: Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Native Client][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:133) in D:\Inetpub\include\config.inc on line 173 SQL Profiler trace: declare @p1 int set @p1=NULL exec sp_prepare @p1 output,N'@P1 text,@P2 text',N'SELECT * FROM ptrips_readable() a WHERE a.ptripgroupid = ( SELECT ptripgroupid FROM ptrips_readable() b WHERE b.ptripid = @P1 ) AND a.actiontypeid <> @P2',1 select @p1 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=44643&edit=1
