How to write a binary blob onto disk on Informix

Question

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?

Answer 1

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

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

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

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

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.

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.

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

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

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

(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!)