You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > RPG

Open Access IFS Handler Revisited


We once wrote an article describing an RPG Open Access (OA) Handler designed to simplify the production of Character Separated Variable (CSV) files that avoided the need to use the CPYTOIMPF command or to code directly to the C I/O functions. If you’re not familiar with writing OA handlers, read "Getting a Handle on RPG’s Open Access" before continuing.

Recently a reader sought advice on how to extend the functionality of that handler to include the ability to generate an optional column-heading row. We decided that this enhanced utility could help many readers, so today we’ll go beyond the original article which described the basic process, and only discuss enhancements that handle the column-heading requirement.

Sourcing the Column Headings

The system already provides column-heading support for reports etc. via the DDS keyword COLHDG and its SQL equivalent LABEL ON, so we’ll use that rather than invent our own system. The advantage: this information can be retrieved from the SYSCOLUMNS table in QSYS2. We also decided to offer two other column-heading alternatives: the basic 10-character field name; and the full SQL column name (which is the same as the ALIAS name in DDS).

We first must modify the IFS information passed from the user program to the handler. Originally this simply contained the full path name for the file, but we now added a field to indicate the type of column heading required. The four options are F(ield name), H(eading), and N(ame) and a space if no column heading row is required. If any other character is used, the result is no heading row. The resulting template for the DS looks like this:

     D ifs_hdlr_info_t...
     D                 ds                  qualified template
     D   headingType                  1a
     D   path                      1024a   varying

We also need a change to facilitate headings retrieval. So we added the keyword EXTFILE to file definitions in the program that use the handler. Why? If more than one copy of the file being used to define the IFS output exists, then we need to know its library name when querying the SYSCOLUMNS table in order to retrieve the correct headings.

Normally if an RPG program needs this information it retrieves it from the INFDS⎯but there's a problem with that. That information is normally set by the system routines responsible for opening a file, but our OA handler is replacing that functionality and actually is responsible for setting the library name into the INFDS! All it’ll normally receive from RPG is the file name and *LIBL for the library name. We could resolve the library list in the handler and determine exactly where the file resides, but we think it’d be simpler to have the programmer simply specify exactly which file to use via the EXTFILE keyword since this information is passed to the handler. We will discuss an alternate approach in a later iDevelop blog post.

Below you’ll see an extract from a test program that uses the new handler, and you’ll see the keyword in action. For the full version of the source, download the complete source package from our web site.

    // IFS output file - definition specifies fields to output

  FIFS_OUT1  o    e             Disk    Handler('OAHND_IFS2' :
  F                                              ifs_info1)
  F                                     UsrOpn 
  F                                     ExtFile('PARTNER400/IFS_OUT1')
   ...

    // include definition of the IFS parameter template
   /copy ExtraSrc,ifs_cpy2

  D ifs_info1       ds                  likeds(ifs_hdlr_info_t)

    ifs_info1.headingType = 'H'; // Select column  headings
 
    ifs_info1.path = '/Partner400/TestHandler4a.csv';

 

Changes to the Handler

The first change required was in the logic that handles the opening of the file. We added logic to validate the heading type and to set to the default of "no heading" in the event that the code was not recognized. Notice that the heading type selected is stored in the file's state information along with the file handle.

         // Set heading type from input request and set into state
         //   info for file

         If ( fileInfo.headingType = Field ) or
            ( fileInfo.headingType = Heading ) or
            ( fileInfo.headingType = Name );
            state.headingType = fileInfo.headingType;
         Else;
            state.headingType = None; // Default to no heading row
         EndIf;

The next change is in the write routine and requires a little more explanation. First we defined the DS array column_names (A) to contain the column heading information. This will be populated by SQL from the SYSCOLUMNS table and searched via a %LOOKUP operation using the field name passed to us by RPG OA. In addition to the basic field name (B), we have also specified the full column name (C), and the column heading (D). Here's the definition:

       // Used to map field name (system_Column_Name) to heading
       //   Array populated during file open and used by write ops.

(A)  D column_names    ds                  Qualified Dim(100)
(B)  D   system_Column_Name...
     D                               10a
(C)  D   column_Name                128a
(D)  D   column_Heading...
     D                               60a  

