How to store image data into VARBINARY datatype

Answer

VARBINARY and ST_GEOMETRY are two new dataypes, that were introduced in NPS 7 and are only supported in IBM Netezza Analytics for INZA 2.5.

It is unsupported in the IBM Netezza SQL Extensions Toolkit. Along these same lines, NPS customers presently use varchar columns to store geometric data. NPS has never had a column type to store binary data.

INZA 2.5 provides customers a specialized means for storing and identifying binary data. The feature also provides support for a variable-length binary data type known as VARBINARY and support for a new ST_GEOMETRY data type that can be used to exclusively store spatial objects or geometric data.                                                        

However, browsing through the documentation in IBM Netezza Analytics for INZA 2.5 was not very clear in describing the use of VARBINARY.

I also found in NPS Administration Guide version 7, section 3-7 "Binary Data Types":

...
You can insert the data for the binary objects using tools such as user-defined functions (UDFs) that create the binary data, or you can specify the content using hexadecimal string
literal notation.

The hexadecimal string literal representation has the following format:
x'hexValue'

A valid hexadecimal string literal must begin with the letter x in uppercase or lowercase followed by a single-quoted string of hexadecimal characters. Each hexadecimal character is
formed as a pair of two characters from the numbers 0 through 9 and the letters A through F (uppercase or lowercase). For example, the string 'hello' appears as x'68656c6c6f' in
hexadecimal string format. A sample INSERT statement with a hexadecimal string literal follows:

insert into my_table values (1, x'68656c6c6f');

By since the representative is in hex; you needs to write a UDF to convert the binary image to hexdecimal code; and another UDF to convert the hexdecimal code back into binary image. Maybe where INZA 2.5 comes in to help in implementing them. Which a lot of room to explore for customer's developer. I even went on and search on Netezza Development Network website but did not find much information about VARBINARY.

So I came up with a simple illustration, by using the linux commands: "xxd"; to help in understanding the use of VARBINARY by example.

Here are the steps :
1) Create the table to store the image data with datatype VARBINARY:
CREATE TABLE Graphics (id INTEGER, icon VARBINARY(32000));

2) Find a jpg file to be store, and put it in your netezza server:
$ ls -l test.jpg
-rwxr-xr-x 1 nz nz 1567 Apr  1 02:18 test.jpg

3)Use "xxd -p" command to convert the 'test.jpg' to hex string format, to be inserted into "Graphics" table

nzsql cf -c "INSERT INTO GRAPHICS values (1, X'`xxd -p test.jpg | tr -d '\n' `');"
INSERT 0 1

That's it. You have store and inserted the image into the table. Now we see how we retrieve it and compare if we are storing it correctly:

4) select the data from the table, and use "xxd -p -r" (reserve the hex to binary) to save into back into another binary file...

nzsql cf -A -t -c "SELECT icon FROM Graphics WHERE id=1;" | cut -d\' -f 2 | cut -d\' -f 1 | xxd -p -r > test.jpg.2

5) Compare the different, there are the same.

$ ls -l test.jpg.*
-rw-rw-r-- 1 nz nz 1567 Apr  1 02:35 test.jpg.2
$ diff test.jpg test.jpg.2


** IMPORTANT: the above is for illustration purpose only for the use of VARBINARY datatype. It is not intended to use as a full solution.. Customer should instead consider writing a UDF to work on similar fashion with greater scalability. Since the example may consume more of the host resource, and might not be fully scalable.

The IBM Netezza Developer Network at ndnsupport@wwpdl.vnet.ibm.com may be able to assist further with UDFs or any C/C++ binaries in UDFs.