Thread: Postgres blob question - insert from basic.
I'm confused about permissions to use lo_import/export:
Docs say this:
test=> CREATE TABLE fruit (name CHAR(30), image OID);
CREATE
test=> INSERT INTO fruit
test-> VALUES ('peach', lo_import('/usr/images/peach.jpg'));
INSERT 27111 1
test=> SELECT lo_export(fruit.image, '/tmp/outimage.jpg')
test-> FROM fruit
test-> WHERE name = 'peach';
lo_export
-----------
1
(1 row)
test=> SELECT lo_unlink(fruit.image) FROM fruit;
lo_unlink
-----------
1
(1 row)
However I want to do this in code (gambas basic), so i've a query where I've
selected a picture from a popup dialog. (Moddbconnect.Exec-Query is just a
function I have to do the database work and works for everything else, so that
is not the problem).
sql = "INSERT INTO demo_blob VALUES( 'my picture', lo_import('"
sql &= Dialog.Path & "'))"
modDBConnect.exec_query(sql)
This bombs, saying I don't have permission as super-user.
The doc's say use psql \lo_import, but I've no idea how to use this within a
query from basic.
Any help appreciated, thanks in advance.
Regards
Richard
richard terry a écrit :
> I'm confused about permissions to use lo_import/export:
...
got the same on command line as a user: must be SU to use lo_import.
but why don't U use a BYTEA (upper size limit is 1GB instead of 2GB,
but I doubt U have such large objects).
on command line, it works as a user:
INSERT INTO fruit ('tst', '/tmp/australia_land_of_dragons.png');
INSERT 0 1
JY
--
Any girl who believes that the way to a man's heart is through
his stomach is obviously setting her standards too high.
On Tuesday 24 November 2009 01:05:20 Jean-Yves F. Barbier wrote:
> richard terry a écrit :
> > I'm confused about permissions to use lo_import/export:
>
> ...
>
> got the same on command line as a user: must be SU to use lo_import.
>
> but why don't U use a BYTEA (upper size limit is 1GB instead of 2GB,
> but I doubt U have such large objects).
>
> on command line, it works as a user:
> INSERT INTO fruit ('tst', '/tmp/australia_land_of_dragons.png');
> INSERT 0 1
>
> JY
>
Thanks for your reply, BTW when replying you should include the original post
as this header dosn't give the information about what the problem/lack of
knowledge was.
Did you try and retreive the image using this code into another png file and
try viewing it to verify it was an image you saved? It dosnt' work in my hands
unfortunately.
Thanks.
Regards
Richard
On Mon, Nov 23, 2009 at 3:49 PM, richard terry <rterry@pacific.net.au> wrote:
> On Tuesday 24 November 2009 01:05:20 Jean-Yves F. Barbier wrote:
>> richard terry a écrit :
>> > I'm confused about permissions to use lo_import/export:
>> ...
>> got the same on command line as a user: must be SU to use lo_import.
>>
>> but why don't U use a BYTEA (upper size limit is 1GB instead of 2GB,
>> but I doubt U have such large objects).
>>
>> on command line, it works as a user:
>> INSERT INTO fruit ('tst', '/tmp/australia_land_of_dragons.png');
>> INSERT 0 1
Careful. The way this INSERT is written, it's inserting the two
strings 'tst' and '/tmp/australia_land_of_dragons.png' into the table
fruit -- it's not inserting the actual PNG.
> Did you try and retreive the image using this code into another png file and
> try viewing it to verify it was an image you saved? It dosnt' work in my hands
> unfortunately.
If you really want to have your blobs stored in the database, I'd
recommend using a "bytea" column as Jean-Yves suggests -- this way you
won't need superuser privs to insert your data, and you'll avoid the
additional complication of lo_import() only working on files on the
server's filesystem. There might be a way to insert OID records
without using lo_import() and without needing PG superuser privileges,
but I haven't tried. For bytea data, you'll have to find the correct
method in gambas' PG adapter for escaping the binary blob that you'll
be inserting; I'm not familiar with gambas, but as an example in
Python I use psycopg2.Binary(..).
I would seriously consider whether you can get away with just storing
the images on the filesystem instead of inside the database, though.
This topic comes up quite often, see e.g.
http://archives.postgresql.org/pgsql-admin/2002-10/msg00318.php
Josh
> If you really want to have your blobs stored in the database, I'd > recommend using a "bytea" column as Jean-Yves suggests -- this way you > won't need superuser privs to insert your data, and you'll avoid the > additional complication of lo_import() only working on files on the > server's filesystem. There might be a way to insert OID records > without using lo_import() and without needing PG superuser privileges, > but I haven't tried. there is an sql method but I have not found the documentation (it's used in SQL backups) there's also the libpq lo_open lo_read lo_write functions that said bytea works well for most purposes, if working in raw sql encode() can be used to convert the double-escaped data to more easily handled base64
On Tuesday 24 November 2009 22:23:01 Jasen Betts wrote: > > If you really want to have your blobs stored in the database, I'd > > recommend using a "bytea" column as Jean-Yves suggests -- this way you > > won't need superuser privs to insert your data, and you'll avoid the > > additional complication of lo_import() only working on files on the > > server's filesystem. There might be a way to insert OID records > > without using lo_import() and without needing PG superuser privileges, > > but I haven't tried. > > there is an sql method but I have not found the documentation (it's used in > SQL backups) there's also the libpq lo_open lo_read lo_write functions > > that said bytea works well for most purposes, if working in raw sql > encode() can be used to convert the double-escaped data to more easily > handled base64 > I've settled for bytea at the moment and using the ordinary gambas database object its workingly simply and quickly, at least with images so far. thanks for all the replies. Regards Richard
I used base64 and even hex , and respectively if you've got 8G of image data it's going to bloat by 1.5 to 2 times. Uploading files to a server-side image directory makes the backup process split into dumping the postgres database and targzipping a directory tree, but quite a few previous applications in richard's domain have gone down that path , if I remember genie and md3. On Tue 24/11/09 11:36 , "richard terry" rterry@pacific.net.au sent: > On Tuesday 24 November 2009 22:23:01 Jasen Betts wrote: > > > If you really want to have your blobs > stored in the database, I'd> > recommend using a "bytea" column > as Jean-Yves suggests -- this way you> > won't need superuser privs to insert your > data, and you'll avoid the> > additional complication of lo_import() only > working on files on the> > server's filesystem. There might be a way > to insert OID records> > without using lo_import() and without > needing PG superuser privileges,> > but I haven't tried. > > > > there is an sql method but I have not found the > documentation (it's used in> SQL backups) there's also the libpq lo_open > lo_read lo_write functions> > > that said bytea works well for most purposes, if > working in raw sql> encode() can be used to convert the > double-escaped data to more easily> handled base64 > > > I've settled for bytea at the moment and using the ordinary gambas database > object its workingly simply and quickly, at least with images so far. > > thanks for all the replies. > > Regards > > Richard > > -- > Sent via pgsql-novice mailing list (p > gsql-novice@postgresql.org)To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > >