Traditionally, us RPG programmers have written rather laborious RPG code using IBM *API's to read data from the IFS. A couple of years ago, IBM released a service pack adding these wonderful SQL Functions which will let us RPG Hippies easily read IFS entries, just as easily as we read database files.
A stream file does not contain rows; it is a continuous flow of data. Carriage returns and line feeds will prompt a PC viewer to display each field on a new line.
IBM i SQL Table functions?
IBM i SQL gives us three main SQL/IFS Table functions that return IFS data in various formats:
In 99% of cases I suspect you will be using IFS_READ. However, each function has the same input parameters, so they are super easy to use, and switch between if needs be.
PATH_NAME: The path and file name are essential components, as they are required to locate and read the file.
MAXIMUM_LINE_LENGTH: The maximum number of characters per line in an IFS file is optional, but must exceed zero if declared.
END_OF_LINE: The character that signifies the end of a line is known as the newline character, often represented as '\n' in several programming languages, but for IBM this value can be:
CR - Carriage return
LF - Line feed
CRLF - Carriage return Line Feed
LFCR - Line Feed Carriage Return
NONE - No end of line character, in this case the end of line will be denoted by the MAXIMUM_LINE_LENGTH parameter.
I love working with SQL and hate it in equal measures 😉
But, one of the things I really like is the ability to test SQL statements before we even load them into our RPG code. For example, we could use this table function to simply return the value of an existing IFS file. Let's look at an example of retrieving the contents of a simple IFS file I have stored at /home/nicklitten/myfile.txt
Now we can use the other parameters to tweak the data that is returned. For example, if you just wanted the first 10 characters of the data you could use the MAXIMUM_LINE_LENGTH value:
from table (
QSYS2.IFS_READ(
PATH_NAME => '/home/nicklitten/myfile.txt',
MAXIMUM_LINE_LENGTH => 10)
)
Pretty simple right?
Simple RPG SQL Program using IFS_READ
Below is an example of an SQLRPGLE program that reads data from an IFS file using the IFS_READ SQL function:
/title Simple RPG program to read the IFS using SQL
// ----------------------------------------------------------
//
// Service - READIFS.RPGLE
//
// Function - Simple RPG program to READ the IFS using SQL
//
// COMPILE NOTES:
//
// Obviously change the source location to match yours:
//
// CRTSQLRPGI OBJ(NICKLITTEN/READIFS)
// SRCSTMF('/home/nicklitten/source/READIFS.sqlrpgle')
// COMMIT(*NONE)
// OBJTYPE(*PGM)
// DBGVIEW(*SOURCE)
// CVTCCSID(*JOB)
//
// Modification History:
// 2020-06-31 V1.0 Created by Nick Litten
// ----------------------------------------------------------
ctl-opt
main(READIFS)
option(*srcstmt:*nodebugio:*noshowcpy)
/if Defined(*CRTSQLRPGI)
dftactgrp(*no) actgrp('NICKLITTEN')
/endIf
copyright('READIFS.SQLRPGLE: Version 1.0 June 2020');
dcl-proc READIFS;
dcl-pi READIFS end-pi;
// Status Message string for humans
dcl-s stsMsg char(50);
// define variables for the IFS file and a variable for each row of data returned
dcl-s ifsFile varchar(255) inz('/home/nicklitten/myfile.txt');
dcl-s ifsData varchar(1024);
monitor;
exec sql
declare c1 cursor for
select *
from table(qsys2.ifs_read(:ifsFile));
exec sql open c1;
// Keep reading until end of file (or an error occurs)
dou sqlCode <> 0;
exec sql fetch c1 into :ifsData;
Select;
when sqlcode = 0;
// + --------------------------------------------- +
// + This is where you would add some logic to do +
// + something with the value of 'ifsData' +
// + --------------------------------------------- +
stsMsg = 'Completed normally - read next row';
when sqlcode = 100;
stsMsg = 'No data found';
leave;
when sqlcode > 0;
stsMsg = 'Completed with warning';
leave;
when sqlcode < 0;
stsMsg = 'Did not complete normally';
leave;
endsl;
enddo;
exec sql close c1;
on-error ;
dump(a);
dsply ('*** READIFS has failed!');
endmon ;
return;
end-proc;