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.
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:
- 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.
- Number of key fields – this file has two key fields.
- Record format with the key – by using *N I am telling the command to use the only member in the file.
- 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.
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
ReplyDeleteInstead of overriding files, we create quite often a SQL view which returns the desired result with RUNSQL and loop through this view after.
ReplyDeleteHi,
ReplyDeletei 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.
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.
DeleteDepending 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...so, I guess it's just whatever character x'4F' represents in your codepage. For US this is |, for Europe mostly !
Deletetry using *CAT or *TCAT instead of pipes.
DeleteSorry Barb, I hate *CAT and *TCAT. I much prefer the double pipes as they are quicker to type and take up less room.
DeleteI 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.
Hey, is there a way to declare a sql generated view?
ReplyDeleteI 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
I have found my error. In the view there was an date field and cl does not know dates (converted it to char). greets
ReplyDeleteCan we read a keyed file sequentially in CL program?
ReplyDeleteTry using OVRDBF first to override the file to sequential.
Delete