Retrieve file's Column Headings using API

In May I posted two examples of how to retrieve the Column Headings for fields in files to be used as the column headings in a CSV file in the IFS. The first example, Adding Column Headings to a file in the IFS, I used DSPFFD. I was sent an alternative method that I gave in a later post, Adding Column Headings to a file in the IFS, continued, which used SQL to extract the information from the SYSCOLUMNS table.

Shortly after the second post I received a message from David Gomes. In it he explained a third method:

Whenever there is an API available to get the data, you should be using that and not running a CL command to produce a output file to read. In this situation I would recommend using the List Fields API (QUSLFLD). This will allow you to retrieve the Field name, Column or Text descriptions for the field. Using the X-Ref tables like SYSCOLUMNS is useless if the file is in QTEMP.

As I have explained how to use a List API, like QUSLFLD, that outputs to a User Space in the post User Space introduction in this post I am just going to explain how to use QUSLFLD.

The Header Section and the List Data section are what I need to address for the QUSLFLD API.

The Header section is constant for this API, no matter which format list you decide to use. In this example I only care for a few of the fields in the Header:

1. Offset: The number of bytes away from the start of the User Space the list starts.
2. Count: Number of list items.
3. Size: How many bytes long each list item is.

Below is the free format version, followed by the fixed format.

29 dcl-ds ListHeader based(UserSpacePointer) qualified ; 30 Offset int(10) pos(125) ; 31 Count int(10) pos(133) ; 32 Size int(10) pos(137) ; 33 end-ds ; 29 D ListHeader DS based(UserSpacePointer) 30 D qualified 31 D Offset 10I 0 overlay(ListHeader:125) 32 D Count 10I 0 overlay(ListHeader:133) 33 D Size 10I 0 overlay(ListHeader:137)

The Column Heading fields are in the FLDL0100 format so I need to code the data structure that will receive the list data to include the field name (not really necessary but I like having it there for testing) and the Column Heading fields. The data structure below is where I define the variables I want to extract from the list item:

34 dcl-ds FieldInfo based(FieldPointer) qualified ; 35 Name char(10) pos(1) ; 36 ColHdg1 char(20) pos(153) ; 37 ColHdg2 char(20) pos(173) ; 38 ColHdg3 char(20) pos(193) ; 39 end-ds ; 34 D FieldInfo DS based(FieldPointer) 35 D qualified 36 D Name 10A overlay(FieldInfo:1) 37 D ColHdg1 20A overlay(FieldInfo:153) 38 D ColHdg2 20A overlay(FieldInfo:173) 39 D ColHdg3 20A overlay(FieldInfo:193)

The only part I want to explain in the part of the code that builds the Header row field, HeaderRow. I loop through the list items retrieving its values using the pointer FieldPointer to the FileInfo data structure. I then trim and concatenate the column heading fields together to make one field, that I finally trim and concatenate into the HeaderRow field. See below:

46 for i = 1 to ListHeader.Count ; 47 if (HeadingRow <> ' ') ; 48 HeadingRow = %trimr(HeadingRow) + ',' ; 49 endif ; 50 FieldPointer = UserSpacePointer + ListHeader.Offset + (ListHeader.Size * (i - 1)) ; 51 ColumnHeadings = %trim(FieldInfo.ColHdg1) + ' ' + %trim(FieldInfo.ColHdg2) + ' ' + %trim(FieldInfo.ColHdg3) ; 52 HeadingRow = %trimr(HeadingRow) + '"' + %trimr(ColumnHeadings) + '"' ; 53 endfor ;

The entire program looks like below, if you need to be reminded of what the User Space handling APIs do refer to the User Space introduction post.

