Back in October 1983, I published the first issue of Q38--The Newsletter for Programmers of the IBM System/38. In that issue, I included a program that simply printed the field descriptions of an existing, compiled database file; it spanned more than five pages. Today, this kind of tool can be just as complex to write, or it can be relatively easy.
This article isn't about printing a list of field descriptions; it's about using some of the OS/400 APIs to create more complex applications. For example, I've had people ask me how to convert a database file's fields to comma-separated values so they can be written to a file on the IFS. You can do this for an entire file by running the CPYTOSTMF or CPYTOIMPF commands. But you can't really do it on a record-by-record or field-by-field basis. You'd have to use a custom routine to do that.
And then, there are the two questions I get every month: "How do I calculate the day of the week?" and "How do I retrieve a system value in RPG?" The answer to the first question is simple: Call the CEEDYWK API, and let it do it for you. The answer to the system value question is a little more complex; you have to call the QWCRSVAL API and then, depending on the type of system value, extract it correctly from the returned data structure.
There are several of these types of requirements that I receive regularly from my readership and colleagues. I have published many of them, including a day-of-week routine and, more recently, a Find/Replace routine.
But to continue to publish generic routines that do one simply task will become tedious and perhaps a bit boring. To create new routines that do more complex and interesting tasks would require that I incorporate some of those previously published routines. This could lead to very large articles, or if I simply refer back to the old articles, it might leave some readers with without a frame of reference. In addition, I may want to illustrate routines that take advantage of tools (RPG add-ons or extensions) that cannot be published as source code due to copyright issues.
Beginning with this issue, I may incorporate third-party add-on tools and/or products into the code featured in this newsletter. When I include a third-party component, I will provide a link to a Web site (if available) where the add-on tool can be acquired. Primarily, I intend to use my own library of tools called the RPG ToolKit for OS/400, which was announced on January 6, 2003, but I will use others, if available.
The RPG ToolKit for OS/400 is a service program that can be easily snapped into your new or existing code and leveraged without fanfare. In fact, typically all you need to do is include a BNDDIR('TOOLKIT') keyword on your Header specification, and a /COPY statement in your Definition specifications to be able to use any of the add-on tools in this service program.
I will continue to publish stand-alone routines or utilities as appropriate, but I will leverage these kinds of add-on tools as well.
List File Field Description
Often, it is necessary to view the description of a database file. It may be that you need a field's attributes for a program you're writing or to simply verify that the file matches the DDS. OS/400 includes the DSPFFD command that will display the field's attributes. However, I've never really liked the output from DSPFFD.
Many years ago, I wrote a utility called List File Field Description (LSTFFD), which printed the description of the fields within a database file. I've decided to update that program in this issue and make it considerably shorter.
There are three techniques you can use to create the LSTFFD tool:
- Generate and read the output from DSPFFD OUTPUT(*OUTFILE)
- Create a user space by calling QUSCRTUS and then generate the list of fields by calling the QUSLFLD API
- Use the RPG ToolKit to generate the list of fields
The first option is the one I used in the original version of this utility, and it was pretty cool at the time. Using an output file--or outfile as it's called--isn't something I like to do today, however. While there is nothing wrong with outfiles, they seem to require the most clock time to finish.
The second option is probably the most efficient, especially if the user space already exists. Dumping a list of field descriptions for a file into a user space is very fast and relatively easy to do. The difficulty, however, arrives when you try to extract the field definitions from the user space. Bouncing around a list of offsets to get to each field is, well, not a common practice in general-purpose business programming.
The third technique is the easiest if the RPG Toolkit for OS/400 is installed on your system. If it is not, you'll need to use the first or second technique.
To take advantage of the outfile technique, simply issue the DSPFFD command, specifying the file you want to output, and then specify the outfile name along with the OUTPUT(*OUTFILE) parameter. The command redirects the output to the specified file. Then, in the RPG program, simply read the file and write the field descriptions.
To simplify the example, I've created a short database file named TESTDDS that contains several fields. Essentially, I've included one field for each traditional data type. Once compiled, the object TESTDDS is created and may be used as a test file for the LSTFFD program. This allows you to verify that the output from the utility is correct. The DDS for the TESTDDS file is listed in Figure 1.
A R FILES
A CHARFIELD 12A TEXT('CHAR FIELD')
A VARFIELD 30 VARLEN(10) TEXT('VARYING CHAR(30)')
A PKDFIELD 7P 2 TEXT('PACKED FIELD') DFT(123.45)
A ZNDFIELD 7S 0 TEXT('ZONED FIELD')
A BINFIELD 9B 0 TEXT('BIN4 (9B0) FIELD')
A DTEFIELD L DATFMT(*ISO) TEXT('ISO DATE')
A TIMFIELD T TIMFMT(*USA) TEXT('USA TIME')
A MDYFIELD L DATFMT(*MDY) TEXT('MDY DATE')
A TMPFIELD Z TEXT('TIMESTAMP FIELD')
Figure 1: DDS source for the TESTDDS file
The output from the LSTFFD program includes the name, data type, length, and text description for each field in the file. The input file name and library are also listed. A sample of the output from the LSTFFD program is listed in Figure 2. Note that the TESTDDS file's field text descriptions identify the data type and lengths of each field. While this is not a recommended practice, it does lend itself well to testing the LSTFFD command.
File: testdds
Library: newsletter
Field Name Type Length Dec Text
CHARFIELD Char 12 CHAR FIELD
VARFIELD VChar 30 VARYING CHAR(30)
PKDFIELD Pkd 7 2 PACKED FIELD
ZNDFIELD Znd 7 0 ZONED FIELD
BINFIELD Bin 9 0 BIN4 (9B0) FIELD
DTEFIELD Date 10 ISO DATE
TIMFIELD Time 8 USA TIME
MDYFIELD Date 8 MDY DATE
TMPFIELD Dts 26 TIMESTAMP FIELD
Figure 2: Sample output from LSTFFD
Listed in Figure 3 is the source code for the LSTFFD program. It may be entered and compiled verbatim, provided the RPG ToolKit for OS/400 (aka ToolKit) is installed on your system (visit www.rpgiv.com/toolkit for more information).
The first Header specification contains the BNDDIR keyword. It identifies the binding directory needed to successfully create the program.
The three /COPY statements include external source code that is needed to compile the program. The first /COPY includes the prototypes for the CrtFldList, InList, and GetNextField procedures that are part of the ToolKit. The second /COPY includes the API error data structure used by the ToolKit. This data structure is imported from the ToolKit service program and is checked after the call to the CrtFldList procedure. If an error occurs during CrtFldList, the CPF message ID is returned in the TK_ERRMSGID subfield of the error data structure. The third /COPY includes the QUSLFLD source member from the QSYSINC library. This source member contains the QUSL0100 data structure. This data structure is used when extracting a file's field descriptions from a user space, when those fields have been generated by the QUSLFLD API. The GetNextField procedure also returns the field descriptions used in this same data structure. This provides a high level of compatibility with the QUSLFLD API.
H BNDDIR('TOOLKIT/TOOLKIT')
H DFTACTGRP(*NO)
.....FFileName++IFEASFRlen+LKeylnKFDevice+.Functions++++++++++
FQPRINT O F 132 PRINTER OFLIND(*INOF)
XX /COPY TOOLKIT/QCPYSRC,LISTS
XX /COPY TOOLKIT/QCPYSRC,RTKERROR
/COPY QSYSINC/QRPGLESRC,QUSLFLD
.....D*ame+++++++++++EUDS.......Length+TDc.Functions++++++++++
D LSTFFD PR
D szFile 10A
D szLib 10A
D LSTFFD PI
D szFile 10A
D szLib 10A
.....D*ame+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++++++++++
D szMyUs C Const('FIELDLIST QTEMP')
D ShortType S 1A DIM(7) CTDATA PERRCD(7)
D FullType S 4A DIM(7) CTDATA PERRCD(7)
D FldName S Like(QUSFN02)
D DataType S 5A
D nLength S Like(QUSFLB)
D szDecPos S 2A
D szText S Like(QUSFTD)
D nRef S 10I 0
D i S 5I 0
D nFldCnt S 5I 0
.....C..n01..............OpCode(ex)Extended-factor2++++++++++++++++++++++++++
C eval *INLR = *ON
** Create the user space and enumerate the list of fields
** for the TESTDDS file. If 0 is returned, the function failed.
XX C If CrtFldList(szMyUS : szFile : szLib) <= 0
** We can check TK_ERRMSGID at this point to see what the error is.
XX C TK_ERRMSGID DSPLY
C return
C endif
** Retrieve the first field's descritpion from the list.
XX C eval nRef = GetNextField(szMyUS:nRef:QUSL0100)
C if nRef > 0
C Except Heading
C endif
** Stay in loop until end-of -list is detected.
C DoW nRef > 0
** Copy the values from the couriously named IBM fields to normal names.
C eval FldName = QUSFN02
** If the data type is numeric use the digits value, otherwise
** use the "buffer length" value.
XX C if InList(QUSDT: 'P':'S':'B')
C eval nLength = QUSIGITS
C evalr szDecPos = %CHAR(QUSDP)
C else
C eval szDecPos = *BLANKS
C eval nLength = QUSFLB
C if QUSVLFI = *ON
** If the data-type is Char and VARLEN is specified,
** decrease the buffer length by the 2-byte binary prefix
** that is added for variable length fields.
C eval nLength = nLength - 2
C endif
C endif
C eval i = 1
C QUSDT LOOKUP ShortType(I) 88
C if %EQUAL
C eval DataType = FULLTYPE(I)
C if DataType = 'Char'
C and QUSVLFI = *ON
C Eval DataType ='VChar'
C endif
C endif
C eval szText = QUSFTD
** At this point, we can do anything we want with the data,
** in this example, it is simply printed.
C Except FieldDesc
** Get the next field's description from the user space
XX C eval nRef = GetNextField(szMyUS:nRef:QUSL0100)
C enddo
.....OFormat++++DAddn01n02n03Except++++SpbSpaSkbSka.Constant++++++++++++++++++
OQPRINT E Heading 1
O +9 'File Field Descriptions'
OQPRINT E Heading 1
O +2 'File:'
O szFile +1
OQPRINT E Heading 2
O +2 ' Library:'
O szLib + 1
OQPRINT E Heading 1
O +2 'Field Name'
O +2 'Type'
O +4 'Length'
O +1 'Dec'
O +1 'Text'
OQPRINT E FieldDesc
O FldName +2
O DataType +2
O nLength Z +0
O szDecPos + 1
O szText +2
**CTDATA SHORTTYPE
PSBAZLT
**CTDATA FULLTYPE
Pkd Znd Bin CharDts DateTime
Figure 3: The LSTFFD source member
Within the source member, positions 1 and 2 of a few statements contain the letters XX. These lines are highlighted because they contain calls to procedures or references to the ToolKit.
The first procedure used is CrtFldList. It creates a list of fields in a user space for the file specified. If an error occurs, such as the file not existing, the procedure returns a value of 0; otherwise, it returns a count of the number of fields listed in the user space.
The next procedure that's used is GetNextField, which retrieves a field description from the user space into the QUSL0100 data structure. The procedure uses a reference index to control which field is retrieved. The procedure updates the QUSL0100 structure and returns a new reference index that may be used on a subsequent call to GetNextField. This allows you to iterate through the list of fields rather easily.
The only other ToolKit procedure used is the InList procedure. This procedure allows a single value to be compared to a list of values. This allows you to avoid extensive conditional statements when a field, such as the data type field in this example, is compared to a long list of values.
The LSTFFD program is simple, but it illustrates the power of using an add-on library like the ToolKit. Whether you license the RPG ToolKit or another third-party library or build a library yourself, having such a library can help reduce the complexities of programming complicated tasks and greatly improve programmer productivity.
The ToolKit used in this article is but one of many interesting low-cost or no-cost toolkits available for AS/400 and iSeries. Future articles may take advantage of some of their features. Here's a list of just a few:
TAA Productivity Tools--$750 to $7,500, depending on which version/release of OS/400 you are running and which processor group you have. You may already have a license to this product as it was previously named QUSRTOOLS and was free with OS/400. IBM stopped supporting it, and Jim Sloan (the author of the tools) started his own company. Now, this is one of the best-selling products (in terms of number of customers) of any third-party AS/400 software product.
iSeries Toolkit--Free. This is probably the most robust and complete free toolkit on the Web.
RPG ToolKit for OS/400--$295 (until May 3, 2003). This new package contains a service program that extends RPG IV with several add-on procedures.
Craig Rutledge's Open Source AS/400 Tools--Free. This toolkit is a fairly large collection of commands and utilities for the AS/400 and iSeries.
Database Generation Utilities (DBG/400)--Free. DBG/400 is a collection of tools and commands that help to set up a test environment with any required files and data areas and then populate those files with all, or just a subset, of realistic data from an existing source.
Function Junction--$895. This package is a large collection of productive procedures that are incorporated into a service program. They extend RPG IV with several features and functions.
LATEST COMMENTS
MC Press Online