Re: psql and blob

----- Original Message -----
From: "Daniel Schuchardt" <daniel_schuchardt(at)web(dot)de>
To: "Doug McNaught" <doug(at)mcnaught(dot)org>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, September 18, 2003 4:44 PM
Subject: Re: [GENERAL] psql and blob

> Yes thats it.
>
> Ok - one last question.
> My Script looks like this and actually i can run it only on the server (so
i have to copy all my data to the server each time i want to update my
blobs):
>
> INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).
>
> Now we know if I want to upload a clientfile I have to use \lo_import BUT
i cant use this inside the script.
>
> INSERT INTO tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not
possible because \lo_import is a unknown command for the server.
>
> So I have to do 2 steps manually in psql:
>
> \lo_import(ClientFile) -> Returns OID
>
> INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)
>
> Is there a way to do this automatically?
> Means my Clientside script should upload a local file (from the same
computer where the script is executed) to the server and insert this file in
a special table automatically.

Maybe Your problem is only to find last inserted oid ?
See: http://www.postgresql.org/docs/7.3/interactive/app-psql.html and look
for LASTOID.
Part from docs:

LASTOID
The value of the last affected OID, as returned from an INSERT or lo_insert
command. This variable is only guaranteed to be valid until after the result
of the next SQL command has been displayed.

So solution might be :
\lo_import(ClientFile)
INSERT INTO tablexy (BLOBFIELD) VALUES (:LASTOID)
I'm not shure what exactly you want to acomplish, but this might work.

I repeat: lo_read/lo_write from libpq are the only true "client side" way I
know. You can write small "C" program that reads the file from local
filesystem and writes it directly to sql server with no "uploads" or nothing
like that.
Look at : http://www.postgresql.org/docs/7.3/interactive/lo-libpq.html
Note that \lo_import and \lo_export PSQL INSTRUCTIONS are using the same
technique, and they act different than server side lo_import() and
lo_export() SERVER SIDE FUNCTIONS. See:
http://www.postgresql.org/docs/7.3/interactive/app-psql.html
Hope this helps.

Regards !