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
