Yii Framework Forum: Boolean type with PostgreSQL - Yii Framework Forum

Jump to content

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

Boolean type with PostgreSQL problem with inserting FALSE value into a boolean field Rate Topic: -----

#1 User is offline   Alexander Palamarchuk 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 24
  • Joined: 08-August 11
  • Location:Moscow, Russia

Posted 31 May 2012 - 10:44 AM

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

#2 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 478
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 01 June 2012 - 06:44 AM

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

#3 User is offline   Alexander Palamarchuk 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 24
  • Joined: 08-August 11
  • Location:Moscow, Russia

Posted 04 June 2012 - 08:18 AM

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
0

#4 User is offline   Zugluk 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 205
  • Joined: 08-August 12

Posted 08 November 2012 - 07:39 AM

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);

0

#5 User is offline   tremity 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 27-August 11

Posted 14 December 2012 - 11:30 AM

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
1

#6 User is offline   Zugluk 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 205
  • Joined: 08-August 12

Posted 17 December 2012 - 05:04 AM

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

#7 User is offline   tremity 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 27-August 11

Posted 17 December 2012 - 10:18 AM

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
0

#8 User is offline   Zugluk 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 205
  • Joined: 08-August 12

Posted 07 January 2013 - 08:27 AM

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;
    }
          
  }
}

0

#9 User is offline   tremity 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 27-August 11

Posted 07 September 2013 - 06:59 AM

Hi all again (after a very long time). Just a couple of more little things.

So in the class CPgsqlColumnSchema add the code:

	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(is_string($value)) $lower_value = strtolower($value);
			
			if($value===TRUE || $value===true || $lower_value === 't' || $lower_value === 'true' ||
			   $lower_value==='y' || $lower_value==='yes' || $lower_value==='on' ||
			   $lower_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;
		}
	}


Note that there is a small modification with respect to the code posted by Zugluk. The change is:
if(is_string($value)) $lower_value = strtolower($value);

if($value===TRUE || $value===true || $lower_value === 't' || $lower_value === 'true' ||
			   $lower_value==='y' || $lower_value==='yes' || $lower_value==='on' ||
			   $lower_value==='1' || $value===1 ) 
			return 'true';

			else 
			return 'false';

This is necessary for including any possible typing (e.g. TRUE, true, True, FALSE, False , and so on).

Moreover, it is necessary to modify the function createColumnCriteria in the class CDBCommandBuilder (at least in the version of the file I have) as follows:

	public function createColumnCriteria($table,$columns,$condition='',$params=array(),$prefix=null)
	{
		$this->ensureTable($table);
		$criteria=$this->createCriteria($condition,$params);
		if($criteria->alias!='')
			$prefix=$this->_schema->quoteTableName($criteria->alias).'.';
		$bindByPosition=isset($criteria->params[0]);
		$conditions=array();
		$values=array();
		$i=0;
		if($prefix===null)
			$prefix=$table->rawName.'.';
		foreach($columns as $name=>$value)
		{
			if(($column=$table->getColumn($name))!==null)
			{
				if(is_array($value))
					$conditions[]=$this->createInCondition($table,$name,$value,$prefix);
				else if($value!==null)
				{
					if($bindByPosition)
					{
						$conditions[]=$prefix.$column->rawName.'=?';
						$values[]=$value;
					}
					else
					{
						$conditions[]=$prefix.$column->rawName.'='.self::PARAM_PREFIX.$i;
						/* OLD 
						$values[self::PARAM_PREFIX.$i]=$column->typecast($value);
						*/
						$values[self::PARAM_PREFIX.$i]=$column->typecast($value);
						$i++;
					}
				}
				else
					$conditions[]=$prefix.$column->rawName.' IS NULL';
			}
			else
				throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',
					array('{table}'=>$table->name,'{column}'=>$name)));
		}
		$criteria->params=array_merge($values,$criteria->params);
		if(isset($conditions[0]))
		{
			if($criteria->condition!='')
				$criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')';
			else
				$criteria->condition=implode(' AND ',$conditions);
		}
		return $criteria;
	}


The change is
/* OLD 
$values[self::PARAM_PREFIX.$i]=$column->typecast($value);
*/
$values[self::PARAM_PREFIX.$i]=$column->typecast($value);

and it is necessary to make sure that active records use the typecast function defined above.

Cheers
0

#10 User is offline   nineinchnick 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 622
  • Joined: 12-September 11
  • Location:Bialystok, Poland

Posted 07 September 2013 - 12:14 PM

I use PostgreSQL and the only issue I had is a well-known bug in PDO where you can't bind boolean true and false values. You have to use 1 and 0.

There is a bug report in Yii repo on Github for this.
Don't be a dick.
0

#11 User is offline   chalist 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 16-December 12

Posted 12 February 2014 - 02:31 AM

You solved my problem. thanks dude :)
<?php 
/*
 * codeless documentation
 */
?>

@chalist
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