Thread: Saving a jpg into Postgresql table
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 -- Cheers Gibson Tang Game Programmer 179, River Valley Building #04-09 Singapore 179033 Nexgen Studio <www.nexgenstudio.com>
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