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 SMALLINT) RETURNS BOOLEAN
02   DEFINE fetch_ok BOOLEAN
03             
04   LET fetch_ok = (SQLCA.SQLCODE == 0)
05   
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 02 defines a Boolean variable, fetch_ok, to serve as an indicator whether the FETCH was successful.
  • Line 04 assigns the value of fetch_ok to the value of SQLCA.SQLCODE.
  • 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.