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