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

Alan Millington <admillington(at)yahoo(dot)co(dot)uk> writes:
> 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.

You probably need to ask the mxODBC developers (who AFAIK don't hang out
on this list) what they are doing with that data. It sounds fairly
likely to me that the bytea value is just being sent as a string without
any special encoding. That would explain both the null sensitivity you
mention later in the thread, and the encoding validity complaints ---
PG 8.1 was much less picky about string encoding validity than recent
versions are.

There are basically two ways that you could make this work reliably:
arrange for the bytea value to be sent as an out-of-line binary
parameter, or encode it using backslash sequences (eg, '\000' for a
null). Whether the former is possible with mxODBC I dunno. The latter
might be something that mxODBC will do for you if it knows the value
is supposed to be bytea, but without that knowledge I don't see how it
could. You might end up having to do the encoding yourself.

regards, tom lane