Yii Framework Forum: Database Error - convert boolean to integer - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Database Error - convert boolean to integer Datatype mismatch: 7 ERROR Rate Topic: -----

#1 User is offline   badi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 30-August 11
  • Location:Germany/Bruchsal

Posted 26 June 2012 - 03:39 AM

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.

Enviroment:
- PostgreSQL 8.3 -
Server Version 8.3.17
- Yii 1.1.7
- Server software: Apache/2.2.17 (Win32) PHP/5.3.14

thx & greetz badi

0

#2 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,548
  • Joined: 12-October 09
  • Location:Croatia

Posted 26 June 2012 - 04:10 AM

PHP and postgres has different ways of representing boolean values.

By postgres documentation - http://www.postgresq...pe-boolean.html

Try to use

$model->agb = 'true';


Find more about me.... btw. Do you know your WAN IP?
0

#3 User is offline   Devesh Naswa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 13-June 12
  • Location:IIT, New Delhi

Posted 26 June 2012 - 04:13 AM

Best is to change the datatype from Boolean to Int.
0

#4 User is offline   kiran sharma 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 498
  • Joined: 21-May 11
  • Location:India

Posted 26 June 2012 - 04:44 AM

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'

Thanks,
Kiran Sharma.
0

#5 User is offline   badi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 15
  • Joined: 30-August 11
  • Location:Germany/Bruchsal

Posted 26 June 2012 - 05:26 AM

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
0

#6 User is offline   kiran sharma 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 498
  • Joined: 21-May 11
  • Location:India

Posted 26 June 2012 - 06:41 AM

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.
Thanks,
Kiran Sharma.
0

#7 User is offline   keithws 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 03-January 11

Posted 30 July 2012 - 10:12 PM

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.
0

#8 User is offline   keithws 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 03-January 11

Posted 30 July 2012 - 10:31 PM

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.
0

#9 User is offline   ManInTheBox 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 75
  • Joined: 17-June 11

Posted 18 September 2012 - 12:01 PM

View Postkeithws, on 30 July 2012 - 10:31 PM, said:

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.


Thank you! This saved my day :)
Ticket is in 'assigned' status... hope they'll fix it soon...
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users