Edit report at https://bugs.php.net/bug.php?id=57655&edit=1
ID: 57655 Updated by: [email protected] Reported by: nkgrant at gmail dot com Summary: PDO_DBLIB prepare statements quoting integers -Status: Feedback +Status: Open Type: Bug Package: PECL Operating System: Linux (Debian Sarge) PHP Version: 5.2.1 Block user comment: N Private report: N Previous Comments: ------------------------------------------------------------------------ [2014-10-23 03:49:31] [email protected] Works in trunk with SQL Server 2008. require("php_pdo_login.php"); $db = new PDO($dsn,$user,$pass); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "SELECT x from (select cast(1 as int) as x) as tab where x = ?"; $sth = $db->prepare($sql); $sth->execute(array(1)); while ($row = $sth->fetch(PDO::FETCH_ASSOC)){ print_r($row); } ------------------------------------------------------------------------ [2014-10-21 04:17:16] [email protected] All PDO_DBLIB binds are done as strings. To quote one of the PDO authors: "...strings give the greatest fidelity...". There is no formal specification for how PDO *should* bind PHP zvals to SQL Variables. This may change in the future. The workaround would be to bind: :myVar as cast(:myVar as int) ------------------------------------------------------------------------ [2009-08-17 13:01:05] a at a dot com Unfortunately, it does not work even with bindParam(..., PDO::PARAM_INT). I've tried many (all?) potential solutions, but did not found any, except using convert() ------------------------------------------------------------------------ [2007-10-10 11:14:06] richardtector at thekeelecentre dot com bindParam() should allow you to force it to handle it as an integer. I must admit, I find it frustrating that you can't simply specify the parameter types during the prepare as in DB/MDB2 where you just pass an array of types: array('integer', 'boolean') etc. Hope that helps. Richard ------------------------------------------------------------------------ [2007-05-08 14:10:11] nkgrant at gmail dot com Description: ------------ When using PDO_DBLIB to prepare statements, integers are quoted and fatal error is returned. The freetds.log contains the actual query statement sent to the database server (Sybase ASE 11) and the integer value in the query statement was quoted. I tested a prepared statement using the PEAR::DB method and it worked fine. using freetds-stable. (0.63) php 5.2.1 config line: ./configure --with-apache=../apache_1.3.33 --enable-gd-imgstrttf --enable-gd-native-ttf --enable-memory-limit --enable-sablot-errors-descriptive --enable-track-vars --enable-trans-sid --enable-xslt --with-config-file-path=/www/conf --with-curl=/usr/local/lib --with-expat --with-fdftk=/usr --with-freetype-dir=/usr --with-gd-native-ttf --with-gd --with-jpeg-dir=../jpeg-6b --with-openssl --with-png --with-regex --with-sablot-js --with-sybase-ct=/usr/local --with-xml --with-xmlrpc --with-xslt-sablot --with-zlib --enable-ftp --enable -pdo -with-pdo-dblib --with-xsl --with-mysql --with-gmp --with-pdo-odbc=ibm-db2,/home/db2inst1/sqllib Thanks for any help. Reproduce code: --------------- $dsn = 'dblib:host=1.2.3.4:4100'; $db = new PDO($dsn, 'user', 'password',array(PDO_ATTR_PERSISTENT => 1)); $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); $sql = "SELECT x from y..z where x = ?"; $sth = $db->prepare($sql); $sth->execute(array(1)); while ($row = $sth->fetch(PDO::FETCH_ASSOC)){ print_r($row); } Expected result: ---------------- Array ( [x] => 1 ) Actual result: -------------- Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 20018 Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. [20018] (severity 5) [(null)]' in [__FILE__]:[__LINE__] Stack trace: #0 [__FILE__]([__LINE__]): PDOStatement->execute(Array) #1 {main} thrown in [__FILE__] on line [__LINE__] ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=57655&edit=1 -- PECL development discussion Mailing List (http://pecl.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
