[Alexander Palamarchuk]

Simple Postgres table:

CREATE TABLE public.test (
  id INTEGER NOT NULL, 
  val BOOLEAN NOT NULL, 
  CONSTRAINT test_pkey PRIMARY KEY(id)
);

Do this:

Yii::app()->db->createCommand()->insert('test', array(
		'id'=>1,
		'val'=>true,
	));

Everithing's allright:

Quote

Executing SQL: INSERT INTO "test" ("id", "val") VALUES (:id, :val). Bound with :id=1, :val=true

But doing this

Yii::app()->db->createCommand()->insert('test', array(
		'id'=>1,
		'val'=>false,
	));

I'm getting the error:

Quote

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: ""
LINE 1: INSERT INTO "test" ("id", "val") VALUES ('1', '')
^. The SQL statement executed was: INSERT INTO "test" ("id", "val") VALUES (:id, :val). Bound with :id=1, :val=false

Am I wrong?

[CeBe]

seems to be a bug.
please report it on github: https://github.com/yiisoft/yii/issues
and post a link to the issue here.

[Alexander Palamarchuk]

Found this is a semi-bug/semi-feature in PHP+PDO:
https://bugs.php.net/bug.php?id=33876

Here is the solution to fix it in the Yii:

Quote

This issue has been resolved on the tip of the 5.1 branch.
There was a bug, but your script was still technically "wrong".

By default, PDO treats all data as strings, so the bool was being cast to string, which proved to be incompatible with the bool that pgsql expected.

You need to tell PDO when you're binding booleans (or any type that might be ambiguous to the driver); you can do this using either PDOStatement::bindParam() or PDOStatement::bindValue():

$res->bindValue(1, false, PDO_PARAM_BOOL);
$res->execute();

Report an issue:
https://github.com/y.../yii/issues/779

[Zugluk]

Hi, I'm facing the same problem actually with my fixture and boolean type in tables... I read your post and pasted the code you wrote in cdbCommand in order to avoid problem but it still appears.

To be honest, I don't know why but in cdbCommand in "execute()" function, the $params array is always empty... Though the error return is well "boolean waited but type integer given"...

Where do I put :

if($params) {  
  foreach($params as $param=>$value) 
  {                     
    $this->bindValue($param, $value);                 
  }
}
$this->_statement->execute();

is it in execute() function in cdbCommand ?

$params is always empty don't understand why


EDIT :

Ok I solved the problem of casting boolean in integer with postgres adding in CdbCommandBuilder:208

          if($column->dbType==='boolean') //
            $values[self::PARAM_PREFIX.$i]=$value;//
          else//
            $values[self::PARAM_PREFIX.$i]=$column->typecast($value);

[tremity]

Hi all,
I had the same problem with the following setup:
Ubuntu 12.04 + postgres 9.2 + PHP 5.4 + Yii 1.1.

I tried first the fix suggested from Zugluk:

adding in CdbCommandBuilder:208 (i.e. createInsertCommand function)

          if($column->dbType==='boolean') //
            $values[self::PARAM_PREFIX.$i]=$value;//
          else//
            $values[self::PARAM_PREFIX.$i]=$column->typecast($value);

It works when inserting a new entry in the table but
updates remained a problem.
I also tried to copy past the same code snippet in the
createUpdateCommand but with no results.

After some hours digging around Yii code I was able to
track back to another function: CDbColumnSchema::typecast($value)

CDbColumnSchema is a base class which is extended by database-dependent ones.
In the case of postresql the extended class is called CDbPgsqlColumnSchema.

I fixed the problem as follows:
Copied the function typecast($value) from the base class CDbColumnSchema.
Pasted it in the class CDbPgsqlColumnSchema.
Finally I added a control on the data type at the beginning of the freshly
copied function.
The final code (that is the one that you have to insert in the CDbPgsqlColumnSchema class)
is the following

        /**
	 * Converts the input value to the type that this column is of.
	 * @param mixed $value input value
	 * @return mixed converted value
	 */
	public function typecast($value)
	{
		/*WARNING: I had to add this check:
		 * when the table column is a boolean there are problems because php 
		 * represents boolean differently and there is a misconversion
		 * with this modification it seems to work
		 */
		if(stripos($this->type,'bool')!==false){
			if($value===TRUE || $value===true || $value === 't' || $value === 'true' ||
					$value==='y' || $value==='yes' || $value==='on' ||
					$value==='1' || $value===1 ) 
				return 'true';
			
			else 
				return 'false';
		}
		//END OF MODIFICATION
			
		if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)
			return $value;
		if($value==='')
			return $this->type==='string' ? '' : null;
		switch($this->type)
		{
			case 'string': return (string)$value;
			case 'integer': return (integer)$value;
			case 'boolean': return (boolean)$value;
			case 'double':
			default: return $value;
		}
		
	}

With this code in place everything seems to work (inserts as well as updates).
You can remove the patch provided by Zugluck as well.

cheers

[Zugluk]

My code had to be applied for all times you found "$values[self::PARAM_PREFIX.$i]=$column->typecast($value);", It seems to me it appears 3 times in the file.

[tremity]

I only found two occurrences of "$values[self::PARAM_PREFIX.$i]=$column->typecast($value);".
This is probably why it was only working partly

I should have missed the third occurrence

[Zugluk]

However, I fell on same problem although my changes...

I have one model, but two different forms in order to edit fields separately. In rules I defined, according to the scenario, the safe and unsafe fields. And when I apply a form, the false boolean fields which can be editable only with the second form are set to 'void' (which is normal for in database false is set to 'void') giving the same problem


has someone tested with 1.1.13 ??

EDIT

OK !! completing tremity suggestion I got it working in all cases :

  public function typecast($value)
  {
    /*WARNING: I had to add this check:
     * when the table column is a boolean there are problems because php 
     * represents boolean differently and there is a misconversion
     * with this modification it seems to work
     */
    if(stripos($this->type,'bool')!==false ||
       stripos($this->type,'boolean')!==false){
      if($value===TRUE || $value===true || $value === 't' || $value === 'true' ||
          $value==='y' || $value==='yes' || $value==='on' ||
          $value==='1' || $value===1 ) 
        return 'true';
      
      else 
        return 'false';
    }
    //END OF MODIFICATION
            
    if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression)
      return $value;
    if($value==='')
    { //
      if($this->type === 'bool') //KLUDGE If it's a boolean, when there is void we return '0'
        return '0'; //
      else //
        return $this->type==='string' ? '' : null;
    } //
      
    switch($this->type)
    {
      case 'string': return (string)$value;
      case 'integer': return (integer)$value;
      case 'boolean': return (boolean)$value;
      case 'double':
      default: return $value;
    }
          
  }
}