Zend Framework

This can't be a blocker, because there is a workaround:
you can convert PHP bool variables to the corresponding the 't' or 'f' values in your application code.

Reword summary.

This applies to all RDBMS, not just PostgreSQL, because there may be no implicit mapping from PHP native boolean values to SQL expressions. This could also apply to PHP native null values.

surely some kind of solution can be worked into the framework for this, like maybe some adapter level casting?

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Reassigning as Ralph is the maintainer of Zend_Db

Please mark this as "needed" (or so). It's not just nice to have, but essential when dealing with this kind of database and data.

The workaround by converting bool variables into 't' or 'f' chars/strings is not very nice ... Can I help fixing this issue somehow? We really need this

Regards

Datatype issues

So, how would this work:

$db->query('SELECT * FROM some_table WHERE some_column = :someBoolValue', array('someBoolValue' => true));

There is no way for the statement to know that some_column is the BOOLEAN pgsql datatype? Does it matter? Should we blindly map all php booleans to 't' or 'f' pre-bind and execute?

After looking at the code, it appears this should be applied in the _execute() and bindValue() methods of Zend_Db_Statement_Pdo, does that sound about right?

-ralph

I have not tested it yet, but would this work:

Index: library/Zend/Db/Statement/Pdo.php
===================================================================
--- library/Zend/Db/Statement/Pdo.php   (revision 15638)
+++ library/Zend/Db/Statement/Pdo.php   (working copy)
@@ -229,6 +229,14 @@
     {
         try {
             if ($params !== null) {
+                // ensure PGSQL will cast the php boolean correctly
+                if ($this->_adapter->getConnection()->getAttribute(PDO::ATTR_DRIVER_NAME) == 'PDO_PGSQL') {
+                    foreach ($params as $paramName => $paramValue) {
+                        if (is_bool($paramValue)) {
+                            $params[$paramName] = ($paramValue) ? 'true' : 'false';
+                        }
+                    }
+                }
                 return $this->_stmt->execute($params);
             } else {
                 return $this->_stmt->execute();

Of the three RDBMS I use regularly (PostgreSQL, MySQL, SQLite), all three of them support the use of the integer values 1 and 0 on boolean columns to correspond to TRUE and FALSE, so that would be my recommendation for how to properly map PHP booleans. I've had workarounds in place for this issue since at least 1.0 on my codebase so I am very surprised to see that this bug is still open.

PostgreSQL is not the only RDBMS that needs this fix. The current method of casting also generates errors in MySQL when it is running in ANSI mode. "SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'boolean_column' at row 1"

Here is the fix I have been using for a year with no problems:

--- Zend/Db/Adapter/Pdo/Abstract.php    (revision 16719)
+++ Zend/Db/Adapter/Pdo/Abstract.php    (working copy)
@@ -220,6 +220,9 @@

         if (is_array($bind)) {
             foreach ($bind as $name => $value) {
+                if (is_bool($value)) {
+                    $bind[$name] = $value = $value ? 1 : 0;
+                }
                 if (!is_int($name) && !preg_match('/^:/', $name)) {
                     $newName = ":$name";
                     unset($bind[$name]);

Since MySQL 4.1 the constants TRUE & FALSE were added:
http://dev.mysql.com/doc/refman/4.1/en/boolean-values.html

I think it is better to use the constants in MySQL too because if a real boolean datatype will be added in the future it could be used directly.

Hey Matthew,

Instead of constantly pushing this to "next mini release", how about committing the patch I wrote in July of last year to close the bug?

Regards,

Please note that Matthew didn't update this version specifically. Now that 1.10.1 has been released, all issues that were assigned that version, were upped by one.

The reason it's taking so long is probably that Ralph will have to review and evaluate the patch very thoroughly since this issue is basically asking for BC breaks. I can however imagine that with ZF2.0 in mind, this will be less of a problem now.

This will be addressed in ZF 2.0's Db component

It is one thing to address it in ZF 2.0's Db component, but given that this remedy was mentioned a year ago and a number of us are still using 1.11, combined with the fact that a fix was placed here 2.5 years ago by C Snover (who had been using it for a year at that time)... is it unreasonable to have expected a fix in the 1.x ZF code???