Using CL to get records from a file for display

I have been receiving a lot of messages asking for more examples using the CL programming language. For those of you who have been asking I hope you find this useful.

A few weeks ago I received a request for help from a reader, just a beginner in the IBM i world, with an assignment he had to accomplish just using CL. He needed to write a program, or programs, that would allow the entry an employee id, and the program would retrieve information from the employee file and pass it to the display file for display. For anyone with a little RPG experience this is a very simple task, and we could all write a RPG program to do this in a few minutes. Many IBM i programmers balk to do anything like this in CL, but programming is programming, and the same steps you would perform in RPG can be duplicated in CL.

The steps the program would need to do are:

1. Present display file and allow entry of Employee Id.
2. Fetch the record from the file.
3. If the record is fetched pass the data to the display file.
4. If there is no matching record present an error message needs to be displayed on the display file.

I have covered all of the steps necessary to accomplish assignment in previous posts, therefore, this will be pulling all of that information together into a practical example.

First I need an Employee file, EMPLOYEE, and yes part of the requirement was to build a DDS file and not a DDL table. I think if I just give the DDS for the file you will all know everything you need to know about it.

01 A R EMPLOYEER 02 A EMPLOYEEID 7P 0 COLHDG('Employee' 'id') 03 A FIRSTNAME 30A COLHDG('First' 'name') 04 A LASTNAME R REFFLD(FIRSTNAME *SRC) 05 A COLHDG('Last' 'name') 06 A ADDRESS1 30A COLHDG('Address' 'line 1') 07 A ADDRESS2 R REFFLD(ADDRESS1 *SRC) 08 A COLHDG('Address' 'line 2') 09 A K EMPLOYEEID

This file contains three records. Why only three? I am lazy and I know what works for three records will also work for a million. So why take the time to create more records that I need.

Emp First Last Address Address id name name line 1 line 2 1 JOHN SMITH 15 HIGH ST LOS ANGELES, CA 91300 2 VANESSA HERMOSA 10215 SEASIDE BLVD HUNTINGTON BEACH, CA 92300 4 THOMAS CLARKE 755 SUNSET BLVD HOLLYWOOD, CA 92309

The display file I created is also very simple. I don't need anything fancy, just one entry field for the Employee Id, and two output fields for the Name and Address.

01 A DSPSIZ(24 80 *DS3) 02 A CA03(03 'F3=EXIT') 03 A REF(EMPLOYEE) 04 A R SCREEN 05 A 4 3'Employee id . . .' 06 A EMPLOYEEIDR Y B 4 21 07 A 50 ERRMSG('Id not found') 08 A 5 3'Name . :' 09 A NAME 40 O 5 13 10 A 6 3'Address :' 11 A ADDRESS 40 O 6 13 12 A 23 3'F3=Exit'

When compiled the display file's record format looks like:

Employee id . . . 9999999 Name . : OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO Address : OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO F3=Exit

I am sure you have noticed that the file has two fields for the Name and Address while the display file has only one for each. I'll need to do some concatenation to put the data together the way I want.

Now I can get to the CL program.

01 PGM 02 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 03 DCL VAR(&SELECT) TYPE(*CHAR) LEN(30) 04 DCLF FILE(TESTDSPF) 05 DCLF FILE(EMPLOYEE) OPNID(A) 06 DOWHILE COND(&LOOP) 07 SNDRCVF 08 IF COND(&IN03) THEN(LEAVE) 09 CHGVAR VAR(&IN50) VALUE('0') 10 CHGVAR VAR(&NAME) VALUE(' ') 11 CHGVAR VAR(&ADDRESS) VALUE(' ') 12 SNDF 13 IF COND(&EMPLOYEEID = 0) THEN(DO) 14 CHGVAR VAR(&IN50) VALUE('1') 15 ITERATE 16 ENDDO 17 CHGVAR VAR(&SELECT) + VALUE('EMPLOYEEID = ' || %CHAR(&EMPLOYEEID)) 18 OVRDBF FILE(EMPLOYEE) OVRSCOPE(*CALLLVL) SHARE(*YES) 19 OPNQRYF FILE((EMPLOYEE)) QRYSLT(&SELECT) 20 RCVF OPNID(A) 21 MONMSG MSGID(CPF0864) + EXEC(CHGVAR VAR(&IN50) VALUE('1')) 22 IF COND(*NOT &IN50) THEN(DO) 23 CHGVAR VAR(&NAME) VALUE(&A_FIRSTNAME |> &A_LASTNAME) 24 CHGVAR VAR(&ADDRESS) VALUE(&A_ADDRESS1 |> &A_ADDRESS2) 25 ENDDO 26 CLOSE OPNID(A) 27 RCLRSC 28 ENDDO 29 ENDPGM

Line 2: This variable will be used to condition my Do loop.

Line 3: I will be needing to build a query statement for the OPNQRYF, and this is the variable that will contain it.

Line 4: Declaration for my display file.

Line 5: Declaration for the Employee file. I have defined an Open Id, OPNID, too. All of the file's fields will be prefixed by A_, and when I come to close this file I will need to give the OPNID so that the program will know which file to close.

Line 6: Start of my Do-loop, which ends on line 28. This Do-loop is conditioned by the logical variable (think indicator) defined on line 2. The Do will continue to loop until the logical variable becomes false, '0', or I use the LEAVE command.

Line 7: In CL I use the SNDRCVF command to send the display file's contents, and then receive the data from it when Enter is pressed. As the display file contains only one record format, and this is the only file defined in this program I do not have to give the name of the record format.

Line 8: When the user presses F3, &IN03, I will leave the Do-loop.

Lines 9 – 12: When there is an error the display file's fields are not cleared. The Employee Id field is in error, while the other fields contain the data from the previous successfully fetched data. Personally I hate this as I have found it can be confusing. Here I clear the display file's output fields, and then use the SNDF to send them to the screen. The user does not see anything when this is done. If a bad Employee Id was entered the Name and Address fields on the display file will be blank.

Lines 13 – 16: If an Employee Id was not entered, or zero was, then this is an invalid Employee Id. I don't need to perform any other validation I can just set on the error indicator, line 14, and iterate, line 15, to return to the "top" of the Do-loop.

Line 17: I need to make the selection criteria to find the record I desire. The test needs to be when the file's Employee Id is equal to the Employee Id that was entered in the display file. To concatenate the display file's Employee Id to the string I need to first convert the value to character. If your version IBM i is older than this built in function you will have to do this instead.

DCL VAR(&NBR) TYPE(*CHAR) LEN(7) CHGVAR VAR(&NBR) VALUE(&EMPLOYEEID) CHGVAR VAR(&SELECT) VALUE('EMPLOYEEID = ' || &NBR)

Line 18: If I am going to use OPNQRYF I need to share the open data path. I have also overridden at the call level, this means that the override is only used in this program and any others it may call.

Line 19: The OPNQRYF opens the file at the point closest to where the selection criteria matches, using the criteria in &SELECT . This is similar to using the SETLL operation code in RPG.

Line 20: The next record is retrieved (read) from the Employee file.

Line 21: The message CPF0864 is issued when end of file is encountered. The only time this can happen is if there is not a match to the query selection used in the OPNQRYF. In other words there is no record for the Employee Id that was entered in the display file. The error indicator is set on.

Lines 22: I prefer to use this syntax to check if an indicator variable is off.

Lines 23 and 24: I am using concatenation shortcuts to make the Name and Address fields for the display file. |> does the equivalent of a trim right on the first variable, and adds a space before concatenating the other variable. |< does a trim right, and concentrates the two variables together with no space.

Line 26: As Employee file has been opened I need to close it so that it can be opened after an end of file has been encountered, which is what happens when no matching record in found in the file. I need to use the CLOSE command which will allow me to reopen the file, the CLOF does not. I have to use the OPNID so that the command knows I want to close the Employee file.

Line 27: Everyone who programs knows the Reclaim Resource command, RCLRSC. I have inserted it to make sure everything has been closed and overrides cleared properly.

Line 28: End of the Do-loop, and I return to line 6.

After the program is compiled I can then call it, This is what happened when I used various Employee Ids.

Employee id . . . 0000001 Name . : JOHN SMITH Address : 15 HIGH ST, LOS ANGELES, CA 91300 ----- Employee id . . . 0000002 Name . : VANESSA HERMOSA Address : 10215 SEASIDE BLVD, HUNTINGTON BEACH, CA ----- Employee id . . . 0000003 Name . : Address : Id not found ----- Employee id . . . 0000004 Name . : THOMAS CLARKE Address : 755 SUNSET BLVD, HOLLYWOOD, CA 92309

 

Don't worry if I helped the person who asked the question to cheat by giving him the answer, I did not. I pointed him to various posts on this blog. You can find the same posts by using the search functions. And this will not influence his work as the deadline was October 1.

 

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