You can use Binary Large Object (BLOB) database fields to store images, sounds, or PC documents in your database. For example, you might put a picture of each item that you make in a BLOB field in your company's Item Master file. That way, it's in the same place as all the other item attributes.

This article explores the use of BLOBs from an RPG program, including how to create the files, how to save BLOB data, and how to retrieve BLOB data.

Before I begin, I should explain that BLOBs are accessible only through SQL. You have to use SQL to create, read, and write from these files. If you try to read a file that contains a BLOB from a file declared with an F-spec, you receive a CPF428A error when you run your program. The error text reads "Open of member MYMBR file MYFILE in MYLIB failed."

So you need to put your BLOBs in a file not already in use by traditional RPG file operations.

For my demonstration, I create a new file (or "Table," in SQL lingo) called ITEMPIC and keyed by an item number. This ItemNo field matches the key in my Item Master file, so I can easily join the two if I want fields from both.

The following SQL code is embedded in my RPG program to create my ITEMPIC file:

     C/EXEC SQL   Create Table ITEMPIC                   
     C+            (                                     
     C+              ItemNo Dec(5,0) Not Null,           
     C+              Picture BLOB(2M) With Default Null, 
     C+              Primary Key( ItemNo )               
     C+            )                                     
     C/END-EXEC

This code creates a table with two fields named ItemNo and Picture. The former is a 5,0 packed decimal field that's not null capable. The latter is a BLOB that can be 2 MB in size. The BLOB field is null capable. The file is keyed by the ItemNo field.

BLOB Fields
It's important to understand that BLOB fields are variable-length fields. Although I've defined this one to be 2 MB long, that's only the maximum size. For example, if I write only 20 bytes to the Picture field, it uses only 20 bytes of disk space, and not the entire 2 MB.

Consider the following code:

     D item            s              5P 0
     D short           s                   SQLTYPE(BLOB:32766)

     C                   eval      item       = 10000
     C                   eval      short_len  = 7
     C                   eval      short_data = x'01240905906788'
     C/EXEC SQL   Insert Into ITEMPIC Values (:item,:short)
     C/END-EXEC

This code writes 7 bytes of binary data to the BLOB field in the ITEMPIC table. You might be wondering where the short_len and short_data fields came from, because I didn't define them. The SQL precompiler has to create an RPG data structure compatible with a BLOB field in the database. When the precompiler sees the SQLTYPE(BLOB:32766) that I coded, it generates the following data structure:

     D SHORT           DS                  
     D SHORT_LEN                     10U 0 
     D SHORT_DATA                 32766A   

To tell it to write a 7 byte field using this data structure, I have to put the length in the short_len field and the data in the short_data field. When I execute the SQL Insert statement, it uses those fields to write to the database.

You can use the same technique to read a BLOB as well. Here's a sample of doing that:

     C/EXEC SQL   Select ItemNo,Picture
     C+             into :item,:short
     C+             from ITEMPIC
     C+             Where ItemNo = 10000
     C/END-EXEC

This loads the 7 bytes into the Short data structure. You can now use those contents for whatever you need to do.

There's a problem with SQLTYPE(BLOB) code, however. My file allows up to 2 MB for the picture field, but I can code only 32 KB in my Short data structure. In fact, 32766 is the largest size that SQL lets me use, so I can't make it larger. Even if I could, I'd still be limited by RPG's maximums, capping the size of the image at 64 KB. Fortunately, other, less limited ways exist.

BLOB Locators
A locator is a way of referring to a BLOB (or other large object field) in an SQL statement without needing to copy the whole field into the program's memory. For example, you could use a BLOB locator with SQL's POSSTR or SUBSTR functions.

You can learn more about locators in the SQL Programming Concepts manual at the following link:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/sqlp/rbafykickoff.htm

File Reference Variables
Usually when you're working with a BLOB it's because you have a PC file that you want to load into your database or read from your database. Microsoft Word documents, PDF documents, picture files, and sound files are some examples.

Indeed, I have no desire to code the hex values for an entire JPG picture in my RPG code! Fortunately, I don't have to. SQL provides another way of accessing BLOBs (and other large objects) — a file reference variable.

A file reference variable is a variable that tells SQL the name of a stream file in the IFS that you want to load a BLOB from, or write a BLOB to. You define a file reference variable in RPG as follows:

     D pic             s                   SQLTYPE(BLOB_FILE)

When the SQL precompiler sees that definition, it converts it to a data structure that looks like this:

     D PIC             DS                  
     D PIC_NL                        10U 0 
     D PIC_DL                        10U 0 
     D PIC_FO                        10U 0 
     D PIC_NAME                     255A   

As you can see, the SQL precompiler took my variable named PIC and created a data structure containing four subfields. The subfields are as follows:

• PIC_NL = Name Length. You place the length of the name in the PIC_NAME field here.

• PIC_DL = Data Length. SQL places the length of data that it writes to the IFS into this field.

• PIC_FO = File Options. You use this field to tell SQL whether to create, replace, or add to your IFS file.

• PIC_NAME = File Name. You place the path name to the IFS file that you want SQL to read from or write to in this field.

For example, if you want to read a JPG file named test.jpg from the /tmp directory of the IFS and insert it into a new record in the ITEMPIC table, you code the following:

     D pic             s                   SQLTYPE(BLOB_FILE)
       .
       .
     C                   eval      item     = 10001
     c                   eval      pic_fo   = SQFRD
     c                   eval      pic_name = '/tmp/test.jpg'
     c                   eval      pic_nl   = %len(%trimr(pic_name))

     C/EXEC SQL   Insert Into ITEMPIC Values (:item,:pic)
     C/END-EXEC

I've set pic_name to the name of the file that I want to insert into the BLOB. The name is an IFS path name. The name length (NL) field is set to the length of that name, but without any of the trailing spaces. In this example, pic_nl is set to 13, and SQL ignores anything past the 13th character of the pic_name field.

When the Insert statement is executed, SQL loads the contents of that JPG file into the BLOB in the ItemPic table.

The file options (FO) field tells SQL what to do with the file. SQL automatically declares the following constants that can be used for the FO field:

• SQFRD = Read Only. This is used when you want to write data to the database from a stream file. If the file doesn't exist, the statement fails, and SQLCOD is set appropriately.

• SQFCRT = Create File. This is used when you want SQL to create the stream file in the IFS and store the data from the database in it. If the file already exists, the statement fails and, SQLCOD is set appropriately.

• SQFOVR = Overwrite File. This is used when you want SQL to create the stream file in the IFS and store the data from the database in it. If the file already exists, it is overwritten.

• SQFAPP = Append To File. This is used when you want SQL to create the stream file in the IFS and store the data from the database in it. If the file already exists, the BLOB data is appended to the end of the file.

The SQFCRT, SQFOVR, and SQFAPP file options create the file if it doesn't exist. The only difference between these options is what happens while the file does exist. When the file exists, SQFCRT produces an error, SQFOVR overwrites the file, and SQFAPP adds to file.

In the preceding Insert example, I told SQL to read the file from the IFS. That's what you typically use with an Insert or Update statement. With a Select statement, you do the following:

     D out             s                   SQLTYPE(BLOB_FILE)
       .
       .
     C                   eval      out_fo   = SQFOVR
     c                   eval      out_name = '/tmp/testout.jpg'
     c                   eval      out_nl   = %len(%trimr(out_name))

     C/EXEC SQL   Select picture
     C+             Into :out
     C+             From ITEMPIC
     C+             Where itemno = :item
     C/END-EXEC

     C*  OUT_DL now contains the length of the data that
     C*         was written to the IFS.

Now that my picture is in the IFS, I can use the IFS APIs to access it, or users can download the picture using a browser, FTP, iSeries Access, or similar tool.

To change my code to read a different type of document, all I need to change in the preceding code is the file name.