Pages

Wednesday, November 25, 2020

Writing to an IFS file with SQL

ifs_write ifs_write_utf8 ifs_write_binary to write content to an ifs file

This post continues yesterday's theme of doing things to files in the IFS with SQL. Then it was how to read an IFS file, today will be the opposite writing to an IFS file.

This was added in the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.4 TR3. Prior to these TRs I had used C APIs embedded in a RPG program. Having played with this this is so much easier using these new SQL procedures.

As with the IFS read table function there are three different SQL procedures to write to an IFS file:

  1. IFS_WRITE:  Write plain text to the IFS file
  2. IFS_WRITE_UTF8:  Write UTF8 text
  3. IFS_WRITE_BINARY:  Write binary text

All of these procedure have the same parameters:

  1. PATH_NAME:  Path name to the file that will be written to. It is not case sensitive
  2. LINE:  Data to be written to the file
  3. FILE_CCSID:  CCSID used when writing to a file. If I am writing to an existing file this is ignored. If IFS_WRITE_UTF8 is used the CCSID will default to 1208, for the others it will be the partition's default
  4. OVERWRITE:  Three values:
    • APPEND  If the file in the path name exists add to the end of the file
    • NONE  If the file already exists nothing happens
    • REPLACE  An existing file is replaced, deleted and a new one created
  5. END_OF_LINE:  Is not supported if IFS_WRITE_BINARY is used. The others will accept:
    • CR  Carriage return
    • CRLF  Carriage return and line feed
    • LF  Line feed
    • LFCR  Line feed and carriage return
    • NONE  No end of line character(s)

I am going to start by using the file I was reading yesterday: test.txt. When I use the IFS_READ table function it shows the file's contents:

SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyFolder/test.txt'))


LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth line

Let me write a line to that file:

CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                     'Added line 1',
                     OVERWRITE => 'APPEND')

I read the file to see what happened.

LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth lineAdded line 1

The line was appended to the file, but not as a new line, just as a continuation of the last line.

I deleted the file test.txt, and uploaded saved copy from my PC to the IFS folder. Let me try to write again this time with the end of line character CRLF.

CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                     'Added line 2',
                     OVERWRITE => 'APPEND',
                     END_OF_LINE => 'CRLF')

Alas, the same happened to the file as before. The line was appended to the end of the last line:

LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth lineAdded line 2

This must mean that the fifth line does not end with a character that IFS_WRITE understands as an end of line character. So I need to be a bit creative. Let me write a line to the file with just a CRLF character, and then write the line I want to add to the file. Again I replace the file with my saved copy.

01  CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                         '',
                         OVERWRITE => 'APPEND',
                         END_OF_LINE => 'CRLF') ;

02  CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                         'Added line 3',
                         OVERWRITE => 'APPEND',
                         END_OF_LINE => 'CRLF') ;

In the first statement I am writing a null value to the file, which is two apostrophes next to each other ( '' ).

Now I get the results I want, as the first statement just added a CRLF to the fifth line:

LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth line
          6  Added line 3

Providing I have the end of line parameter in my statement I can keep adding line to my file.

CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                     'Added line 4',
                     END_OF_LINE => 'CRLF')

The overwrite parameter is optional, its default is to append a new line to the file. Which gives me:

LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth line
          6  Added line 3
          7  Added line 4

This time I am going to use the IFS_WRITE_UTF8 procedure.

CALL QSYS2.IFS_WRITE_UTF8('/home/MyFolder/test.txt',
                          'Added line UTF8',
                          END_OF_LINE => 'CRLF')

I am still going to use the IFS_READ table function to display the file's contents.

LINE NUMBER  LINE
-----------  -------------
          1  First line
          2  Second line
          3  Third line
          4  Fourth line
          5  Fifth line
          6  Added line 3
          7  Added line 4
          8  Added line UTF8

As the file was created as a plain text file, not as UTF8, the line is added with the file's CCSID which results in a plain text line.

What would happen if I used the IFS_WRITE_BINARY procedure?

CALL QSYS2.IFS_WRITE_BINARY('/home/MyFolder/test.txt',
                            'Added line binary',
                            END_OF_LINE => 'CRLF')

I get an error:

SQL State: 07006 
Vendor Code: -99999 
Message: Data type mismatch. (class java.lang.NumberFormatException)

I am not worried about this as I cannot think of a scenario where I would need to write a binary string to a plain text file.

