Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.11.5

Bug #228 insert() fails on postgres boolean data type
Submitted: 2003-11-12 09:04 UTC
From: i at verk dot info Assigned: alan_k
Status: Closed Package: DB_DataObject
PHP Version: 4.3.3 OS: asplinux 9
Roadmaps: (Not assigned)    

 [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 ( 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 00:59 UTC] alan_k
I have modified the generator to treat 'bool' types as STR+BOOL which should mean it will end up being quoted. can you regenerate your .ini file, and try it Thanks Alan
 [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 10:57 UTC] alan_k
a) can you check to see if the boolean field is being flagged correectly in the {database name}.ini should be boolean_field = 18 if not, can you add a line to _generateDefinitionsTable() in Generator.php just before foreach($defs as $t) { print_r($defs); and send me the output Thanks Alan
 [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; }
 [2005-03-03 03:24 UTC] alan_k
casting now added in CVS.
 [2006-12-23 20:29 UTC] cellog (Greg Beaver)
assign to fixer