Wednesday, August 1, 2018

Copying any data to and from a file in the IFS

using sql to write to a text ifs file

In previous posts I have shown how to write SQL data to a file in the IFS and then how to retrieve the XML data from an IFS file, but what about just plain old text?

In this post I am going to keep the example as simple as possible so that you can see how easy this is. I am going to write three strings of data, "records", to an IFS file, retrieve the data from the file, then move each the "record" into an array. Some of what I am describing here I have also mentioned in my earlier posts, but I think it is valuable to have it in here too.

My example program is only 32 lines, which goes to show easy this is. All the hard work is performed by Db2 for i.

All programs start with the definitions:

01  **free
02  ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no) ;

03  dcl-s IfsFile sqltype(clob_file) ;
04  dcl-s Path varchar(100) inz('/MyFolder/text.txt') ;
05  dcl-s String varchar(1024) ;
06  dcl-s Array char(40) dim(10) ;

07  exec sql SET OPTION COMMIT = *NONE, 
                        CLOSQLCSR = *ENDMOD ;

Line 1: I only ever use totally free format RPG these days.

Line 2: My favorite control options. The DFTACTGRP is a giveaway that the program uses a procedure.

Line 3: RPG does not support all of Db2 for i data types, if I need to use one of these data types I have to define it with the SQLTYPE keyword and the SQL precompiler translate it into a RPG data structure. The CLOB_FILE allows me to write directly to a file in the IFS. If I look in the compiler listing I can see how this variable has been "translated" into a RPG data structure.

//*DCL-S IFSFILE SQLTYPE(CLOB_FILE) ;
DCL-DS IFSFILE;
  IFSFILE_NL UNS(10);
  IFSFILE_DL UNS(10);
  IFSFILE_FO UNS(10);
  IFSFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS IFSFILE;

I only need to be concerned with three of the subfields:

  1. IFSFILE_NAME path to the file in the IFS
  2. IFSFILE_NL length of the IFS path
  3. IFSFILE_FO file operation

More about them when I use them later in the program.

Line 4: This variable contains the IFS path to the file I will be using. I have defined it is as a variable length character, but I could have defined it as a fixed length character variable.

Line 5: I have defined this variable as a miscellaneous character variable. There is nothing special about it.

Line 6: This array will contain the "records" I retrieve from the IFS file. I have defined the elements to be 40 long character, and there are 10 elements.

Line 7: I add this line to all my SQLRPGLE programs. These SQL options ensure that when the program is compiled I do not have to change the COMMIT and CLOSQLCSR parameters.

Let me write some data to the IFS file.

08  clear IfsFile ;
09  IfsFile_Name = %trimr(Path) ;
10  IfsFile_NL = %len(IfsFile_Name) ;

11  IfsFile_FO = SQFOVR ;  // Overwrite
12  exec sql SET :IfsFile = 'First record' ;

13  IfsFile_FO = SQFAPP ;  // Append
14  exec sql SET :IfsFile = 'Second record          ' ;
15  exec sql SET :IfsFile = 'Third record' ;

16  IfsFile_FO = SQFRD ;  // Read only
17  exec sql SET :String = :IfsFile ;

18  ExtractRecords() ;

19  *inlr = *on ;

Line 8: As RPG has converted the CLOB_FILE to a data structure I am clearing it before I use it.

Line 9: The data structure subfield IfsName_Name needs to contain the path name to where I want my file to be in the IFS.

Line 10: IfsFile_NL is the length of the path name. As I have defined the path variable to be variable length, see line 4, it is only as long as the length of the data within it. So I can just use the %LEN built in function to determine the length. If I had used a fixed character variable I would have to trim the variable before using the %LEN BiF.

Line 11: The SQL precompiler creates constants that correspond to the operation I want to perform on the file, see below. In this case I am going to use the overwrite value. If the file exists in the given path it will be overwritten, if it does not then a new file will be created.

DCL-C SQFRD CONST(2);
DCL-C SQFCRT CONST(8);
DCL-C SQFOVR CONST(16);
DCL-C SQFAPP CONST(32);

