PostgreSQL \lo_import以及如何将生成的OID转换为UPDATE命令? [英] PostgreSQL \lo_import and how to get the resulting OID into an UPDATE command?

查看:495
本文介绍了PostgreSQL \lo_import以及如何将生成的OID转换为UPDATE命令?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用Postgres 9.0,并且有一个应用程序需要在其中将图像插入到远程 c $ c服务器中。所以我用:

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'";

其中

192.168.1.12 是服务器系统的IP地址

192.168.1.12 is the IP address of the server system

5432 是服务器系统的IP地址。端口号

5432 is the Port number

myDB 是服务器数据库名称

my_admin 是用户名

\lo_import'C:// im / zzz4 .jpg' 是触发的查询。

在将图像插入数据库后,我需要像这样更新表中的一行:

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';

所以我的问题是:如何获得 OID $ lo_import 之后返回$ c>?

So my question is: how do I get the OID returned after the \lo_import in psql?

我尝试运行 \ grelo_import'C://im/zzz4.jpg'在Postgres中,但出现错误:

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'"

尝试了此操作:

update species
set speciesimages=\lo_import 'C://im/zzz4.jpg'
where species='ACAAC04';

但我收到此错误:

ERROR:  syntax error at or near "\"
LINE 2: set speciesimages=\lo_import 'C://im/zzz4.jpg'
                          ^


推荐答案

文件位于本地计算机上,并且您想将blob导入到远程服务器上,您有两个选择:

As your file resides on your local machine and you want to import the blob to a remote server, you have two options:

1)将文件传输到服务器并使用服务器端功能

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)使用 psql元命令,如您所愿。

2) Use the psql meta-command like you have it.

但是您不能将psql元命令与SQL命令混合使用,这是不可能的。

:LASTOID
在同一psql会话中 \lo_import 元命令之后立即启动的> UPDATE 命令:

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';

编写脚本(在Linux中工作,我对Windows Shell脚本不熟悉):

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




  • \\ 是分隔符元命令。您需要在 字符串中将 \ 加倍,因为外壳解释了一层。

  • \ 换行符只是Linux shell中的行延续。

    • \\ 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.
    • 备用语法(再次在Linux上测试):

      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
      

      这篇关于PostgreSQL \lo_import以及如何将生成的OID转换为UPDATE命令?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