Bug #75937 [Opn]: PDO_DBLIB prepare statements quoting numerics

Edit report at https://bugs.php.net/bug.php?id=75937&edit=1 ID: 75937 Updated by: adambaratz@php.net Reported by: equaproduction at gmail dot com Summary: PDO_DBLIB prepare statements quoting numerics Status: Open Type: Bug Package: PDO DBlib Operating System: Centos 7.2 PHP Version: 7.2.2 Block user comment: N Private report: N New Comment: I haven't seen that particular error. Wondering if it's an environmental thing. The pdo_dblib driver is unfortunately limited with how it can handle types. It doesn't support true prepared statements, so the only flexibility would be around what kind of SQL string you can get it to produce. There isn't one that handles decimal values. I had worked on an RFC to deal with this: https://wiki.php.net/rfc/pdo_float_type I had trouble getting traction. Perhaps you have a suggestion on how to revive it? All that said, I'd suggest working a CONVERT call into your query as a workaround. Previous Comments: ------------------------------------------------------------------------ [2018-02-08 20:25:45] equaproduction at gmail dot com Actually : PDO::PARAM_BOOL Represents a boolean data type. PDO::PARAM_NULL Represents the SQL NULL data type. PDO::PARAM_INT Represents the SQL INTEGER data type. PDO::PARAM_STR Represents the SQL CHAR, VARCHAR, or other string data type. According to https://wiki.php.net/rfc/pdo_float_type (see Introduction) ; > The PDO extension does not have a type to represent floating point values. > > The current recommended practice is to use PDO::PARAM_STR. ------------------------------------------------------------------------ [2018-02-08 18:24:50] peehaa@php.net Am I missing something here? You are telling PDO the param must be treated as a string using \PDO::PARAM_STR so indeed it is treated as a string. ------------------------------------------------------------------------ [2018-02-08 18:21:57] equaproduction at gmail dot com Description: ------------ When using PDO_DBLIB to prepare statements, numerics are quoted and fatal error is returned. PDOStatement::bindValue() PDOStatement::bindParam() OS : Centos 7.2 PHP : 7.2.2 Freetds : 0.95.81 DBMS : Sybase ASE 12.5.4 & Sybase ASE 16.0 Test script: --------------- // create table foo ( n numeric(14,2) null ) $db = new \PDO("dblib:host=$hostname:$port;dbname=$dbname", "$username", "$pw"); $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); $n = 123.45; $query = 'INSERT INTO foo ( n ) values ( :n )'; $stmt = $db->prepare($query); $stmt->bindParam(':n', $n, \PDO::PARAM_STR); $stmt->execute(); Actual result: -------------- SQLSTATE[HY000]: General error: 20018 Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query. [20018] (severity 16) [INSERT INTO foo ( n ) values ( '123.45' )] ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=75937&edit=1