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 STRING) RETURNS BOOLEAN 
02   DEFINE  sql_text STRING,
03         fetch_ok BOOLEAN         
04
05   LET sql_text = "SELECT store_num, " ||
06      " store_name, addr, addr2, city, " ||
07      " state, zip_code, contact_name, phone " ||
08      " FROM customer WHERE " || p_where_clause ||
09      " ORDER BY store_num"
10 
11   DECLARE cust_curs SCROLL CURSOR FROM sql_text 
12   OPEN cust_curs 
13   
14   IF NOT ( fetch_ok := fetch_cust(1) ) THEN
15     MESSAGE "no rows in table."   
16   END IF
17
18   RETURN fetch_ok 
19 
20 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 05 thru 09 concatenate the entire text of the SQL statement into the local STRING variable sql_txt.
  • Line 11 declares a SCROLL CURSOR with the identifier cust_curs, for the STRING variable sql_text.
  • Line 12 opens the cursor, positioning before the first row of the result set. These statements are physically in the correct order within the module.
  • Line14 calls 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, which is this case would be the first row.
  • Line 15 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 18 returns the value of fetch_ok to the calling function. This determines whether the function display_cust is called.
  • Line 20 is the end of the function cust_select.