如何在Informix上的磁盘上写入二进制Blob [英] How to write a binary blob onto disk on Informix

问题描述

informix数据库中有一些图像，例如二进制blob字段(jpg)，如何使用SQL将图像写到磁盘上?

I have some images in an informix database, as a binary blob field (jpg), how can i write the images onto disk with an SQL?

推荐答案

数据是否存储在BYTE或BLOB字段中?

Is the data stored in a BYTE or a BLOB field?

如果数据存储在BLOB列中，则可以使用:

If the data is stored in a BLOB column, then you can use:

SELECT LOTOFILE(blob_column, '/path/to/file/on/client', 'client')
  FROM TheTable
 WHERE PK_Column = 23411   -- PK value

如果数据存储在BYTE列中，则您必须付出更大的努力.如果您的计算机上装有ESQL/C(ClientSDK)和C编译器，则建议从 IIUG软件获取SQLCMD.存档并提取软件.您需要Informix环境集，并且需要能够编译C程序.然后运行:

If the data is stored in a BYTE column, then you have to work rather harder. If you have ESQL/C (ClientSDK) and a C compiler on your machine, then I recommend obtaining SQLCMD from the IIUG Software Archive and extracting the software. You need your Informix environment set, and you need to be able to compile C programs. Then run:

./configure --prefix=$HOME/bin

您指定作为前缀并不重要-您只需要运行configure脚本即可.

It doesn't much matter what you specify as the prefix - you just need to run the configure script.

然后可以编译所有内容(make)，也可以仅编译程序selblob(make selblob).这个程序就是我所说的"vignette".一个微观程序，显示如何选择BYTE blob到磁盘.但是，它也具有全部功能.它几乎可以与您扔给它的任何东西一起工作，或者诊断出错误.

You can then either compile everything (make), or you can simply compile the program selblob (make selblob). That program is what I call a 'vignette'; a microscopic program that shows how to select a BYTE blob to disk. It is, however, also fully-functional; it will work with just about anything that you throw at it, or diagnose an error.

如果您的数据库名为precious，则字节数据位于表byte_table中，保存数据的列为byte_column，主键列为col1(所需值是)和col2(且所需值是"Habeas Corpus")，则可以运行:

If your database is called precious, the byte data is in a table byte_table, the column holding the data is byte_column, and the primary key columns are col1 (and the value required is 23) and col2 (and the value required is "Habeas Corpus"), then you can run:

selblob -d precious -t byte_table -k col1=23 -k col2="Habeas Corpus" \
        -c byte_column -f output_file

这会将字节值卸载到命名文件中.

This will unload the byte value into the named file.

如果您没有ESQL/C或C编译器或使用它们的权限，那么生活会更加困难.最接近的方法是在DB-Access中使用UNLOAD语句:

If you don't have ESQL/C or a C compiler or permission to use them, then life is more difficult. The closest approach is to use the UNLOAD statement in DB-Access:

dbaccess precious - <<!
unload to "output_file"
select byte_column from byte_table where col1 = 23 and col2 = 'Habeas Corpus';
!

这将创建一个文件，其中包含字节值的十六进制转储(每个字符2个字节).然后，您需要对文件进行后处理，以将十六进制转换为常规数据.请注意，如果该列是TEXT列而不是BYTE列，则不需要转换.您可以使用一个相当简单的Perl脚本来进行转换(只要文件足够小，可以将其压缩到内存中-如果文件不够小，则必须更加努力地工作):

This will create a file containing a hex-dump of the byte value (2 bytes per character). You then need to post-process the file to convert the hex into regular data. Note that if the column was a TEXT column instead of a BYTE column, then no conversion would be needed. You can use a fairly simple Perl script to do the conversion (provided the file is small enough to be slurped into memory - you have to work harder if it is not small enough):

perl -w -e '
    $/ = "";
    my $data = <>;
    while (length($data) > 1)
    {
        my $hex = substr($data, 0, 2);
        printf "%c", hex($hex);
        $data = substr($data, 2);
    }' <output_file

length条件指定'> 1'以便在卸载的数据末尾处理换行符.

The length condition specifies '> 1' to deal with the newline at the end of the unloaded data.

(出于歇斯底里的葡萄干"(又名历史原因")，尽管IDS 9.00引入了智能斑点"的显式名称BLOB和CLOB，但我仍称BYTE和TEXT为斑点类型".具有大致相应功能的数据类型-在我的书中，它们都是blob(小写)类型，这是在1990年才了解BYTE和TEXT blob的老家伙的麻烦，在添加BLOB和CLOB blob之前的六年或更长时间. 无论如何，对于老式的blob，没有一个很好的替代官方术语.在政治上使用哑巴"是不正确的！)

(For 'hysterical raisins', aka 'historical reasons', I still call both BYTE and TEXT 'blob types', even though IDS 9.00 introduced the explicit names BLOB and CLOB for 'smart blobs', a slightly different pair of data types with roughly corresponding functionality - in my book, they're all blob (lower-case) types. That's the trouble with old guys who learned about BYTE and TEXT blobs in 1990, six years or more before BLOB and CLOB blobs were added. In any case, there isn't a good alternative official terminology for the older style blobs; using 'dumb blobs' is not politically correct!)

这篇关于如何在Informix上的磁盘上写入二进制Blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！