Tuesday, May 30, 2017

Read, write, and update a file in CL

equivalent of rpg read, write, update, setll, chain 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:

 

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

12 comments:

  1. another good example is an CL program that reads control file keyed by company number and passes company company number to program for processing instead of hard coding

    ReplyDelete
  2. Instead of overriding files, we create quite often a SQL view which returns the desired result with RUNSQL and loop through this view after.

    ReplyDelete
  3. Hi,
    i try to use the update function in a CLP. I follow your example using 3 ''' before the character || but i had this error message:
    String '|| &KEYFIE' contains a character that is not valid.
    String '|| ''' ') ' contains a character that is not valid.

    It seems that the IBMi editor do not like the character ||.
    Any suggestion?
    Thank you very much for your blog.
    Paolo.

    ReplyDelete
    Replies
    1. Formatting these strings can be tricky and a quote or space easily missed or misplaced. I Run the pgm in debug and copy the SQL statement to an interactive sql line. It should execute and if it doesn't will give you a better idea how to fix it.

      Delete
    2. Depending on the codepage of your emulation (Client Access or so) you might need to use a different charachter than |. In the environments I'm working we use codepage 500 and I have to use ! instead of |

      Delete
    3. ...so, I guess it's just whatever character x'4F' represents in your codepage. For US this is |, for Europe mostly !

      Delete
    4. try using *CAT or *TCAT instead of pipes.

      Delete
    5. Sorry Barb, I hate *CAT and *TCAT. I much prefer the double pipes as they are quicker to type and take up less room.

      I have no issue if people want to use *CAT and *TCAT, I will not.

      The pipes are also used for the same purpose in SQL too. Keeping things constant across programming languages is a good thing too.

      Delete
  4. Hey, is there a way to declare a sql generated view?
    I have created a view with current date, year, month, day, week_iso, dayname on the sysibm.sysdummy1 and i want to recieve this one record. The only thing i recieve is the CPF4131 error-message :-)
    Thx Chris

    ReplyDelete
  5. I have found my error. In the view there was an date field and cl does not know dates (converted it to char). greets

    ReplyDelete
  6. Can we read a keyed file sequentially in CL program?

    ReplyDelete
    Replies
    1. Try using OVRDBF first to override the file to sequential.

      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.