Line 13: As I want to add more "records" to the file I am going to change operation to append, add any subsequent data to the file.

Line 14: I "write" a second "record" to the file.

Line 15: And a third "record".

Line 16: Having "written" data to the file now I want to retrieve it. What I must do is change the file operation to read.

Line 17: When you retrieve data from a file in the IFS using CLOB_FILE it does not have distinct records. I need to retrieve the entire contents of the file into a single variable, String.

When I look at the data retrieved I can see a problem.

  ....5...10...15...20...25...30...35...40...45...50...55...60
'First recordSecond record          Third record             '

How do I determine where the "records" end?

I have always used a "end of record" marker when using files in the IFS. I always put an asterisk ( * ) at the end of record, as it is a character I never use within data. If I change following lines:

12  exec sql SET :IfsFile = 'First record*' ;


14  exec sql SET :IfsFile = 'Second record         *' ;
15  exec sql SET :IfsFile = 'Third record*' ;

When I retrieve the file's data into the variable String I can easily see the end of each "record".

  ....5...10...15...20...25...30...35...40...45...50...55...60
'First record*Second record         *Third record*           '

Now I can execute the procedure, ExtractRecords, to extract the "records" from String and load them into the array, Array defined on line 6.

20  dcl-proc ExtractRecords ;
21    dcl-s Counter uns(3) ;
22    dcl-s Start uns(5) inz(1) ;
23    dcl-s End like(Start) ;

24    for Counter = 1 to %elem(Array) ;
25      End = %scan('*':String:Start) ;
26      Array(Counter) = %subst(String:Start:(End-Start)+1) ;
27      if (End = %len(String)) ;
28        leave ;
29      endif ;
30      Start = End + 1 ;
31    endfor ;
32  end-proc ;

Line 20: All free format procedures start with a DCL-PROC. This is what I call an open procedure. As there is no procedure interface definition, DCL-PI, all of the variables defined in the main body of the program are available within the procedure.

Lines 21 – 23: The variables I define in a procedure remain local, only available within the procedure.

Line 24: I am using a For group to extract the individual "records" from String. This loop is performed the same number of times as number of elements in the array. The For group end on line 31.

Line 25: I am using the %SCAN BiF to look for the next asterisk. The first time the For group is performed the starting position for the scan, variable Scan is 1 and after that it is always one place greater than the where the asterisk was last found, line 30.

Line 26: Using the substring, %SUBST BiF, I extract the "record" from String into the next available element in the array.

Line 27 - 29: How can I tell if I have extracted the last "record" from String? As String is a variable length character variable it will only be as long as the data contained within. Therefore, if the end position of the last "record", End, is the same as the length of String there is no more data to check. And I want to leave the For group.

Line 30: If there is more data in String I want the next start position, Start, to be one place greater than the last asterisk, End.

After the procedure has been executed the array contains the three "records":

ARRAY(1) = 'First record*                           '
ARRAY(2) = 'Second record         *                 '
ARRAY(3) = 'Third record*                           '
ARRAY(4) = '                                        '

As you have seen this is a very easy method to add data and get data from an file in IFS with a minimum amount of programming.

 

This article was written for IBM i 7.3, and should work for releases later than 7.1 TR4.

7 comments:

  1. What does the Procedure Interface have to do with the availability of global variables in the procedure?

    ReplyDelete
    Replies
    1. No need to have a procedure interface in an "open" subprocedure, see here.

      Delete
  2. Shouldn't line 10 be: IfsFile_NL = %len(Path) ; since Path is the VARCHAR variable? Otherwise, great article Simon.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. The IFS file seems to be created with the system CCSID (e.g., 37). I'm used to seeing the CCSID as 819 or 1252 so I can open via PC program (Excel, Notepad). Can you specify the CCSID in the program before writing the file? I will usually use QShell cmd TOUCH to do that.

    ReplyDelete
    Replies
    1. This post gives an example of how to set the CCSID for the XML-CLOB variable. The same should be possible with a CLOB too.

      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.