#44597 [Com]: Postgres driver does not prepare booleans correctly

ID: 44597 Comment by: execut3 at gmail dot com Reported By: kenaniah at gmail dot com Status: No Feedback Bug Type: PDO related Operating System: Red Hat 4.1.1 PHP Version: 5.2.6 New Comment: The issue is still not fixed, I'm with PHP 5.2.6 on Ubuntu and postgresql 8.3. ERROR: invalid input syntax for type boolean: "" Previous Comments: ------------------------------------------------------------------------ [2009-05-03 01:00:12] php-bugs at lists dot php dot net No feedback was provided for this bug for over a week, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open". ------------------------------------------------------------------------ [2009-04-25 15:02:13] jani@php.net Please try using this CVS snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://windows.php.net/snapshots/ ------------------------------------------------------------------------ [2009-02-04 03:57:28] kenaniah at gmail dot com This issue seems like it would be a very easy fix and can be reproduced without fail, regardless of server environment or PHP version. A fix would be greatly appreciated ------------------------------------------------------------------------ [2008-10-07 19:23:48] dac514 at hotmail dot com This is happening to me too, OS X and CentOS, PHP 5.2.6 I am converting a web application from MySql to PostgreSQL and i've run into a roadbloack that is forcing me to find every single boolean query and manually binding it instead of benefiting from execute() $input_parameters. PITA! I discovered the explanation to this "bug" here: http://ca.php.net/manual/en/pdostatement.execute.php#84990 As of 5.2.6 you still can't use PDOStatement->execute() $input_parameters to pass a boolean to PostgreSQL. To do that, you'll have to call bindParam() with explicit types for *each* parameter in the query. Pseudo example, where col5 is of type boolean (i.e. tinyint(1) in MySQL) $q = 'INSERT INTO table (col1, col2, col3, col4, col5, col6) VALUES (? , ?, ?, ?, ?, ?)'; $v = array('foo1', 'foo2', 'foo3', foo4', false, 'foo6'); $st = $db->prepare($q); $st->execute($v); PostgreSQL complains and the script dies. Leaving me in the cold and I have to rewrite the code, which becomes excessively painful when the queries are dynamically generated. PostgreSQL workaround, boooooo! $q = 'INSERT INTO table (col1, col2, col3, col4, col5, col6) VALUES (? , ?, ?, ?, ?, ?)'; $v = array('foo1', 'foo2', 'foo3', foo4', false, 'foo6'); $st = $db->prepare($q); $st->bindParam(1, $v[0]], PDO::PARAM_STR); $st->bindParam(2, $v[1]], PDO::PARAM_STR); $st->bindParam(3, $v[2]], PDO::PARAM_STR); $st->bindParam(4, $v[3]], PDO::PARAM_STR); $st->bindParam(5, $v[4]], PDO::PARAM_BOOL); $st->bindParam(6, $v[5]], PDO::PARAM_STR); $st->execute(); Can we get a fix for this soon? ------------------------------------------------------------------------ [2008-04-01 21:00:50] kenaniah at gmail dot com Description: ------------ When using postgres via PDO and attempting to execute an INSERT or UPDATE query using $stmt->execute(array_values($data)) syntax, postgres returns an error for any boolean fields that may be present. Reproduce code: --------------- <?php // $db is my PDO connection object $values = array(true, false); $sql = "UPDATE table SET boolean_column1 = ?, boolean_column2 = ?"; $stmt = $db->prepare($sql); $stmt->execute($values); ?> Expected result: ---------------- PDO will recognize that the values in the array are boolean, and will provide these values to the prepared statement as correctly-formatted booleans. Actual result: -------------- PostgreSQL 8.1.9 returns an error stating that the provided values for the booleans are not in the correct format, and may need to be type-casted. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=44597&edit=1