Read, write, and update a file in CL

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

1. Read equivalent
2. Set lower limits equivalent
3. Chain equivalent
4. Write equivalent
5. Update equivalent

 

Read equivalent

To be able to read a file in a CL program I have to use the Declare File command, DCLF, to define it. Then use the Receive File command, RCVF to retrieve data from the file. In its simplest form this could look like:

01 PGM 02 DCLF FILE(FILE1) 03 RCVF 04 ENDPGM

As there is only one file declared in the program I do not have to gives its name when using the RCVF command.

I rarely have a file and just read it once, usually I read it multiple times. I can do that using a Do loop (no more excuses for using the GOTO command).

01 PGM 02 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 03 DCLF FILE(FILE1) 04 DOWHILE COND(&LOOP) 05 RCVF 06 MONMSG MSGID(CPF0864) EXEC(LEAVE) 07 ENDDO 08 ENDPGM

What do I do if I have more than one file?

01 PGM 02 DCLF FILE(FILE1) OPNID(A) 03 DCLF FILE(FILE2) OPNID(B) 04 RCVF OPNID(A) 05 RCVF OPNID(B) 06 CHGVAR VAR(&A_FIELD1) VALUE(&A_FIELD1) 07 CHGVAR VAR(&B_FIELD1) VALUE(&B_FIELD1) 08 ENDPGM

Lines 2 and 3: As I have more than one file declared in this program I need to use the Open File Identifier, OPNID, parameter to give each file its own unique id. I just use a letter, but the OPNID can be up to ten character.

Lines 4 and 5: When I use the RCVF I have to tell it which file to use. The OPNID is used and must match the value in a file declaration.

Lines 6 and 7: When I use the OPNID the fields' name are automatically prefixed with the open identifier and an underscore ( _ ). This ensures that the field names are unique.

 

Set lower limits equivalent

If I want to position the file pointer to place in the file other than the start I would use the Override Database File command, OVRDBF, like this:

01 PGM 02 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 03 DCL VAR(&KEYFIELD) TYPE(*CHAR) LEN(10) 04 DCLF FILE(FILE1) 05 CHGVAR VAR(&KEYFIELD) VALUE('Value') 06 OVRDBF FILE(FILE1) + POSITION(*KEYAE 2 *N &KEYFIELD) + OVRSCOPE(*CALLLVL) 07 DOWHILE COND(&LOOP) 08 RCVF 09 MONMSG MSGID(CPF0864 CPF4137) EXEC(LEAVE) 10 ENDDO 11 ENDPGM

Line 6: I am overriding the declared file using the Starting Position In File parameter, POSITION, this will position the file pointer to that point in the file when I perform my first "read". The four parts of this parameter are:

1. Retrieve order - *KEYAE this means position the file pointer to either the exact match on the key or to the next record. If I had used *KEY I would have needed an exact match, including trailing spaces. I have always found it better/easier to use the *KEYAE.
2. Number of key fields – this file has two key fields.
3. Record format with the key – by using *N I am telling the command to use the only member in the file.
4. Key value – this can either be a variable, as I have shown, or you can enter a literal instead.

I have the RCVF within a Do loop so that this program will read all the records starting at the closest match to the value in the variable &KEYFIELD to the end of the file.

I can only use one key field in the OVRDBF command, no matter how many keys the file may have. What happens if I want to position to a key of two values, one 10 character the other 5,0 packed numeric?

01 PGM 02 DCL VAR(&DATA_STRCT) TYPE(*CHAR) LEN(13) 03 DCL VAR(&SUBFLD1) TYPE(*CHAR) STG(*DEFINED) + LEN(10) DEFVAR(&DATA_STRCT 1) 04 DCL VAR(&SUBFLD2) TYPE(*DEC) STG(*DEFINED) + LEN(5 0) DEFVAR(&DATA_STRCT 11) 05 DCLF FILE(FILE3) 06 CHGVAR VAR(&SUBFLD1) VALUE('VALUE') 07 CHGVAR VAR(&SUBFLD2) VALUE(2) 08 OVRDBF FILE(FILE3) + POSITION(*KEYAE 2 *N &DATA_STRCT) + OVRSCOPE(*CALLLVL) 09 RCVF 10 ENDPGM

Lines 2 – 4: If my two key fields were alphanumeric it would be easy to just concatenate them into one variable and use that as the key field. But I deliberately made this difficult for myself by having a mixed data type key. To create a key that contains both of the data types I define a CL data structure, the first subfield is the alphanumeric key field and the second the packed decimal key field.

If I look in the job log after calling this program I can the key field in the OVRDBF command:

0800 - OVRDBF FILE(TESTFILE2) POSITION(*KEYAE 2 *N X'E5C1D3E4C5404040404000002F') OVRSCOPE(*CALLLVL)

 

Chain equivalent

What is a Chain? Surely it is just the equivalent of a set lower limits followed by a read equal. In CL I do not have the equivalent of a read equal so I would have read and then a comparison. Which could look something like:

