----- Original Message ----- From: "Daniel Schuchardt" <[EMAIL PROTECTED]> To: "Doug McNaught" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> 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 ! ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly