473,890 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

psql and blob

Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.

I have a script like this :

UPDATE xy SET z = lo_import('loca lpath_and_file' );

but i want to execute this script from the client and so my blob-data is
on the client and lo_import fails (the server doesn't have this file).
Hm.

Thanks for tips - Daniel.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05
15 12371
On Wednesday 17 September 2003 20:37, Daniel Schuchardt wrote:
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel

I'm afraid that You did not understand me.
In that documentation are *all* the ways to import and export large objects.
Check lo_write/lo_read and You'll see that they not depend on server
filesystem.

Regards !

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #11
Yes thats it. Thanks.

Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
Daniel Schuchardt <da************ ***@web.de> writes:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.


The psql function to use is \lo_import--this reads the file from the
client side.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #12
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('BLO BFILE')).

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('BL OBFILE')). 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(Clie ntFile) -> 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.

Thanks
Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
Daniel Schuchardt <da************ ***@web.de> writes:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.


The psql function to use is \lo_import--this reads the file from the
client side.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #13
Daniel Schuchardt <da************ ***@web.de> writes:
So I have to do 2 steps manually in psql:
\lo_import(Clie ntFile) -> Returns OID
INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID) Is there a way to do this automatically?


See psql's :LASTOID (I think that's the name) variable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #14

----- Original Message -----
From: "Daniel Schuchardt" <da************ ***@web.de>
To: "Doug McNaught" <do**@mcnaught. org>
Cc: <pg***********@ 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('BLO BFILE')).

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('BL OBFILE')). 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(Clie ntFile) -> 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/i.../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(Clie ntFile)
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/i.../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/i.../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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #15
Am Do, 2003-09-18 um 20.21 schrieb Darko Prenosil:
----- Original Message -----
From: "Daniel Schuchardt" <da************ ***@web.de>
To: "Doug McNaught" <do**@mcnaught. org>
Cc: <pg***********@ 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('BLO BFILE')).

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('BL OBFILE')). 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(Clie ntFile) -> 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/i.../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(Clie ntFile)
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/i.../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/i.../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_pi cture,
'/Sql/Dump/Blobs/mainmenu.mm_id. '|| CAST(mm_id AS VARCHAR) || '.blob')
FROM mainmenu" $2
psql -h $1 -c "SELECT lo_export(r_blo b,
'/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_i mport('/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_impor t('/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

Nov 11 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
595
by: Daniel Schuchardt | last post by:
Hi @ all, i'm sure there was a psql-function to transfere my Blob-Data to the server but I can't remember. I have a script like this : UPDATE xy SET z = lo_import('localpath_and_file'); but i want to execute this script from the client and so my blob-data is
2
5059
by: Howard Lowndes | last post by:
I'm working psql v7.2.2 to postmaster v7.2.2 and want to use the \lo_import function. The psql manual says that the syntax is \lo_import '<filename>' 'comment' This loads the blob OK and returns the loid but the comment gets lost and doesn't appear when I run \lo_list Any ideas?
4
5232
by: Phil Campaigne | last post by:
Hi All, I just installed postgresql 7.3.4 as an upgrade to 7.3.2 and all went well untill I tried to log into a database that I successfully created. Here are the steps in question: bash-2.05b$ psql -h localhost -U postgres hardwoodthunder Welcome to psql 7.3.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands
7
37786
by: Willem Herremans | last post by:
I am developing a client application for postgreSQL in Tcl/Tk (see http://gborg.postgresql.org/project/pfm ). It mainly uses PgTcl or pgintcl. I don't have any problems with those, but I am also trying to call psql from my application for SQL statements typed directly by the user. I have used the Tcl command set psqlChannel
4
2158
by: Brendan Jurd | last post by:
Hello all, I just wanted to pass on some information about compatibility between the psql client and the postgres server. On a particular network, my workstation and the server are both debian boxes, but the workstation is on a more frequent upgrade schedule. The server is still running postgres 7.3.4, but my workstation has been upgraded with the latest (7.4.1) postgres client tools.
1
2203
by: Josué Maldonado | last post by:
Hello List, I'm having this issue with beta 8.0 C:\pgsql\bin>pg_dump -U postgres farmacia > xfar.sql Password: C:\pgsql\bin>psql -U postgres farmacia2 < xfar.sql Password: psql: FATAL: Password authentication failed for user "postgres" C:\pgsql\bin>psql -U postgres -W farmacia2 < xfar.sql
2
1764
by: Russ Brown | last post by:
Hello, Today I tried connecting to my database locally via psql. I got the usual welcome & basic help messages, but it never got to the prompt: it just hung. So I checked top and the psql process was increasing in size at quite a rate (up to a gig in under 30 seconds). I'd been using psql with no problems only a couple of hours ago, and I haven't installed anything for at least a couple of days that I can think of.
3
4007
by: oksofar | last post by:
Hello - I'm running PG 8.1 on Windows XP. I've installed the server to run as a service. The psql command fails to connect to the server, although I can connect with other clients. When I enter psql -d mydb -h localhost -U admin I get the following response: psql: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" and accepting
0
1685
by: gezerpunta | last post by:
Hi folks :) I need an example which is about pdo connection with bytea type .I searched all day.but I can t find. It looks like blob format.because it shows resorce id when I fetch forum pdo. thanks
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5856
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6064
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4683
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4278
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3287
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.