29
Fri, Nov
0 New Articles

Return of the BLOB

General
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: