Step 2 Fetch Existing Data from a File

Step 2. Fetch Existing Data from a File

FRM035 - Maintain a Simple Database Table

In this step you will use the FETCH command to retrieve a single record from the database. You should notice that there are no OPEN or CLOSE statements required for the file. File opening and closing is handled automatically by LANSA.

1.  In the FETCH.Click event routine, add a FETCH command to retrieve the DEPTMENT and DEPTDESC fields from the DEPTAB file. For this first example, you want to FETCH the record where DEPTMENT='ADM'.

     Your code should appear as follows:

EVTROUTINE HANDLING(#FETCH.Click)
FETCH FIELDS(#DEPTMENT #DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY('ADM') ISSUE_MSG(*YES)
ENDROUTINE

     Reminder: You can use F1 to display the online help for more details about commands.

2.  Compile and execute the form.

a.  Press the Fetch button.

     The record for department ADM is read from the database and the result displayed on the form. If for some reason this record does not exist, a message is automatically issued due to the ISSUE_MSG(*YES) parameter on the FETCH command . In your own applications you will usually want to remove this option and add your own error handling.

3.  Typically, you want to allow the user to enter a Department Code for the record to be read. The value that the user has entered for the DEPTMENT field on the form will be used to fetch the record.

     If the record is not found, appropriate error messages must be displayed to the user. Review the use of the IF_STATUS command to check that the FETCH was successful.

     I/O Command Return Codes Table

* An attempted INSERT with a duplicate key will return VE.

# A SELECT command using a WHERE parameter will select each record and test for the condition. When the last record is selected, the processing will leave the SELECT loop with the data from the last record selected. This record may not have met the WHERE condition.

     Your finished code should appear as follows:

EVTROUTINE HANDLING(#FETCH.Click)
FETCH FIELDS(#DEPTMENT #DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT)
IF_STATUS IS_NOT(*OKAY)
MESSAGE MSGTXT('Error retrieving Department')
ENDIF
ENDROUTINE

4.  Compile and execute the form.

a.  Leave the Department Code blank and press the Fetch button.

     The error message will be displayed.

b.  Enter a Department Code of ADM and press the Fetch button.

     The ADM record is read from the database and the result displayed on the form.

5.  Close the form.