In “Invasion of the BLOBs” [February 2001, MC], I covered how to use binary large objects (BLOBs) in a client/server environment using Microsoft Access. By using linked tables and Access’ bound object frame control, I showed how easy it is to store picture and sound data in a table containing the BLOB data type.

However, the methods employed had several shortcomings, including poor performance, proprietary storage format, and wasted space. In this article and its accompanying Web sidebar (“Build Blazing Applications with BLOBs,” at www.midrangecomputing.com/mc), I will attempt to show you some programming methods that can alleviate these issues.

Tools to Manipulate BLOBs

To start, AS/400 Client Access Express for Windows V4R5 with the latest service pack and OS/400 V4R4 or higher with the latest database Fixpack are required. The client code is written in Visual Basic for Applications (VBA) using ActiveX Data Objects (ADOs), and the server code is written in RPG IV and C. Due to the length of the code, only highlights will be shown here, while the full code may be downloaded at www.midrangecomputing.com/mc. Also included is a save file containing the object programs for the benefit of those who may not have a C or RPG compiler or the SQL development kit. Finally, only the BLOB data type is discussed here, but the coding principles also apply to character large objects (CLOBs) and double-byte character large objects (DBCLOBs).

Nebulous BLOBs Waste Space

Recall from the February article that, we, an imaginary consulting firm, were hired by a chain of hobby stores called The Yardmaster. Our task was to convert the company’s existing Access database into a client/server environment using an AS/400 as the database server. BLOB data columns were needed in the AS/400 item master table to store pictures and sounds of the products. Figure 1 shows The Yardmaster’s item master table with the picture and sound columns able to store up to 1 MB of binary data.

When defined in a database table, BLOBs have to be defined with a maximum size. This maximum size is reserved in the table even if it is not all used. Therefore, if most of

your BLOBs are only 1 MB, but you reserve 10 MB for your BLOB column due to an occasional large data segment, you will be wasting 9 MB for most of your records. One solution to this problem is to leave the data stored as a file on the AS/400 Integrated File System (AS/400 IFS) rather than storing it in a BLOB.

In the Yardmaster scenario, leaving the data stored in a file on the AS/400 IFS would be useful, as an AS/400 Web site could be built to allow customers to see pictures online and hear sounds that the toys make. If this binary data is stored in BLOBs, it becomes difficult to publish to a Web site, since Web pages usually refer to pictures and sounds via a file name. In addition, the client/server application could also reference the AS/400 IFS files by storing a link to them, thus allowing the binary data to be accessed by both a Web server and the client/server application.

To work with AS/400 IFS files, SQL/400 has a datalink data type that can be used to store a reference to a file and verify that a valid URL has been entered. (Although beyond the scope of this article, datalinks can also be used to alidate an AS/400 IFS file’s existence and to enforce referential integrity between the datalink column and the AS/400 IFS file.)

Figure 2 shows the Create Table statement for a revised item master table, which uses a datalink to store a reference to the file data rather than using a BLOB to store the data itself. As you can see from the definition, the datalink can store a path and file name up to 100 characters long.

