Pages

Tuesday, August 20, 2024

Using file name from data area for reading IFS file

The idea of this post came from a question I was asked. The questioner had a scenario where the name of an IFS file they needed read is in a data structure. They were unable to work out how to use the value from the data structure as the path name to read of the IFS file.

Let me start using ACS's "Run SQL Scripts", RSS, tool.

Before I can read a file in the IFS I need to create it. I can use the IFS_WRITE SQL procedure to do so:

01  CALL QSYS2.IFS_WRITE('/home/MyDirectory/test.txt',
02                       'First line',           
03                       END_OF_LINE => 'CRLF')

Line 1: Call the IFS_WRITE SQL procedure. Passing to it three parameters first is the file path.

Line 2: Second parameter is the data that will be written to the IFS file.

Line 3: Finally, I need to give what I want the end of line character to be. In this case it is carriage return and line feed.

I can check that the file was created where I expected using the IFS_OBJECT_STATISTICS table function:

01  SELECT PATH_NAME
02    FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS
03                  ('/home/RPGPGM/','NO','*STMF'))

Line 1: I only want the path (file) name in my results.

Line 3: I am passing three parameters to the IFS_OBJECT_STATISTICS: directory name, I want no results from any subdirectories, and only stream files.

My result is:

PATH_NAME
-----------------------------
/home/MyDirectory/test.txt

I know the file exists, what about its contents? I use the IFS_READ table function to read the file:

01  SELECT *
02    FROM TABLE(QSYS2.IFS_READ(
03           '/home/MyDirectory/test.txt',
04           MAXIMUM_LINE_LENGTH => 50))

Line 3: This is the path of the file I want to read.

Line 4: The maximum record length of this file is 50 characters, which is bigger than the string I wrote to the file but that does not cause an problem.

The results show that I what I expected was written to the file.

LINE_NUMBER  LINE
-----------  --------------------
          1  First line

I now need a data area, which I created with the following CL statement:

01  CRTDTAARA DTAARA(MYLIB/TESTDA) 
02              TYPE(*CHAR) LEN(100) 
03              VALUE('home/MyDirectory/test.txt')

I can retrieve the contents of the data structure using the DATA_AREA_INFO table function:

01  SELECT DATA_AREA_VALUE
02    FROM TABLE(QSYS2.DATA_AREA_INFO
03                  ('TESTDA','MYLIB'))

The result confirms that I have the information I desire in the data area:

DATA_AREA_VALUE
--------------------------------
/home/MyDirectory/test.txt

I need to thank Christian for the code below. He corrected my original SQL statement to retrieve the value from the data area and use it as the path name in IFS_READ:

01  SELECT *
02    FROM TABLE(QSYS2.IFS_READ(
03           (SELECT TRIM(DATA_AREA_VALUE)
04              FROM TABLE(QSYS2.DATA_AREA_INFO
05                  ('TESTDA','MYLIB'))),
06           MAXIMUM_LINE_LENGTH => 50))

Line 3: The trim of the data area column is essential for the path name to be valid.

The result from the amended statement is:

LINE_NUMBER    LINE
------------   -------------
1	First line

Let me switch from RSS to RPG, and explain how I can take the above statements and accomplished this scenario.

Here is the code for my RPG program:

01  **free
02  dcl-s Line varchar(50) ;

    // Create/write file in IFS
03  exec sql CALL QSYS2.IFS_WRITE('/home/MyDirectory/test.txt',
04                                'First line',
05                                END_OF_LINE => 'CRLF') ;

    // Read file in IFS
06  exec sql SELECT LINE INTO :Line
07             FROM TABLE(QSYS2.IFS_READ(
08                          (SELECT TRIM(DATA_AREA_VALUE)
09                             FROM TABLE(QSYS2.DATA_AREA_INFO
10                               ('TESTDA','RPGPGM1'))),
11                          MAXIMUM_LINE_LENGTH => 50))
12             LIMIT 1 ;

13   dsply SQLCOD ;
14   dsply Line ;

15   *inlr = *on ;

Line 2: I am defining the variable that will be used to retrieve the contents of the IFS file.

Lines 3 – 5: I am creating the file in the IFS, as I did above at the beginning of this post.

Lines 6 – 12: This is almost the same as the IFS_READ statement below. The only difference is on line 12.

Line 12: As the IFS_READ could return more than one row, I need to limit the results to one.

Line 13: If the IFS_READ failed for any reason the SQL code will not be zero.

Line 14: I want to display the value I retrieved from the IFS file.

After compiling the program I then called it, and the following was displayed:

DSPLY          0 
DSPLY  First line

The first DSPLY is zero, which shows that there were no issues with the IFS_READ.

The second is the contents of the file in the IFS.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

7 comments:

  1. You can read in SQL using the following SQL:

    01 SELECT *
    02 FROM TABLE(QSYS2.IFS_READ(
    03 (SELECT TRIM(DATA_AREA_VALUE)
    04 FROM TABLE(QSYS2.DATA_AREA_INFO
    05 ('TESTDA','MYLIB'))),
    06 MAXIMUM_LINE_LENGTH => 50))

    As Scott Forstie would say: "SQL can do it!" :-)

    Best regards,
    Christian

    ReplyDelete
    Replies
    1. Thank you for this. I have made your recommended change to the post.

      Delete
  2. Hi Simon Please kindly explain why attempting as example with following command from article : SELECT *
    02 FROM TABLE(QSYS2.IFS_READ(
    03 '/home/MyDirectory/test.txt',
    04 MAXIMUM_LINE_LENGTH => 50)) - I get the *POINTER value instead of character contents ? How does it possible to perform CASTing for IFS file ?

    ReplyDelete
    Replies
    1. My guess is that you are using STRSQL, rather than ACS's "Run SQL Scripts".
      If that is true then you need to stop STRSQL and move to "Run SQL Scripts". STRSQL is an old tool that is missing things that "Run SQL Scripts" offers.

      Delete
  3. You could just cast the data area value:
    SELECT *
    02 FROM TABLE(QSYS2.IFS_READ(
    03+ TRIM(SELECT cast(DATA_AREA_VALUE as varchar(50))
    04 FROM TABLE(QSYS2.DATA_AREA_INFO
    05 ('TESTDA','MYLIB'))),
    06 MAXIMUM_LINE_LENGTH => 50))

    ReplyDelete
  4. Hello, when I run this sentence: SELECT DATA_AREA_VALUE FROM TABLE(QSYS2.DATA_AREA_INFO
    ('TESTDA'')). (Without library because I cannot set the library inside the program, the program will be moving to the client server and I don’t know the name of the library) this query takes a long time. Can I use RTVDTAARA inside the SQLRPGLE program? I was trying, but the variable comes blank. Any idea to get the information from the data area?

    ReplyDelete
    Replies
    1. Put '*LIBL' in the second parameter ands see if that makes a difference.

      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.