The write logic was first modified to check if column heading information should be retrieved from SYSCOLUMNS (E) and, if so, builds the required SQL statement (F) using the file and library names supplied by RPG, prepares the statement, assigns a cursor, and then executes the statement (G). Finally the data is fetched into the column_names array (H). Note that we arbitrarily assigned a maximum size of 100 elements to the array. If you are going to be producing CSVs that require more than 100 columns you will need to change the array definition and the SQL fetch operation.

     // Load column heading information for file from SYSCOLUMNS
     //   Note: Not required if column heading type = F(ield)
     //         as we will already have the field name
(E) If ( fileInfo.headingType <> None ) and
       ( fileInfo.headingType <> Field ) ;

(F)    sqlText = 'select system_column_name, column_name, +
                     column_heading +
                     from qsys2/syscolumns where table_schema = '
                     + apost + %Trim(info.externalFile.library ) 
                     + apost + ' and table_name = '
                     + apost + %Trim(info.externalFile.name ) + apost
                     + ' order by system_column_name';

(G)    exec sql Prepare getColumnInfo from :sqlText;

       exec sql Declare columnData Cursor for getColumnInfo;

       exec sql open columnData;

(H)    exec sql Fetch next from columnData
                       for 100 rows
                       into :column_Names;

       exec sql close columnData;

    EndIf;

The additional logic to build and output the headings was added to the FOR loop that processes each of the individual fields in turn.

We begin by copying the field name supplied by RPG into a temporary area (fieldName) to avoid having to use the qualified array element name externalName repeatedly (I). Then at (J) we then check if column headings are required and if so check if the field name is to be used (K). If it is, then the trimmed name is simply copied into the columnName field. In all other cases we need one of the columns retrieved by the SQL we saw earlier, so we use %LOOKUP to search the column_names array (L). The location in the array is then used to set the heading from either the column_Heading or column_Name. When the column_Name is used, any underscores in the name are converted to spaces via the %SCANRPL BIF.

Once the value of the column heading has been determined then it is enclosed in quotation marks and added to the buffer (M).

At the end of the section, as with the field value processing, a comma is added to the buffer following each heading entry, except the last, and the record terminated by a carriage return/line feed combination.

Once the record has actually been written (O), the only thing left to do is to set a flag to signal that heading output is complete. Since we already have a "no headings required" option, the simplest thing was to allow that to perform double duty as the flag. So it’s set to the "no heading" status at the end of the process (P).

And that’s it.

   // Process all fields in record

   For i = 1 to nvInput.num;

    // Copy current name for later use
(I) fieldName = nvInput.field(i).externalName;
(J) If fileInfo.headingType <> None;

(K)    If fileInfo.headingType = Field;
          columnName = %TrimR(fieldName); // Use field name
   
       Else;

(L)       f = %Lookup( fieldName: column_names(*).system_Column_Name);

          If fileInfo.headingType = Heading;
             columnName =  %TrimR(column_names(f).column_Heading);
          ElseIf fileInfo.headingType = Name; 
             // Replace underscores in name

             columnName = %TrimR( %ScanRpl('_': ' ':
                                  column_names(f).column_Name));
          EndIf;

       EndIf;

(M)    headingBuffer += quote + columnName + quote;

(N)    If i <> nvInput.num;
          headingBuffer += comma;
       Else;
          headingBuffer += CRLF;
(O)       reply = write ( fileInfo.fileHandle
                        : %Addr(headingBuffer:*Data)
                        : %Len(headingBuffer) );

          // Set headings to none as they have been done
(P)       fileInfo.headingType = None; 
       EndIf;
    EndIf;

Additional Thoughts

As written, this code should not be considered quite "production ready". For example the return code from the IFS write operations is not being checked, and neither is SQLSTATE during the heading text retrieval process. This was done to simplify the code for explanatory purposes.

Only you can decide how critical these omissions are for your installation and how your code should react to them. There is certainly a possibility of the SQL failing, for example if the EXTFILE keyword was omitted from the user program or if it were specified but an incorrect library name supplied. Both of these situations would likely either cause a failure in the SQL or result in a zero index being returned by the subsequent %LOOKUP operation.

When deciding how to implement error checks in the SQL part of the code, we highly recommend the approach described by our friend Paul Tuohy in his article on "Embedded SQL Exception/Error Handling" which makes things much simpler and more "RPG like".

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters