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

Search Discussions

  • Jean-Yves F. Barbier at Nov 23, 2009 at 2:05 pm

    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.
  • Richard terry at Nov 23, 2009 at 8:51 pm

    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
  • Josh Kupershmidt at Nov 23, 2009 at 10:35 pm

    On Mon, Nov 23, 2009 at 3:49 PM, richard terry 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
  • Jasen Betts at Nov 24, 2009 at 11:23 am

    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
  • Richard terry at Nov 24, 2009 at 11:38 am

    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
  • Syan Tan at Nov 25, 2009 at 3:53 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 23, '09 at 11:14a
activeNov 25, '09 at 3:53a
posts7
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase