![using sql to write to a text ifs file](https://3.bp.blogspot.com/-7BAIbHv5GG8/XeLbDmH1sNI/AAAAAAAALaI/RsDOfbHxW0kcD5Bgo_E4EmasSyi5x2OwACPcBGAYYCw/s200/sql.png)
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:
- IFSFILE_NAME path to the file in the IFS
- IFSFILE_NL length of the IFS path
- 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.
What does the Procedure Interface have to do with the availability of global variables in the procedure?
ReplyDeleteNo need to have a procedure interface in an "open" subprocedure, see here.
DeleteShouldn't line 10 be: IfsFile_NL = %len(Path) ; since Path is the VARCHAR variable? Otherwise, great article Simon.
ReplyDeleteYes, you are right.
DeleteThis comment has been removed by a blog administrator.
ReplyDeleteThe 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.
ReplyDeleteThis 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"This Post"-Link is not working. Can you repeat which Post ? Thx
DeleteThat comment is over 2 years old. I am guessing that it was to this post here.
DeleteSo, I've been trying to use this as part of a routine to create debug logs on the IFS but my problem is that no matter what I try in the code the IFS file is always CCSID 37. dcl-s IfsFile sqltype(clob_file) CCSID(1252); Unless I precreate the file with the correct CCSID it isn't usable. I'm on V7R2 (for a few more weeks) then i'll try it again on V7R4, but i would love to know if i'm missing something.
DeleteThere are a set of prototypes in QSYSINC that make it easy to just use the standard IFS functions from RPG. Just /copy qsysinc/qrpglesrc,ifs and you can write to IFS using something like:
ReplyDeletefname = '/home/rdiedrich/ifsxmp.txt';
fd = open(fname:
O_WRONLY + O_CREAT + O_CCSID + O_TEXT_CREAT + O_TEXTDATA:
0:819:0);
line = 'Rich Diedrich';
rc = write(fd:%addr(line:*data):%len(line));
rc = close(fd);
A full program looks like this:
**free
ctl-opt main(main) dftactgrp(*no);
/copy qsysinc/qrpglesrc,ifs // Very useful set of IFS function prototypes
dcl-proc main;
dcl-pi main extpgm('IFSXMP');
end-pi;
dcl-s fname varchar(100);
dcl-s fd int(10);
dcl-s rc int(10);
dcl-s line varchar(100);
dcl-s rbuffer char(50);
dcl-s rlength int(10);
dcl-s errno int(10) based(errnoptr);
fname = '/home/rdiedrich/ifsxmp.txt';
// Open file for writing, create it if needed, translate the data to ASCII
fd = open(fname:
O_WRONLY + O_CREAT + O_CCSID + O_TEXT_CREAT + O_TEXTDATA:
0:819:0);
if fd < 0; // Use errno to determine the specific error
errnoptr = getErrnoPtr();
dsply errno;
endif;
// Write the line
line = 'Rich Diedrich';
rc = write(fd:%addr(line:*data):%len(line));
// Close the file
rc = close(fd);
// Open the file for reading, translate the data
fd = open(fname:O_RDONLY + O_TEXTDATA);
if fd < 0;
errnoptr = getErrnoPtr();
dsply errno;
endif;
// Read the data
rlength = read(fd:%addr(rbuffer):%size(rbuffer));
// Display it
if rlength > 0;
dsply %subst(rbuffer:1:rlength);
endif;
// Close the file
rc = close(fd);
end-proc;