Example custquery.4gl (function cust_select)

This function is called by the function query_cust, if the row count returned by the function get_cust_cnt indicates that the criteria previously entered by the user and stored in the variable where_clause would produce an SQL SELECT result set.

Function cust_select:
01 FUNCTION cust_select(p_where_clause) 
02   DEFINE p_where_clause STRING,
03         sql_text STRING,
04         fetch_ok SMALLINT
05
06   LET sql_text = "SELECT store_num, " ||
07      " store_name, addr, addr2, city, " ||
08      " state, zip_code, contact_name, phone " ||
09      " FROM customer WHERE " || p_where_clause ||
10      " ORDER BY store_num"
11 
12   DECLARE cust_curs SCROLL CURSOR FROM sql_text 
13   OPEN cust_curs 
14   CALL fetch_cust(1)   -- fetch the first row 
15     RETURNING fetch_ok 
16   IF NOT (fetch_ok) THEN
17     MESSAGE "no rows in table."   
18   END IF
19
20   RETURN fetch_ok 
21 
22 END FUNCTION
Note:
  • Line 01 The function cust_select accepts as a parameter the where_clause, storing it in the local variable p_where_clause.
  • Lines 06 thru 10 concatenate the entire text of the SQL statement into the local STRING variable sql_txt.
  • Line 12 declares a SCROLL CURSOR with the identifier cust_curs, for the STRING variable sql_text.
  • Line 13 opens the cursor, positioning before the first row of the result set. These statements are physically in the correct order within the module.
  • Lines 14 and 15 call the function fetch_cust, passing as a parameter the literal value 1, and returning a value stored in the local variable fetch_ok. Passing the value 1 to fetch_cust will result in the NEXT row of the result set being fetched (see the logic in the function fetch_cust), which is this case would be the first row.
  • Line 16 Since fetch_ok is defined as a SMALLINT, it can be used as a flag containing the values TRUE or FALSE. The value returned from the function fetch_cust indicates whether the fetch was successful.
  • Line 17 displays a message to the user if the FETCH was not successful. Since this is the fetch of the first row in the result set, another user must have deleted the rows after the program selected the count.
  • Line 20 returns the value of fetch_ok to the calling function. This determines whether the function display_cust is called.
  • Line 22 is the end of the function cust_select.