#44597 [Com]: Postgres driver does not prepare booleans correctly
| From: | dac514 at hotmail dot com | Date: | Tue, 07 Oct 2008 19:23:49 +0000 |
| Subject: | #44597 [Com]: Postgres driver does not prepare booleans correctly | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-129551@lists.php.net to get a copy of this message | ||
ID: 44597
Comment by: dac514 at hotmail dot com
Reported By: kenaniah at gmail dot com
Status: Open
Bug Type: PDO related
Operating System: Red Hat 4.1.1
PHP Version: 5.2.5
New Comment:
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?
Previous Comments:
------------------------------------------------------------------------
[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