Postgres Cactiverecord Bytea/blob Fields Utf8 Error

I’m facing a pretty weird situation with Postgres. I’m migrating my web application from Firebird using YiiFirebird (fantastic extension by the way) to Postgres. Everything has been running fine until we arrive at the blob fields.

I’m using Postgres 9.2, UTF8. The Firebird database is running on utf8 for almost seven years with zero problems, so I’m quite sure there is nothing to care about Firebird.

I have several routines that save/upload/download/show images working fine when using Firebird. Everything works fine with CActiveRecord with Firebird.

I’m afraid that’s something related with CActiveRecord+PDO which are unable to deal with blob/bytea being a string. It seems it can’t properly escape or encode a string when saving the bytea field. I’m supposing CActiveRecord knows the field type based on Schema. Am I right?

Gii has created the model using ‘string’ type for the bytea field. So, I’m following this convention. Am I right?

I’ve googled a lot and for some reason nobody is facing this problem. Why? Pretty weird. I’ll really appreciate any help on this. I’m struggling on this on last couple days and I just can’t believe that nobody has problems to upload an image when everything is utf8. Please help.

Problem #1: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xff.

  • Running Console Application, Mac OS Terminal.

  • connection array:




	'db' => array(

                    'connectionString'=>'pgsql:host=localhost;port=5432;dbname=blacc',

                    'username' => 'username',

                    'password' => 'password',

                    'charset'  => 'UTF8',

                    'class'    => 'CDbConnection'

                ),



  • code:



        $From = new EntitiesFrom();

        $From = EntitiesFrom::model()->findAll();

        

        foreach($From as $f)

        {

            $To = new Entities();

            $To->attributes = $f->attributes;

            $To->logo_image = $f->logo_image; // This is the company's logo image. Can be jpeg, gif, or png.

            

            if (!$To->insert()) // Tested with save() method too with the same error

            {

                throw new CDbException(Yii::t('yii','Error When Running save() Method!'));

            }

        }



Problem #2: if I use cdbcommand to update the field separately, the uploaded file gets double sized, thus invalid/

* Running normal Web Application:


* In the model's rules, the field logo_image is  "file" and it's uploaded.



		$fp = fopen($uploaded_logo->getTempName(), 'r');

		$content = fread($fp, filesize($uploaded_logo->getTempName()));

		fclose($fp);

		$model->logo_image = $content;                                


		$command=Yii::app()->db->createcommand("UPDATE entities SET logo_image=:logo WHERE id=:id");

		$command->bindValue(":id"  , $model->id);

		$command->bindValue(":logo", $model->logo_image,PDO::PARAM_LOB);



Nobody wants to assist a poor soul????? :mellow:

hello, it’s been a year and I see no response, I have searched the forum and all the examples follows the same error. the problem is the encoding for binary data and the fact that yii not handled correctly. must go to the core and change the way you handle the schema and data type, but I want to get the code for yii, last update continues with the problem. Beam got the solution?