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