Thread: bytea insert difference between 8.3 and 9.x

So consider this code C++, using libpqxx:

string = "INSERT INTO foo(x) VALUES( E'" + T.esc_raw(data) + "' )";

foo(x) is bytea , before you ask.

On 8.3, it works fine.
On 9.x:

ERROR:  invalid byte sequence for encoding "UTF8": 0x00 (if \000 is in
the string).

Now, I can take out the E'' and it will work fine on 9.X, but will
whine about it on 8.3. (HINT:  Use the escape string syntax for
escapes, e.g., E'\r\n'.)


I need one piece of code that will work on both, what should I do in
this case ?

Thanks.

--
GJ

On 26 September 2011 14:39, Merlin Moncure <mmoncure@gmail.com> wrote:

> urk -- I have to be honest -- that's a pretty lousy way to send bytea.
> Personally, I'd encode the string as hex and send it like this:
>
> "INSERT INTO foo(x) VALUES( decode('" + hex_string + "'))";
>
> libpqxx doesn't have the ability to parameterize queries?
>

Thanks Merin.

It does, and that's probably what I'll do. Your solution isn't great
either, because it requires extra function to be run on the postgresql
side. Me no likeey that ;)
When you say parameterized - it allows you to prepare queries - which
I do in 80% of select/insert/update cases, apart from some older code
that no one wants to touch.
But the time came for me to act on it, and try to put us forward using
9.1 instead of old rusty 8.3 (which is still better then 8.1 they used
before I started working here).

m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea",
pqxx::prepare::treat_binary);

Gotta try that one with both boys ;)

Btw, I hope 9.1.1 is out soon, gotta package some version for tests.
We used floating point timestamps, and I gotta repackage centos rpms
with that config option - otherwise it's pg_dump and restore of gigs
of data....


--
GJ

On Mon, Sep 26, 2011 at 5:51 AM, Gregg Jaskiewicz <gryzman@gmail.com> wrote:
> So consider this code C++, using libpqxx:
>
> string = "INSERT INTO foo(x) VALUES( E'" + T.esc_raw(data) + "' )";
>
> foo(x) is bytea , before you ask.
>
> On 8.3, it works fine.
> On 9.x:
>
> ERROR:  invalid byte sequence for encoding "UTF8": 0x00 (if \000 is in
> the string).
>
> Now, I can take out the E'' and it will work fine on 9.X, but will
> whine about it on 8.3. (HINT:  Use the escape string syntax for
> escapes, e.g., E'\r\n'.)
>
>
> I need one piece of code that will work on both, what should I do in
> this case ?

urk -- I have to be honest -- that's a pretty lousy way to send bytea.
Personally, I'd encode the string as hex and send it like this:

"INSERT INTO foo(x) VALUES( decode('" + hex_string + "'))";

libpqxx doesn't have the ability to parameterize queries?

merlin

On Mon, Sep 26, 2011 at 8:50 AM, Gregg Jaskiewicz <gryzman@gmail.com> wrote:
> Thanks Merin.
>
> It does, and that's probably what I'll do. Your solution isn't great
> either, because it requires extra function to be run on the postgresql
> side. Me no likeey that ;)

If you are sending a bytea as encoded text, you have to call a
decoding function on the server side no matter what -- it's implicitly
done for you some cases though.  the decode() route is x-version and
guarantees hex method of transfer, not the old escaped method which is
stupid and slow.

The only way to not send encoded text is via binary switch in the
protocol...not sure if libpqxx supports this method of execution.

merlin

Merlin Moncure-2 wrote
>
> m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea",
> pqxx::prepare::treat_binary);
>

I have the same problem. I can't get accsess to pqxx::prepare::treat_binary.
And m_connection.prepare(...) two arguments only. What i mist?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/bytea-insert-difference-between-8-3-and-9-x-tp4840946p5713320.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.