PostgeSQL \\ lo_import,以及如何获得所产生的OID成一个UPDATE命令? [英] PostgeSQL \lo_import and how to get the resulting OID into an UPDATE command?
问题描述
我在Postgres 9.0的工作,我有一个应用程序,我需要插入图片到远程服务器
。所以我用:
C:\\ Program Files文件\\ PostgreSQL的\\ 9.0 \\ BIN \\ psql.exe-h 192.168.1.12 -p 5432 -d MYDB -U my_admin可以-c\\ lo_import'C ://im/zzz4.jpg';
其中,
192.168.1.12
是服务器系统的IP地址
5432
是端口号
MYDB
是服务器的数据库名称
my_admin可以
是用户名
\\ lo_import'C://im/zzz4.jpg'。
是打响了查询
图像已被插入到数据库后,我需要更新一个表中的一行是这样的:
更新品种
SET speciesimages = 17755; - 从previous命令OID ..如何获得OID?
WHERE品种='ACCOAA';
我的问题是:我如何获得 OID
在PSQL后 \\ lo_import
返回
我试图运行 \\ lo_import'C://im/zzz4.jpg
在Postgres的,但我得到一个错误:
错误:语法错误或接近\\ lo_import'C://im/zzz4.jpg'
LINE 1:\\ lo_import'C://im/zzz4.jpg'
我也尝试这样做:
更新品种
设置speciesimages = \\ lo_import'C://im/zzz4.jpg
其中,种='ACAAC04';
但我得到这个错误:
错误:语法错误或接近\\
LINE 2:设置speciesimages = \\ lo_import'C://im/zzz4.jpg
^
随着你的文件驻留在本地机器上,你要导入的BLOB到远程服务器,你有两个选择:
1)将文件传输到服务器,并使用服务器端功能:
更新品种
SET speciesimages = lo_import('/路径/要/服务器的本地/文件/ zzz4.jpg')
WHERE品种='ACAAC04';
2)使用 psql元-command 喜欢你拥有它。
但你不能混合使用SQL的命令psql元命令,这是不可能的。结果
使用PSQL变量:LASTOID
在更新
命令,你的 \\ lo_import后立即启动
在同一会话PSQL元命令:
更新品种
SET speciesimages =:LASTOID
WHERE品种='ACAAC04';
要脚本(在Linux下正常工作,我不熟悉Windows shell脚本):
回声\\ lo_import'/path/to/my/file/zzz4.jpg\\\\\\\\ UPDATE种SET speciesimages =:LASTOID WHERE品种='ACAAC04'; | \\
PSQL -h 192.168.1.12 -p 5432 -d MYDB -U my_admin可以
-
\\\\
是分隔符元命令。你需要加倍\\
,在,
字符串,因为shell间$ P $点一层。 -
\\
换行符之前只是在Linux shell的续行。
替代语法(在Linux上测试过一次):
的psql -h 192.168.1.12 -p 5432 -d MYDB -U my_admin可以<< EOF
\\ lo_import'/path/to/my/file/zzz4.jpg
UPDATE种
SET speciesimages =:LASTOID
WHERE品种='ACAAC04';
EOF
I'm working with Postgres 9.0, and I have an application where I need to insert images into the remote server
. So I use:
"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.12 -p 5432 -d myDB -U my_admin -c "\lo_import 'C://im/zzz4.jpg'";
where
192.168.1.12
is the IP address of the server system
5432
is the Port number
myDB
is server database name
my_admin
is the username
"\lo_import 'C://im/zzz4.jpg'"
is the query that is fired.
After the image has been inserted into the database I need to update a row in a table like this:
UPDATE species
SET speciesimages=17755; -- OID from previous command.. how to get the OID ??
WHERE species='ACCOAA';
So my question is: how do I get the OID
returned after the \lo_import
in psql?
I tried running \lo_import 'C://im/zzz4.jpg'
in Postgres but I get an error:
ERROR: syntax error at or near ""\lo_import 'C://im/zzz4.jpg'""
LINE 1: "\lo_import 'C://im/zzz4.jpg'"
I also tried this:
update species
set speciesimages=\lo_import 'C://im/zzz4.jpg'
where species='ACAAC04';
But I get this error:
ERROR: syntax error at or near "\"
LINE 2: set speciesimages=\lo_import 'C://im/zzz4.jpg'
^
As your file resides on your local machine and you want to import the blob to a remote server, you have two options:
1) Transfer the file to the server and use the server-side function:
UPDATE species
SET speciesimages = lo_import('/path/to/server-local/file/zzz4.jpg')
WHERE species = 'ACAAC04';
2) Use the psql meta-command like you have it.
But you cannot mix psql meta commands with SQL-commands, that's impossible.
Use the psql variable :LASTOID
in an UPDATE
command that you launch immediately after the \lo_import
meta command in the same psql session:
UPDATE species
SET speciesimages = :LASTOID
WHERE species = 'ACAAC04';
To script that (works in Linux, I am not familiar with Windows shell scripting):
echo "\lo_import '/path/to/my/file/zzz4.jpg' \\\\ UPDATE species SET speciesimages = :LASTOID WHERE species = 'ACAAC04';" | \
psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin
\\
is the separator meta-command. You need to double the\
, in a""
string, because the shell interprets one layer.\
before the newline is just the line continuation in Linux shells.
Alternative syntax (tested on Linux again):
psql -h 192.168.1.12 -p 5432 -d myDB -U my_admin << EOF
\lo_import '/path/to/my/file/zzz4.jpg'
UPDATE species
SET speciesimages = :LASTOID
WHERE species = 'ACAAC04';
EOF
这篇关于PostgeSQL \\ lo_import,以及如何获得所产生的OID成一个UPDATE命令?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!