01 dcl-pr CrtUserSpace extpgm('QUSCRTUS') ; 02 *n char(20) const ; // Name 03 *n char(10) const ; // Attribute 04 *n int(10) const ; // Initial size 05 *n char(1) const ; // Initial value 06 *n char(10) const ; // Authority 07 *n char(50) const ; // Text 08 *n char(10) const options(*nopass) ; // Replace existing 09 *n char(32767) options(*varsize:*nopass) ; // Error feedback 10 end-pr ; 11 dcl-pr GetPointer extpgm('QUSPTRUS') ; 12 *n char(20) const ; // Name 13 *n pointer ; // Pointer to user space 14 *n char(32767) options(*varsize:*nopass) ; // Error feedback 15 end-pr ; 16 dcl-pr DltUserSpace extpgm('QUSDLTUS') ; 17 *n char(20) const ; // Name 18 *n char(32767) options(*varsize:*nopass) ; // Error feedback 19 end-pr ; 20 /copy qsysinc/qrpglesrc,qusec 21 dcl-pr ListFields extpgm('QUSLFLD') ; 22 *n char(20) const ; // User space name 23 *n char(8) const ; // Format 24 *n char(20) const ; // File name 25 *n char(10) const ; // Record format 26 *n char(1) const ; // Use override 27 *n char(32767) options(*varsize:*nopass) ; // Error feedback 28 end-pr ; 29 dcl-ds ListHeader based(UserSpacePointer) qualified ; 30 Offset int(10) pos(125) ; 31 Count int(10) pos(133) ; 32 Size int(10) pos(137) ; 33 end-ds ; 34 dcl-ds FieldInfo based(FieldPointer) qualified ; 35 Name char(10) pos(1) ; 36 ColHdg1 char(20) pos(153) ; 37 ColHdg2 char(20) pos(173) ; 38 ColHdg3 char(20) pos(193) ; 39 end-ds ; 40 dcl-s i int(3) ; 41 dcl-s HeadingRow char(5000) ; 42 dcl-s ColumnHeadings char(62) ; 43 CrtUserSpace('@USRSPACE QTEMP':'':131072:x'00': '*ALL':'List of fields in file':'*YES':QUSEC) ; 44 ListFields('@USRSPACE QTEMP':'FLDL0100':'TESTFILE *LIBL': 'TESTFILER':'0':QUSEC) ; 45 GetPointer('@USRSPACE QTEMP':UserSpacePointer) ; 46 for i = 1 to ListHeader.Count ; 47 if (HeadingRow <> ' ') ; 48 HeadingRow = %trimr(HeadingRow) + ',' ; 49 endif ; 50 FieldPointer = UserSpacePointer + ListHeader.Offset + (ListHeader.Size * (i - 1)) ; 51 ColumnHeadings = %trim(FieldInfo.ColHdg1) + ' ' + %trim(FieldInfo.ColHdg2) + ' ' + %trim(FieldInfo.ColHdg3) ; 52 HeadingRow = %trimr(HeadingRow) + '"' + %trimr(ColumnHeadings) + '"' ; 53 endfor ; 54 DltUserSpace('@USRSPACE QTEMP':QUSEC) ;

The equivalent of the definitions in fixed format is:

01 D CrtUserSpace PR extpgm('QUSCRTUS') 02 D 20A const 03 D 10A const 04 D 10I 0 const 05 D 1A const 06 D 10A const 07 D 50A const 08 D 10A const options(*nopass) 09 D 32767A options(*varsize:*nopass) 11 D GetPointer PR extpgm('QUSPTRUS') 12 D 20A const 13 D * 14 D 32767A options(*varsize:*nopass) 16 D DltUserSpace PR extpgm('QUSDLTUS') 17 D 20A const 18 D 32767A options(*varsize:*nopass) 20 /copy qsysinc/qrpglesrc,qusec 21 D ListFields PR extpgm('QUSLFLD') 22 D 20A const 23 D 8A const 24 D 20A const 25 D 10A const 26 D 1A const 27 D 32767A options(*varsize:*nopass) 29 D ListHeader DS based(UserSpacePointer) 30 D qualified 31 D Offset 10I 0 overlay(ListHeader:125) 32 D Count 10I 0 overlay(ListHeader:133) 33 D Size 10I 0 overlay(ListHeader:137) 34 D FieldInfo DS based(FieldPointer) 35 D qualified 36 D Name 10A overlay(FieldInfo:1) 37 D ColHdg1 20A overlay(FieldInfo:153) 38 D ColHdg2 20A overlay(FieldInfo:173) 39 D ColHdg3 20A overlay(FieldInfo:193) 40 D i S 3I 0 41 D HeadingRow S 5000A 42 D ColumnHeadings S 62A

 

You can learn more about the QUSLFLD API from the IBM web site here

 

This article was written for IBM i 7.1.