Saving a jpg into Postgresql table

On Thu, Aug 24, 2006 at 06:25:28PM +0800, Gibson wrote:

I have a projects using ASP.NET (VB.net) where i upload a jpeg file
using a web form and then save the jpeg file into a table. I've decided
to use "Oid" instead of "Bytea" as i heard that "Oid" is more memory
efficient.

See the Large Objects documentation:

http://www.postgresql.org/docs/8.1/interactive/largeobjects.html

You can read a large object in chunks, but if you need to assemble
those chunks into a complete object then you might not end up saving
much memory. Bytea can be easier to work with, so before committing
to using large objects I'd recommend evaluating whether you really
have a problem that they'll solve.

So can anyone point me to a website or link where i can see
the VB.net or C# code that does the SQL insert of the jpeg file into the
"Oid" data type column and also how to do a SQL select to retrieve and
display the image on a web form? Thanks

What client library are you using? Npgsql? If so then see "Working
with large object support" in the documentation:

http://npgsql.projects.postgresql.org/docs/manual/UserManual.htm

I see the following in that documentation:

Warning: There is a known issue when working with bytea datatype
and large data. Npgsql will consume very much memory. Please, if
you need to insert more that 4MB of data, please, use Large Object
API. For updates on this issue, check here"

The link leads to a closed bug report entitled "Enormous memory
increase (and application crash) with large BYTEA parameter." You
might wish to read the comments to see if and how the problem has
been addressed.

--
Michael Fuhr

Gibson wrote:

Hi guys,

I have a projects using ASP.NET (VB.net) where i upload a jpeg file
using a web form and then save the jpeg file into a table. I've
decided to use "Oid" instead of "Bytea" as i heard that "Oid" is more
memory efficient. So can anyone point me to a website or link where i
can see the VB.net or C# code that does the SQL insert of the jpeg
file into the "Oid" data type column and also how to do a SQL select
to retrieve and display the image on a web form? Thanks

Hi,
I have problem like yours and I solve it like that:
I transform the jpeg to Base64 text and then store it into normal text
field.
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.

Regards,

Kaloyan Iliev

On Fri, 2006-08-25 at 10:10 +0300, Kaloyan Iliev wrote:

I have a projects using ASP.NET (VB.net) where i upload a jpeg file
using a web form and then save the jpeg file into a table. I've
decided to use "Oid" instead of "Bytea" as i heard that "Oid" is more
memory efficient. So can anyone point me to a website or link where i
can see the VB.net or C# code that does the SQL insert of the jpeg
file into the "Oid" data type column and also how to do a SQL select
to retrieve and display the image on a web form? Thanks

Hi,
I have problem like yours and I solve it like that:
I transform the jpeg to Base64 text and then store it into normal text
field.
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

That way, you can store it in a bytea field and not waste the space.

Regards,
Jeff Davis

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)

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

Am I correct ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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