Home » Database » DB_DataObject » Bug #228

[2003-11-12 09:04 UTC] i at verk dot info

Description:
------------
PHP Version 4.3.4
PostgreSQL 7.3.2

I have a table 'enrollments' with boolean field 'queue_only'.
When I'm trying to insert a record to the table using insert() I get an error because of not correct SQL query generated by DB_DataObject.
It tries to numeric 0 to the bolean field instead of 'f' or any other supported true or false constants (http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x2632%2ehtm).

To fix that I've added following lines to the line 722 of DataObject.php

if ($this->$k == 'f' || $this->$k == 't') {
$rightq .= ' \'' . $this->$k . '\' ';
continue;
}

Reproduce code:
---------------
$enr1 = new DataObjects_Enrollments;
$enr1->uid = $this->abc->uid;
$enr1->course_id = $this->course1->id;
$enr1->queue_only = 'f';
$enr1->insert();

Expected result:
----------------
generated query:
Generated query: INSERT INTO enrollments (id , course_id , uid , queue_only ) VALUES ( 264 , 517 , 551 , '0' )
or
INSERT INTO enrollments (id , course_id , uid , queue_only ) VALUES ( 264 , 517 , 551 , 'f' )

Actual result:
--------------
Generated query: INSERT INTO enrollments (id , course_id , uid , queue_only ) VALUES ( 264 , 517 , 551 , 0 )

Postgres error: "ERROR: column "queue_only" is of type boolean but expression is of type integer
You will need to rewrite or cast the expression"

[2003-11-13 10:53 UTC] i at verk dot info

I've tried that new Generator with original DB and DataObject classes (without my hack). The problem is still exists. insert() as well as update() tries to insert unquoted 0 or 1 to database.

[2003-11-13 12:44 UTC] i at verk dot info

Sorry for second post it was by my mistake.
New Generator works fine. Thanks!

[2005-03-02 18:26 UTC] carlosjordao at gmail dot com

I was thinking about open a new bug form, but I think it would be better re-open this one.

THE PROBLEM:
MySql accepts very well integer 0 and 1 as boolean values 'false' and 'true'. However, PostgreSQL (7.4.6) doesn't act in the same way. It expects explicts 'false' and 'true' strings, or '1' and '0', with quotes.
The problem now is that quoteSmart() of pgsql.php may not differ between integer and bool if data has no specific cast to bool. Even as the '18' DataType (STRING & BOOLEAN) field, the function quote() looks for data type through is_boolean(), is_integer(), making the '18' solution senseless (for PostgreSQL).

In this case, I use FormBuilder module to do the interface with DataObject, but due the debug() output [Problem on update()/insert() queries], I suppose the code would go here. That's why I won't put the output here, but put some examples that could help.

THE SOLUTION:
I guess this would help. I have included those lines into my DataObject.php

(before $v & DB_DATAOBJECT_STR in insert())

if ($v & DB_DATAOBJECT_BOOL) {
$rightq .= $DB->quote((bool)$this->$k) . " ";
continue;
}

(before $v & DB_DATAOBJECT_STR in update())
if ($v & DB_DATAOBJECT_BOOL) {
$settings .= "$kSql = ". $DB->quote((bool)$this->$k) . ' ';
continue;
}