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 table. Table opening and closing is handled automatically by LANSA.
1. In the FETCH.Click event routine, add a FETCH command to retrieve the xDepartmentCode and xDepartmentDescription fields from the xDepartments table with a key of xDepartmentCode.
Your code should appear as follows:
Evtroutine Handling(#FETCH.Click)
Fetch Fields(#xDepartmentCode #xDepartmentDescription) From_File(xDepartments) With_Key(#xDepartmentCode) Issue_Msg(*YES)
Endroutine
Reminder: You can use F1 to display the online help for more details about each command.
2. Compile and execute the form.
a. Enter a department code, for example 100, 200 or 1000 and press the Fetch button.
The record for department 1000 is read from the database table and the result displayed on the form. Note that your code does need to populate the visual field component values, 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. The default is ISSUE_MSG(*NO). In your own applications you will usually not use this option and add your own error handling.
Note: Department Code is a 4 long alphanumeric field. The Department table has code values such as 100, 200, 300, 400 and 1000, 1100 and 1200. A later exercise shows how the Department Code field could be replaced with a combo-box containing all values loaded from the Department's table.
See Dynamic Visualizations in the Visual LANSA Developer's Guide.
3. Leave the Department Code blank and click Fetch. An automatic message will be issued.
As noted, in practice you would probably avoid issuing this rather unfriendly message for your own application.
4. LANSA's I/O commands return a return code as the parameter IO_STATUS(*STATUS). This syntax means that the returned value is placed in field IO$STS. Of course, this field is already defined in the Repository. Possible values for the return codes are given in this table:
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. See exercise FRM055A - List Component Basics, for an example using the SELECT command.
5. If the record is not found, an appropriate error message should be displayed to the user. Review the use of the IF_STATUS command to check that the FETCH was successful.
Add an IF_STATUS command and a MESSAGE command which issues an error message if the I/O Status is not OK. The IF_STATUS tests the value of field IO$STS (an alphanumeric, 2 field), which is returned by all I/O commands.
Your finished code should appear as follows:
Evtroutine Handling(#FETCH.Click)
Fetch Fields(#xDepartmentCode #xDepartmentDescription) From_File(xDepartments) With_Key(#xDepartmentCode)
If_Status Is_Not(*OKAY)
Message Msgtxt('Error retrieving Department')
Else
#xDepartmentCode.Enabled #FETCH.Enabled #INSERT.Enabled := False
#UPDATE.Enabled #DELETE.Enabled := True
Endif
Endroutine
The FETCH command has a default parameter of Not_Found(*next), which means the IF_STATUS following the FETCH will be executed when a record is not found.
Note: The Visual LANSA editor displays a $ or £ character for the same ASCII value, depending on code page selected during installation. Field IO$STS or IO£STS are the same field.
6. 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 1000 and press the Fetch button.
The record with a code of 1000 is read from the database and the result displayed on the form.
7. Close the form.