As I mentioned previously if I wanted to replace the contents of the existing file I would just use REPLACE in the overwrite parameter.

CALL QSYS2.IFS_WRITE('/home/MyFolder/test.txt',
                     'Replace line',
                     OVERWRITE => 'REPLACE',
                     END_OF_LINE => 'CRLF')

The existing file is deleted and replaced by new one which contains the new line.

LINE NUMBER  LINE
-----------  -------------
          1  Replace line

If I have a file name that does not exist in the folder given in the path name this procedure will create it.

CALL QSYS2.IFS_WRITE('/home/MyFolder/new_test.txt',
                     'New file and a new line',
                     END_OF_LINE => 'CRLF')

I can use the IFS_OBJECT_STATISTICS table function to confirm that the file was created:

SELECT PATH_NAME,OBJECT_TYPE,CREATE_TIMESTAMP
  FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS('/home/MyFolder'))


PATH_NAME                    OBJECT_TYPE
-------------------------    -----------
/home/MyFolder               *DIR
/home/MyFolder/new_test.txt  *STMF
/home/MyFolder/test.txt      *STMF

And see the contents of the new file using the IFS_READ:

SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyFolder/new_test.txt'))


LINE NUMBER  LINE
-----------  -----------------------
          1  New file and a new line

Have to say I like this as it this so much easier than using the C APIs called from a RPG program.

 

You can learn more about the IFS_WRITE SQL procedures from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

18 comments:

  1. OMG! I wish I had these a few years ago. Would have made a project so much easier!

    ReplyDelete
  2. Hi,
    first at all thank you for the blog. Help me a lot.

    I also played arround with IFS_READ/WRITE.
    When using FILE_CCSID (e.g. 1208) it has same effect like END_OF_LINE => 'CRLF'. A new line will be created when OVERWRITE=APPEND

    ReplyDelete
  3. Very cool Simon!

    ReplyDelete
  4. Good to know and I am glad to see these enhancements.

    ReplyDelete
  5. Amazing! Thank you so much for this

    ReplyDelete
  6. cool Simon, yeah really handy and lot shorter than using C or IFS api.

    ReplyDelete
  7. Only one note for those, like me, want to use this simple way for write IFS in a RPG Program.

    When you use a variable for the PATH of the document and a variable for the data to write in the document, be sure to define both like a VARCHAR variable.

    If not and the variable lenght is greater that data contained, you have a problem to read the file.

    EXAMPLE :

    dcl-s doc_data varchar(1000);
    dcl-s doc_path varchar(100);

    // Write in IFS
    exec sql
    CALL QSYS2.IFS_WRITE(PATH_NAME => :doc_path,
    LINE => doc_data,
    END_OF_LINE => 'CRLF',
    OVERWRITE => 'APPEND',
    FILE_CCSID => '819');

    P.S. For Simon

    Me too i'm using only TOTAL FREE from when it's released

    ReplyDelete
  8. Hi Simon
    This is really great to see. Could you please clarify how this SQL procedure compares to commands like CPYFRMIMPF?

    ReplyDelete
    Replies
    1. The only way I can answer that is: it depends.

      Some of it is personal preference. It could the be standards of where you work. It could be the IBM i security settings.

      Personally this will not replace me created CSV using the CPYTOIMPF command.

      Delete
  9. I knew something like this would happen, were still on 7.3! ..on stand by

    ReplyDelete
    Replies
    1. If you get the latest PTFs, especially the database ones, for 7.3 you will get this too.

      Delete
  10. Great stuff, I was thinking of a way to create/write the IFS file a batch of records, let's say a select that writes the data in CSV. I now I can use a cursor adn loop it appending lines, just curious if I could do it in 1 step?

    ReplyDelete
  11. Hi Simon, how can I read a phisical file and for each records, use the IFS_WRITE Funtion to write the file? Best Regards.

    ReplyDelete
    Replies
    1. There is a future post that describes how to do this.

      Delete
  12. Hello, nice Post. I need help with someting.... i Cant use variable in path_name=> :variable1

    variable is varchar(34)

    But gives me error...

    Sorry for bad English.

    ReplyDelete
    Replies
    1. It worked for me when I used a variable in the path name:

      dcl-s PathName varchar(100) ;

      PathName = '/home/MyFolder/test_1.txt' ;

      exec sql CALL QSYS2.IFS_WRITE(:PathName,'Test line',
      END_OF_LINE => 'CRLF') ;

      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.