Re: psql and blob

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

> ----- 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 !

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