ID: 44643 User updated by: ethan dot nelson at ltd dot org Reported By: ethan dot nelson at ltd dot org -Status: Feedback +Status: Open Bug Type: PDO related Operating System: win2k3 PHP Version: 5.2.5 New Comment:
<?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 Previous Comments: ------------------------------------------------------------------------ [2008-10-21 11:27:15] [EMAIL PROTECTED] Please try using this CVS snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://windows.php.net/snapshots/ ------------------------------------------------------------------------ [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/?id=44643&edit=1