Figure 3 (page 64) shows how the datalink column must be populated in an insert (or update) statement by using the DLVALUE function. DLVALUE will convert a character representation of a file name to a datalink. The argument passed to DLVALUE must be a valid URL address. For an AS/400 IFS reference, DLVALUE(‘FILE://SERVER/MYDIR/MYFILE.DAT’) will succeed, but DLVALUE (‘MYDIR/MYFILE.DAT’) will fail because it isn’t a complete URL. Additionally, since datalinks can’t be compared with character variables, special functions like DLURLCOMPLETE are required to cast the datalink to VARCHAR. See the SQL Reference Guide for a complete list of functions (all beginning with the letters DL) used to work with datalinks.

There are a few things to note about the datalink with respect to the Jet database engine. (Jet is used by Access and Visual Basic and is used when linking tables to the AS/400.) Jet does not understand the datalink type. Although Jet can read a datalink column and interpret it as character, it cannot be used to insert or update a row in a table with one. This is because of the special datalink functions required to convert character data to a datalink. Therefore, creating a linked table to a table containing a datalink column will be limited to read-only. Any type of update-capable processing will have to be done with VBA using ADO (RDO or ODBCDirect) or by constructing dynamic pass-through queries. Finally, note that the hyperlink data type in Microsoft Access is not compatible with the AS/400 datalink.

When using a datalink in place of a BLOB, an application accesses the data by reading and writing directly to the file on the AS/400 IFS. However, a few scenarios may make this an undesirable option. The first scenario is security. In my Yardmaster example, all of the remote stores need to access the BLOB data via the Internet, but using NetServer to publish AS/400 IFS data over the Internet isn’t a good idea. Publishing your AS/400 IFS contents over the Internet gives hackers opportunity to steal or even destroy data.

A second undesirable scenario is the difficulty of programming. For example, say the data in an AS/400 IFS file is to be searched or modified with the VBA INSTR or REPLACE functions. Since an AS/400 IFS file may be large, its entire contents might not fit into a single VBA variable. Therefore, the VBA functions will not work without special coding to process the AS/400 IFS file in pieces.

In these two cases, BLOB columns work better, because first, NetServer doesn’t have to publish data over a public network, and second, SQL/400 functions can be used to operate on a BLOB column, regardless of size.

IBM has provided a way to access AS/400 IFS files through the SQL interface. This access is done through a new data type called the BLOB_FILE and is only available in embedded SQL with a host language such as RPG, COBOL, or C. (The Query Manager and SQL Development Kit 5769ST1 are required.) The BLOB_FILE data type is used to transform an AS/400 IFS file to a BLOB column and vice versa, so that a programmer can have the best of both worlds: storing BLOB data in an AS/400 IFS file and having the ability to access that file as a BLOB column when necessary.

Figure 4 shows highlights of the C stored procedure BLOBTOFILE. This program is designed to accept a BLOB parameter (up to 1 MB) and an AS/400 IFS file name. Its job is to convert the BLOB data to an AS/400 IFS file using the BLOB_FILE data type. When the program is completed, it will return the SQL state variable indicating whether the operation was a success.

Figure 4, Section A shows the definition of variables blob_f as a BLOB_FILE and blob as a BLOB(1M). Section B of the figure shows how the SQL precompiler converts these definitions into data structures. Notice that the BLOB data structures have length and data elements. This variable is filled in with the contents of the first parameter: the BLOB passed from the client. The blob_f BLOB_FILE data structures have four elements: name length, data length, file options, and name (the names SQL assigns to these will vary between host languages). The name (file name), name length, and file options (read, write, append, overwrite) must be set before using this variable in an SQL statement. Section C shows the name being set to the 2nd parameter from the client and the file option being set to overwrite.

The BLOB_FILE data type is unique, because it doesn’t store data—it performs a function. When the BLOB_FILE data type is invoked in an SQL statement, it causes the contents of a BLOB to be dumped to an AS/400 IFS file (if write, overwrite, or append are requested), or it will cause an AS/400 IFS file to be read into a BLOB (when read is specified). The VALUES INTO statement (which is like SET, except it can process more than one value) takes the contents of the blob variable and places them into the blob_f variable. But since blob_f is set to perform an overwrite function, the contents of the blob variable will be dumped to the designated file name on the AS/400 IFS.

Note that when a BLOB_FILE variable is used in a VALUES INTO statement, if the BLOB_FILE appears on the left side, the file option should be set to read (and a BLOB variable should be placed on the right side to receive the contents of the AS/400 IFS file). When the BLOB_FILE appears on the right side, the file option should be set to write, overwrite, or append, and a BLOB variable should be on the left. For an INSERT statement, the file option should be set to read, and for a FETCH, the option should be set to one of the write options.

How does this apply to a client/server milieu? In the downloadable code for this article at www.midrangecomputing.com/mc, there is a VBA function called WriteBLOBtoIFS. This code is designed to make use of the BLOBTOFILE program (assuming that, for some reason such as security, you don’t have direct access to the AS/400 IFS). In this sample code, a requested PC file is read and attached to the BLOB parameter with the .AppendChunk method. (Recall that .Get Chunk and .Append Chunk are used to work with large data fields in pieces, since a single variable may not be able to hold all the data at once.) The AS/400 IFS file name is also supplied. When the BLOBTOFILE C program is invoked, it will write the PC file data passed as a BLOB to an AS/400 IFS file. Of course, this is just a long-winded way of copying a PC file to the AS/400 IFS—but it may be necessary if ODBC is your only way of remotely accessing your AS/400 IFS.

Returning the contents of an AS/400 IFS file to a client/server application as a BLOB can be done in a similar way. Unfortunately, ADO parameters do not have a
.GetChunk method, and therefore cannot be used to retrieve BLOB data as a parameter. Instead, a SELECT statement has to be used to get the data.

Figure 5 shows highlights of RPG program FILETOBLOB, which is used as an SQL user-defined function. This function receives the name of an AS/400 IFS file and returns its contents as a BLOB. If a bad file name is passed, then a NULL is returned. Section A shows the SQL data types used. Section B shows how the data types are redefined by the SQL precompiler. Section C reveals the simple code to retrieve the AS/400 IFS file, and section D shows a sample SQL statement utilizing the user-defined function.

In Section B of Figure 5, variable BLOB-DATA is defined not as a BLOB (1M) but as a BLOB_LOCATOR. A locator is a special variable used as a handle to represent the actual BLOB. A locator is useful, because it allows a language like RPG (which has a 32 K variable limit) to work with a BLOB that exceeds this limit in pieces (similar to .GetChunk and .AppendChunk, which allow VBA variables to work with BLOBs in pieces). Also, the SQL SUBSTR and concatenate operations can be performed on these large structures via the use of a locator, without breaking them in pieces. A locator is useful for performance reasons because it allows a row with a BLOB column to be retrieved without actually passing the BLOB data until the data is requested. In the downloadable code for this article, the VBA function ReadBLOBFromIFS shows a sample of how BLOB data could be retrieved from the revised item master table (Figure 2) by converting the AS/400 IFS picture file for a given item number to a BLOB. The data retrieved from the AS/400 IFS is written to a file accessible by the PC.

While the BLOB_FILE data type provides the ability to store BLOBs on the AS/400 IFS (thereby saving space) and have programmatic access to the BLOBs’ contents via SQL as a BLOB column, the BLOB_FILE’s performance can be slow.

Optimizing BLOB Usage

Working with BLOBs can be a challenge. I hope this article helps you determine which storage mechanism to use, BLOB column versus DataLink, and how to use the special data types (BLOB_FILE and LOCATOR) in order to make your work with BLOBs easier.

Create Table Yardmaster/ItemMaster (

ItemNo Char(15), /* Item Number */

Description Char(30), /* Description */

Class Char(5), /* Item Class */

Cost Dec(11,2), /* Item Cost */

Price Dec(11,2), /* Item Price */

MfgID Char(10), /* Mfg ID */

Picture Blob(1M), /* Picture */

Sound Blob(1M), /* Sound Effect */
Primary Key (ItemNo))

Figure 1: This item master table contains two BLOB columns: Picture and Sound.

Create Table Yardmaster/ItemMasRev (

ItemNo Char(15), /* Item Number */

Description Char(30), /* Description */

Class Char(5), /* Item Class */

Cost Dec(11,2), /* Item Cost */

Price Dec(11,2), /* Item Price */

MfgID Char(10), /* Mfg ID */

Picture DataLink(100),/* Picture */

Sound DataLink(100),/* Sound Effect */
Primary Key (ItemNo))

Figure 2: The revised item master table uses datalink columns to reference AS/400 IFS files instead of storing data in BLOBs.

Insert Into Yardmaster/ItemMasRev (ItemNo, Picture, Sound)

Values (‘11900’,DLVALUE(‘FILE://S1020000/YARDMASTER/11900.JPG’),

DLVALUE(‘FILE://S1020000/YARDMASTER/DIESEL.WAV’))

Figure 3: To insert data into a datalink column, the DLVALUE function is used to transform data from character to datalink.

/* Declare SQL Data Types */
EXEC SQL BEGIN DECLARE SECTION;

SQL TYPE IS BLOB_FILE blob_f;
SQL TYPE IS BLOB(1M) blob;
EXEC SQL END DECLARE SECTION;

variable "blob_f" is replaced with:

_Packed struct {
unsigned long name_length;
unsigned long data_length;
unsigned long file_options;
char name[255];
} blob_f;

variable "blob" is replaced with:

_Packed struct blob_t {
unsigned long length;
char data[1048576];
} blob;

/* Set BLOB File data structure elements */
strcpy(blob_f.name,filename);
blob_f.name_length=strlen(filename);
blob_f.file_options = SQL_FILE_OVERWRITE;

/* Convert BLOB to IFS File */
EXEC SQL VALUES :blob INTO :blob_f;

A

B

C

Figure 4: The highlights of the BLOBTOFILE program include variable declarations, precompiler substitutions, and the code to transform a BLOB to an AS/400 IFS file.

A

B

d BLOBDATA s SQLTYPE(BLOB_LOCATOR)
d BLOBFILE s SQLTYPE(BLOB_FILE)

Locator Variable BLOB Data is replaced with:
DBLOBDATA S 10U 0

Blob_File Variable BLOBFILE is replaced with:
DBLOBFILE DS
DBLOBFILE_NL 10U 0
DBLOBFILE_DL 10U 0
DBLOBFILE_FO 10U 0
DBLOBFILE_NAME 255A

c eval blobfile_fo =sqfrd
c eval blobfile_name=pFileName
c eval blobfile_nl=%len(pFileName) *

* Convert IFS File to BLOB and return BLOB Locator Handle
*

c/exec sql
c+ values :blobfile into :blobdata
c/end-exec

Select ItemNo,

FILETOBLOB(IFSFILENAME) /* Return BLOB */

From YardMaster/ItemMasRev
Where ItemNo=?

C

D

Figure 5: The FILETOBLOB user-defined function highlights include variable declarations, precompiler substitutions, the code to transform a BLOB to an AS/400 IFS file, and a sample of how to use it in a SELECT.

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails