Обсуждение: Update and bytea problem...

Hi,

I am trying to write a general update command where
there might be "bytea" fields involved.

The update command is generated programatically.

I am having some problems when a bytea field is
involved.

A generated command might look like:

update pics set info='moof.jpg', byteafield='<pgescaped contents of
moof.jpg>'
    where info='doofus.jpg' and byteafield = '<pgescaped contents of
doofus.jpg>'

Unfortunately the above does not seem to work, I keep getting a
syntax error
with an invalid character '\' generally thousands of bytes into the
command.

Does update play nice with bytea used as above ( the escaped contents
of doofus.jpg
are the results of fetching the byteafield as text...)?

For a couple of reasons ( NULL and DEFAULT) I have to use a text
interface.

Thanks

Jerry

On Fri, Dec 09, 2005 at 08:46:17PM -0500, Jerry LeVan wrote:
> I am trying to write a general update command where
> there might be "bytea" fields involved.
>
> The update command is generated programatically.
>
> I am having some problems when a bytea field is
> involved.

What language are you using and how are you escaping the bytea
data?  Could you post a code snippet so we can see exactly what
you're doing?

--
Michael Fuhr

On Dec 9, 2005, at 9:35 PM, Michael Fuhr wrote:

> On Fri, Dec 09, 2005 at 08:46:17PM -0500, Jerry LeVan wrote:
>> I am trying to write a general update command where
>> there might be "bytea" fields involved.
>>
>> The update command is generated programatically.
>>
>> I am having some problems when a bytea field is
>> involved.
>
> What language are you using and how are you escaping the bytea
> data?  Could you post a code snippet so we can see exactly what
> you're doing?
>
> --
> Michael Fuhr

Sigh, it is really messy, I am writing in Tcl and using Pgtcl as
the interface to Postgresql.

This seems to work ok:

    insert into pics byteafield='<pg_escaped string>'


I have been able to extract the byteafield and get the
original file contents , jpg, png, etc...maybe I have been lucky?

However

     update pics set byteafield='<pg_escaped string>' where
byteafield='<other pg_escaped string>'

has always failed for me unless the "other pg_escaped string" is a
simple string.
probably when pg_escaped string == string.

Assuming I am not overlooking something obvious, I wonder how the
"where clause" above is
evaluated for bytea columns... do I need to unpgescape the string
and then pg quote it?

Something is not clear to me: I notice that pg_escaped strings can
contain "'s are
floating about in pg_escaped strings...

Back to manuals...

Jerry

Begin forwarded message:

> From: Jerry LeVan <jerry.levan@eku.edu>
> Date: December 9, 2005 10:45:35 PM EST
> To: Michael Fuhr <mike@fuhr.org>
> Cc: Postgres General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Update and bytea problem...
>
>
> On Dec 9, 2005, at 9:35 PM, Michael Fuhr wrote:
>
>> On Fri, Dec 09, 2005 at 08:46:17PM -0500, Jerry LeVan wrote:
>>> I am trying to write a general update command where
>>> there might be "bytea" fields involved.
>>>
>>> The update command is generated programatically.
>>>
>>> I am having some problems when a bytea field is
>>> involved.
>>
>> What language are you using and how are you escaping the bytea
>> data?  Could you post a code snippet so we can see exactly what
>> you're doing?
>>
>> -- Michael Fuhr
>
> Sigh, it is really messy, I am writing in Tcl and using Pgtcl as
> the interface to Postgresql.
>
> This seems to work ok:
>
>    insert into pics byteafield='<pg_escaped string>'
>
>
> I have been able to extract the byteafield and get the
> original file contents , jpg, png, etc...maybe I have been lucky?
>
> However
>
>     update pics set byteafield='<pg_escaped string>' where
> byteafield='<other pg_escaped string>'
>
> has always failed for me unless the "other pg_escaped string" is a
> simple string.
> probably when pg_escaped string == string.
>
> Assuming I am not overlooking something obvious, I wonder how the
> "where clause" above is
> evaluated for bytea columns... do I need to unpgescape the string
> and then pg quote it?
>
> Something is not clear to me: I notice that pg_escaped strings can
> contain "'s are
> floating about in pg_escaped strings...
>
> Back to manuals...
>
> Jerry

Hmmm,

This seems to work...

     update pic set byteafield='<pg_escaped string>' where byteafield
= pg_quote (<other pg_escaped string)

Still gotta do more testing. Tain't clear why I don't have to pg_quote
the <pg_escaped string> in the set clause...

Jerry