Re: Saving a jpg into Postgresql table

On Fri, 2006-08-25 at 22:49 +0200, Karsten Hilbert wrote:
> On Fri, Aug 25, 2006 at 10:17:34AM -0700, Jeff Davis wrote:
>
> > > It takes aproximately 25-30% more disk space but is much easier for me
> > > to operate with it.
> > > When I read the object from the database I decode it and I have the file
> > > in the original format.
> >
> > Why not go a step further and do this:
> >
> > (1) encode the image as base64
> > (2) insert into mytable(image) values(decode('<base64 encoding of
> > image>','base64'));
> >
> > Then, to get it back:
> >
> > (1) select encode(image,'base64') from mytable;
> > (2) decode the base64 into your image
>
> Should that jumping through the base64 loops even be
> necessary ? I thought that I could just send the raw data
> when it ends up in a bytea field. However, your advice and
> previous suggestions make me have a sneaking suspicion that
> it still depends on how my PG library puts the data on the
> wire:
>
> a) as a string inside the query itself (in which case it
> should not be touched by encoding conversions as its headed
> towards a bytea field but still needs to be quoted properly
> which, again, the library should do)

If you're using a string inside the query and the binary data has
embedded nulls you need to convert those somehow. You can put binary
data into a bytea field in a number of ways, including PQescapeBytea().

> b) in raw binary if bound parameters are used (values
> transmitted separate from the query)
>

It is safe to send binary data directly if you're using bound parameters
(i.e. PQexecParams()).

I didn't mean to suggest that base64 decoding/encoding was the only way
to insert binary data. It is just a convenient way that works
predictably with any language interface without having to reference the
interface's documentation.

Use whatever is easiest, just make sure it's doing what you think it's
doing.

Regards,
Jeff Davis