Re: Error "invalid byte sequence for encoding UTF8" on insert into BYTEA column

From: Alan Millington <admillington(at)yahoo(dot)co(dot)uk>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Error "invalid byte sequence for encoding UTF8" on insert into BYTEA column
Date: 2010-01-22 10:27:20
Message-ID: 250054.74312.qm@web25407.mail.ukl.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

>> Today for the first time since upgrading to Postgres 8.4.1 I tried
>> out part of the code which inserts some binary data into a table. The
>> insert failed with the error "invalid byte sequence for encoding
>> UTF8". That is odd, because the column into which the insert was made
>> is of type bytea: the data is meant to be binary data, not
>> UTF8-encoded Unicode.
 
>Inserting in bytea needs an extra level of escaping when the parameters
>are sent inline. See
 
>http://www.postgresql.org/docs/8.4/interactive/datatype-binary.html
 
I have compared the 8.4 documentation for bytea with the 8.1 documentation, and as far as I can see, nothing of significance has changed.
 
>> When I was using Postgres 8.1.4 the same code worked. My code, the
>> mxODBC code and the driver are all unaltered: only the Postgres
>> version has changed.. Is there something I can tweak to get it to
>> work on Postgres 8.4.1?
 
>Very odd. The syntax for this hasn't changed in a long time. And I
>would have thought you'd be sending your paramters out of line anyway.
>Can you check that?
 
The difficulty here is that several layers of software are involved. It is not as though I were writing in C and calling Postgres interface functions directly.
 
What I send to mxODBC is the command as a string containing placeholders (question marks), e.g. "insert into mytable (intcol, byteacol) values (?, ?)", plus the actual values as separate arguments, e.g. 1, data (where 1 is a literal and data is a Python variable containing binary data). What mxODBC does with this I have no idea. It is a black box as far as I am concerned.
 
However, when I was running Postgres 8.1.4 it used to work. If I knew that something had changed in Postgres between 8.1.4 and 8.4.1, I could go to eGenix and ask whether the latest mxODBC version supports the change. However, if you are telling me that nothing in this area has changed in Postgres, I am baffled!

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alberto Colombo 2010-01-22 11:45:30 timestamps, epoch and time zones
Previous Message Yan Cheng Cheok 2010-01-22 09:49:50 Shall I convert all my stored procedure to use "EXECUTE", to ensure I get index-scan