Writing to an IFS file with SQL

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.