01 PGM 02 DCL VAR(&DATA_STRCT) TYPE(*CHAR) LEN(13) 03 DCL VAR(&SUBFLD1) TYPE(*CHAR) STG(*DEFINED) + LEN(10) DEFVAR(&DATA_STRCT 1) 04 DCL VAR(&SUBFLD2) TYPE(*DEC) STG(*DEFINED) + LEN(5 0) DEFVAR(&DATA_STRCT 11) 05 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 06 DCL VAR(&FOUND) TYPE(*LGL) 05 DCLF FILE(FILE3) 06 CHGVAR VAR(&SUBFLD1) VALUE('VALUE') 07 CHGVAR VAR(&SUBFLD2) VALUE(2) 08 OVRDBF FILE(FILE3) + POSITION(*KEYAE 2 *N &DATA_STRCT) + OVRSCOPE(*CALLLVL) 09 CHGVAR VAR(&FOUND) VALUE('1') 10 RCVF 11 MONMSG MSGID(CPF0864 CPF4137) + EXEC(CHGVAR VAR(&FOUND) VALUE('0')) 12 IF COND(&FOUND) THEN(+ 13 IF COND((&FIELD1 *NE &SUBFLD1) + 14 *OR (&FIELD2 *NE &SUBFLD2)) + 15 THEN(CHGVAR VAR(&FOUND) VALUE('0'))) /* If "CHAIN" successful then &FOUND = '1' */ 16 ENDPGM

This program is very similar to the last example except for:

Lines 5 and 6: These logical (indicator) variables will be used during the program for controlling the loop, &LOOP, and for indicating if a matching record was found.

Line 9: Let me set my record found logical variable to the equivalent of *on.

Line 11: If I received an error from my RCVF then I know I did not find my match and I set the logical variable to zero (= *off).

Lines 12 – 15: Here I have a if statement nested with in another if statement.

Line 12: If the found logical variable is *on then a record was retrieved from the file by the RCVF.

Line 13 - 15: If either of the two key fields, contained within data structure sub fields, are different to the two key fields from the retrieved record then the found logical variable is changed to zero, (= *off), on line 15.

Is it more complicated than RPG's Chain, yes, but it is possible to duplicate its functionality.

 

Write equivalent

There is no write command, but thanks to the Run SQL command I can use it to insert a record into a file.

01 PGM 02 DCL VAR(&VAR1) TYPE(*CHAR) LEN(10) 03 DCL VAR(&VAR2) TYPE(*DEC) LEN(5 0) 04 CHGVAR VAR(&VAR1) VALUE('SOMETHING') 05 CHGVAR VAR(&VAR2) VALUE(99) 06 RUNSQL SQL('INSERT INTO FILE3 (FIELD1,FIELD2) + VALUES (''' || &VAR1 || ''',' + || %CHAR(&VAR2) |< ')') + COMMIT(*NC) 07 ENDPGM

Line 4 and 5: These are the values I want to insert into my file.

Line 6: I use the SQL parameter to give the Insert statement with the values from the two program variables. As the first is alphanumeric it needs to have three apostrophes ( ' ) either side of it. The second variable is numeric, therefore it has be converted to an alphanumeric value to be used in the command parameter, which I do using CL %CHAR built in function.

Yes, it is that simple.

 

Update equivalent

It should come as no surprise to you that CL does not have a Update command too. Yet again I can overcome that shortcoming by using a Update statement in the RUNSQL command. In this example I want to "chain" the record from FILE1 and update the field TYPE:

01 PGM 02 DCL VAR(&KEYFIELD) TYPE(*CHAR) LEN(15) 03 DCLF FILE(FILE1) 04 CHGVAR VAR(&KEYFIELD) VALUE('ELEPHANT') 05 OVRDBF FILE(FILE1) + POSITION(*KEYAE 1 *N &KEYFIELD) + OVRSCOPE(*CALLLVL) 06 RCVF 07 IF COND(&ANIMAL = &KEYFIELD) THEN(+ 08 RUNSQL SQL('UPDATE FILE1 + 09 SET TYPE = ''MAMMAL'' + 10 WHERE ANIMAL = ''' || &KEYFIELD || ''' ') + 11 COMMIT(*NC)) 12 ENDPGM

Line 5: In this example FILE1 only has one key field, therefore the number of keys in the POSITION parameter is one.

Line 6: I retrieve the record from FILE1 that is the closest match to my key field.

Line 7: If the value in the file field &ANIMAL is the same as my key field value…

Lines 8 – 10: I execute a SQL Update to FILE1 changing the value of the field TYPE when the key field, ANIMAL is equal to the value in the key field variable.

Again this functionality is made simple by using SQL.

 

While doing some of this database file access in CL I realize it is more cumbersome than it would be if I have used an RPG program, but you can see that it is possible. The question becomes whether I should use CL file access in certain scenarios rather than RPG with a call to the QCMDEXC API?

 

In the next post I discuss using Open Query File command to do the same as some of these examples.

 

You can learn more about this from the IBM website:

• DCLF - Declare file command
• RCVF - Receive file command
• OVRDBF - Override database file command

 

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