Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });

Susan



Rafal Pietrak
<rafal@zorro.isa-geek.c To: pgsql-general@postgresql.org
om> cc:
Sent by: Subject: Re: [GENERAL] UTF-8 context of BYTEA datatype??

|-------------------|
pgsql-general-owner@pos | [ ] Expand Groups |
tgresql.org |-------------------|


05/29/2006 06:10
AM






On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:
> >
> > How come the bytearea is *interpreted* as having encoding?
>
> Actually, it's not the bytea type that is being interpreted, it's the
> string you're sending to the server that is. Before you send bytea data
> in a query string, you have to bytea encode it first. The DBD::Pg
> manpage seems to suggest something like:
>
> $rv = $sth->bind_param($param_num, $bind_value,
> { pg_type => DBD::Pg::PG_BYTEA });
>

Hmmm, despite initial euphoria, this doesn't actually work.

Subsequently I've also tried putting SQL_BINARY in place of that
hash-ref, and plain DBD::Pg::PG_BYTEA, and also I tried to use 'TYPE =>'
instead of pg_type. (All those hints in man DBI). None of that worked
either.

But I also did:
$db->do('SET client_encoding = LATIN1') or die "SET";
just after connect and before prepare, and this produced a slightly
different result.... no ERROR, but the image was cut short to 9-bytes
inside the database data-row.

Would perl have interpreted this command according to it's semantics?
And change it's own default string handling accordingly!?

Not knowing the internals, I wouldn't bet on whichever, but I have my
doughts - my quess is thet DBI driver doesn't go that far. So if it
hasn't interpretted the 'SET client_encodding' internally, but just
passed that to database, the only thing that changed is the database
frontend context.

So may be the original error came from the database itself anyway?

Any ideas? (still hopping I wont have to write a C-level interface
function just to test what's really happening....

--
-R

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend





----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, 2006-05-30 at 09:05 -0700, SCassidy@overlandstorage.com wrote:
> Did you try escaping the data:
> my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
> DBD::Pg::PG_BYTEA });

No. But:
$ ./test
Undefined subroutine &main::escape_bytea called at ./test line 34.

Where can I find one?
$ grep -Rl escape_bytea /usr/share/perl* /usr/lib/perl*
.... returns nothing. Neither is listed among:
psql>\df
output.

Where should I look for it?

--
-R

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq