Database Error - convert boolean to integer

Yii 1.1 General Discussion
1

hi yii-developers,

i have problem with storing a boolean field of a model in my postgresql-db (vers. 8.3). Example extract from table:




CREATE TABLE tbl_user(

  id integer NOT NULL DEFAULT nextval('tbl_user_id_seq'::regclass),

  /* ...  */

  agb boolean,

  CONSTRAINT tbl_user_pkey PRIMARY KEY (id )

)

If I want to update this field I use the following SQL-statement:


UPDATE "tbl_user" SET "agb"=true WHERE "tbl_user"."id"=6

If I using yii, it should worked with follow code:




class BenutzerController extends Controller{ //...

    public function actionUpdate($id){

   	$model = $this->loadModel( 6 );

   	$model->agb = true;

   	$model->save( false, array('agb') );

    }

}

On purpose, I set the agb value to true and save it without validation. The SQL statement executed was:


UPDATE "tbl_benutzer" SET "agb"=:yp0 WHERE "tbl_benutzer"."id"=6. Bound with :yp0=true

But than the error:




CDbCommand couldn't execute SQL-Statement: SQLSTATE[42804]:  Datatype mismatch: 7 ERROR:  

column "agb" is of type boolean but  expression is of type integer

LINE 1: 

UPDATE "tbl_user" SET "agb"=1 WHERE "tbl_user"."id"=6

The boolean that I set, suddenly is an integer with 1. Maybe someone has the same problem and could help me to understand or fix this problem.

[i]Enviroment:

  • PostgreSQL 8.3 - [/i]Server Version 8.3.17

[i]- Yii 1.1.7

  • Server software: Apache/2.2.17 (Win32) PHP/5.3.14

[/i]thx & greetz badi[i]

[/i]

2

PHP and postgres has different ways of representing boolean values.

By postgres documentation - http://www.postgresql.org/docs/8.4/static/datatype-boolean.html

Try to use


$model->agb = 'true';
3

Best is to change the datatype from Boolean to Int.

4

it is not best to change datatype.

you can use yii Active Record type to update data as shown above by mdomba.

…

or in your query use…


UPDATE tbl_user SET agb= 'true' WHERE id= '6'
5

Thank you for your quick help…

Here my results:

@mdomba: I followed your link and tried it in all variations, but nothing works.

@Kiran Sharma: I’m with you, thats not in my opinion to change this datatype - cause of logic.

I found out that the way I tried this example works perfect under PHP version 5.3.6 but at my new laptop recently I installed the PHP version 5.3.14. And in this environment it doesn’t work.

Maybe it’s a bug in PHP or with the newer version I should change some configs or change something in the yii framework. For now I go back to the previos version, it works also fine at my server.

Perhaps someone has a workaround for this situation

greetz badi

6

thats gr8 as system works,

and as i say it is not best to change datatype, but you can change at initial level if you found difficulties.

you can also use bit or smallint datatype supported by postgresql.

7

I am having the same problem. I am running:

Yii 1.10

PHP 5.4.4

PostgreSQL 9.1.4

Somewhere along the line the boolean value is converted to an integer.

8

I fixed this by removing the ‘emulatePrepare’ => true, in the ‘db’ component of config/main.php. I did this after reading PHP Bug #62593 Emulate prepares behave strangely with PARAM_BOOL.

9

Thank you! This saved my day :)

Ticket is in ‘assigned’ status… hope they’ll fix it soon…