Wednesday, August 13, 2014

Retrieve file's Column Headings using API

quslfld

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.

6 comments:

  1. ஹரிஹரசுதன் செல்வராஜ்August 13, 2014 at 1:25 PM

    Thanks for your all replies guys.. Appreciate this help. !!

    ReplyDelete
  2. Using the system table SYSCOLUMNS is just not complicated enough !!

    ReplyDelete
  3. Using the SysColumns is pretty cool, if you have the authority to read the file. If speed is not required, the old DSPFFD works. Using the API: QUSLFLD is better as stated. I just ran into authority problem using the system table syscolumns for the run user, which does NOT have special authorities.

    Because I had some many authority issues with SysColumns, I would use the API: QUSLFLD, just less hassle, and very fast!

    ReplyDelete
  4. The API is the best way to retrieve this info. If you use the System Tables there are potential authority issues like Ken mentioned also THEY DO NOT TRACK ANYTHING IN QTEMP. I find it humorous that people keep recommending using them. Furthermore people need to stop recommending calling the CL commands for these type of problems are start using the appropriate API's.

    ReplyDelete
  5. Simon, great post. What type of file are you retrieving information from?

    ReplyDelete
    Replies
    1. From a physical file in QTEMP, as I described in my earlier post.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.