ID: 44643
User updated by: ethan dot nelson at ltd dot org
Reported By: ethan dot nelson at ltd dot org
Status: Open
Bug Type: PDO related
Operating System: *
PHP Version: 5.2CVS-2009-03-20
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[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/?id=44643&edit=1
