As the IFS grows in popularity on the iSeries, it is becoming important to access text file content programmatically. INI files, XML files, and delimited files store important application settings and data. In this two-part series, I'll offer two mechanisms whereby SQL can query IFS file data.

The first technique utilizes a user-defined function (UDF) to read the entire contents of an IFS file and return it as a single column in a SELECT statement. The function receives a qualified IFS file name and returns the file's contents in a character large object (CLOB) variable.

Program FileToClob is an embedded SQL RPG program that serves as an external UDF to place file contents in a CLOB. The concept is illustrated by the following SELECT:

Before using FileToClob, register it with SQL by using the Create Function statement:

CLOBs are superior to VARCHARs in that they can hold up to 16 MB of data, whereas VARCHARs are limited to about 32 K. (Note that some older querying tools may not recognize large object data types.) Further, green-screen tools such as STRSQL will display a value of '*POINTER' for a CLOB. In these cases, a portion of the CLOB's contents must first be cast to VARCHAR.

Let's look at this small program. The reading of the IFS file into a CLOB is handled by a peculiar data type called a CLOB_FILE that is only available to high-level language (HLL)programs using embedded SQL:

The CLOB_FILE data type definition is translated by the SQL pre-compiler into the following data structure:

Notice that subfields are included that contain the original variable name followed by a suffix. The _fo (file option) suffix defines whether the file will be opened for read, append, or write. The _nl and _name variables represent the file name and file name length. To use the CLOB_FILE data type, these subfields need to be populated.

Don't be alarmed when SEU or CODE/400 flags you with an error on the SQLTYPE keyword. For some reason, neither editor recognizes it (since V4R4!). Ignore the editor's error, as the program will compile.

One other thing to note is the use of the "locator" data type:

With the Clob_File's attributes set, we simply assign a locator to point to its contents with the following statement:

A locator is a pointer or "handle" to a large object. This allows a client to read through a resultset containing a large object column without necessarily having to retrieve all of the large object data. The locator allows the large object data to be retrieved only when the client application needs it.

Here are a few other tidbits:

• For older OS/400 versions, you may have to change the Set Option's Commit value from *NONE to *CHG.
• The function will not return files larger than 1 MB.
• CCSID conversions will be performed automatically (but don't attempt to read a binary file; use a BLOB for that).
• The CLOB_FILE data type can also be used to write a file to the IFS. SQL will give you programmatic access to read and write IFS files!

For practical use, I've been using the FileToClob function as an easy way to search for data in a large number of XML files in the IFS. Qshell provides a mechanism for doing this type of search as well, but I prefer the tight programmatic integration that SQL offers, even if it does mean extra overhead. This SQL interface also allows client/server and Web applications to access IFS data without having to open up NetServer to potentially unsecured situations.

For another use, FileToClob can read source members using the /QSYS.LIB file system. I recently needed to find all CL programs on a system that used the RUNQRY command without specifying the QRYFILE parameter. To do this, I dumped a list of source members to an outfile called Qtemp/MbrList using the "DSPFD lib/file *MBRLIST" command. I then used FileToClob to hunt through each source member:

In Part 2 of this series, I'll illustrate a table function that can read a delimited text file and return each row as a row in the result set.

References
FileToClob is a simple re-engineering of a UDF called FileToBlob, which served the purpose of reading binary data rather than text data. For more information on this related UDF, see "Return of the Blob."

"Invasion of the Blobs" contains an intro to BLOBs and has VBA code illustrating how to work with large object data using ActiveX Data Objects.

For more information on large objects, see SQL Programming Concepts and SQL Host Language Programming.

Code for this article can be downloaded here.