Re: psql and blob - Mailing list pgsql-general

Am Do, 2003-09-18 um 20.21 schrieb Darko Prenosil:

----- Original Message -----
From: "Daniel Schuchardt" <daniel_schuchardt@web.de>
To: "Doug McNaught" <doug@mcnaught.org>
Cc: <pgsql-general@postgresql.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 !

Ok i will try this. Thats my aim :

I have a masterdatabase located on a computer in our firm. All chages and so are located in this db. This means also icons/pictures my application uses. So this data changes during the time because of development. If we install our app on a new server or update the version I have 2 scripts. First makes a dump. So I dump my administrative tables and blobs in our firm and than i want to upload / install the dumped information to the customers server. Actually this is ugly because of the local pathes. So I have to upload my dump (it consists of seperate text (dump as INSERT WITH COLUMN NAMES) and blob files to the server to reload the data. My scripts look like this :


to prepare

pg_dump -i -h $1 -a -D -t mainmenu -f "/Sql/Dump/ZZ_7 MainMenu.sql" $2
pg_dump -i -h $1 -a -D -t reports -f "/Sql/Dump/ZZ_10 Reports.sql" $2

psql -h $1 -c "SELECT lo_export(mm_picture, '/Sql/Dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) || '.blob') FROM mainmenu" $2
psql -h $1 -c "SELECT lo_export(r_blob, '/Sql/Dump/Blobs/reports.r_id.'|| CAST(r_id AS VARCHAR) || '.blob') FROM reports WHERE r_blob IS NOT NULL" $2

to reload

psql -h $1 -c "DELETE FROM mainmenu;" $2
psql -h $1 -c "DELETE FROM reports;" $2

psql -h $1 -f "/var/tmp/dump/ZZ_7 MainMenu.sql" $2
psql -h $1 -f "/var/tmp/dump/ZZ_10 Reports.sql" $2

psql -h $1 -c "UPDATE mainmenu SET mm_picture=lo_import('/var/tmp/dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) || '.blob') WHERE mm_picture IS NOT NULL" $2
psql -h $1 -c "UPDATE reports SET r_blob=lo_import('/var/tmp/dump/Blobs/reports.r_id.'|| CAST(r_id AS VARCHAR) || '.blob') WHERE r_blob IS NOT NULL" $2

perhaps I should try to dump / reload with oid's