Example: custquery.4gl (function fetch_cust)

This function is designed so that it can be reused each time a row is to be fetched from the customer database table; a variable is passed to indicate whether the cursor should move forward one row or backward one row.

Function fetch_cust:
01 FUNCTION fetch_cust(p_fetch_flag)
02   DEFINE p_fetch_flag SMALLINT,
03             fetch_ok SMALLINT
04
05   LET fetch_ok = FALSE
06   IF (p_fetch_flag = 1) THEN
07     FETCH NEXT cust_curs 
08       INTO mr_custrec.*
09   ELSE
10     FETCH PREVIOUS cust_curs 
11       INTO mr_custrec.*
12   END IF
13
14   IF (SQLCA.SQLCODE = NOTFOUND) THEN
15     LET fetch_ok = FALSE
16   ELSE
17     LET fetch_ok = TRUE
18   END IF
19
20   RETURN fetch_ok 
21
22 END FUNCTION
Note:
  • Line 01 The function fetch_cust accepts a parameter and stores it in the local variable p_fetch_flag.
  • Line 03 defines a variable, fetch_ok, to serve as an indicator whether the FETCH was successful.
  • Lines 06 thru 12 tests the value of p_fetch_flag, moving the cursor forward with FETCH NEXT if the value is 1, and backward with FETCH PREVIOUS if the value is -1. The values of the row in the customer database table are fetched into the program variables of the mr_custrec record. The INTO mr_custrec.* syntax requires that the program variables in the record mr_custrec are in the same order as the columns are listed in the SELECT statement.
  • Lines 14 thru 15 tests SQLCA.SQLCODE and sets the value of fetch_ok to FALSE if the fetch did not return a row. If the FETCH was successful, fetch_ok is set to TRUE.
  • Line 20 returns the value of fetch_ok to the calling function.
  • Line 22 is the end of the function fetch_cust.