CODEWITHSUNDEEP
phppostgresqlpdo
Ahmad
Ahmad

Reputation: 4254

Postgresql PHP invalid byte sequence for encoding UTF8

I have a simple SQL syntax for inserting to table. I'm using Postgresql 8.4 and already set Database encoding to be UTF8, and POSIX for Collation and Character type.

The query is fine if i run it under pgadmin3, but bring error if i execute in PHP.

"Internal Server Error: SQLSTATE[22021]:
Character not in repertoire: 7 ERROR: 
invalid byte sequence for encoding \"UTF8\": 0xd85b\nHINT:
This error can also happen if the byte sequence does not match the encoding expected by the server,
which is controlled by \"client_encoding\"

So i tried to set NAMES and client_encoding from PHP(PDO), but still have the same problem

$instance->exec("SET client_encoding = 'UTF8';");
$instance->exec("SET NAMES 'UTF8';");

pg_set_client_encoding($link, "UNICODE"); my be work if i'm using native postgresql driver pg_pconnect, but currently i'm using PDO as Driver.

and i also already set mb_internal_encoding('UTF-8');

Is there any other way to fix this issue ?

This error only appear when i trying to insert non ascii word like arabic or japanese word

Upvotes: 12

Views: 32922

Answers (3)

Mani Dwivedi
Mani Dwivedi

Reputation: 73

Answering on an older post but, I just had a similar situation, during a CSV import, I noticed the error: invalid byte sequence for encoding "UTF 8": 0x95 in ....

I've fixed the error by just converting encoding from Windows-1252 to UTF-8 in PHP by using: mb_convert_encoding($fieldValue,'UTF-8','Windows-1252')

    $query = "INSERT INTO student 
              (id, firstName, lastName, age) 
              VALUES 
              (1, '".mb_convert_encoding($firstName,'UTF-8','Windows-1252')."', 
                  '".mb_convert_encoding($lastName,'UTF-8','Windows-1252')."', 23)";

Hope this will help someone.

Upvotes: 2

user3225497
user3225497

Reputation: 39

I'am will be can't submit correct unicode SQL-query (quercus for java vary bad work from unicode and all like "SET NAMES 'UTF8';" no working) , and I resolve this from Base64 convertation:

$name_enc = base64_encode($name);       
$res = $db->prepare(
            'INSERT INTO "MyTable"("ID", "Name") VALUES
               (   nextval(\'gen_addresses\'), 
                   convert_from(decode(?, \'base64\'), \'UTF8\'));' 
     )->execute(array($name_enc));

Upvotes: -1

Dragan Menoski
Dragan Menoski

Reputation: 1102

Try to encode into utf-8 with utf8_encode().

$query = "INSERT INTO student (id, firstName, lastName, age) VALUES (1, 'myFisrtName', 'myLastName', 23)";

pg_exec($connection, utf8_encode($query ));

Upvotes: 11

Related Questions