Example: custquery.4gl (function cust_select)

This function declares a scroll cursor from the SELECT statement build with the where_clause passed as parameter.

Function cust_select:

  1 FUNCTION cust_select(p_where_clause STRING) RETURNS BOOLEAN
  2   DEFINE sql_text STRING
  3   LET sql_text = "SELECT * FROM customer WHERE "
  4               || p_where_clause
  5               || " ORDER BY cust_num "
  6   TRY
  7      DECLARE cust_curs SCROLL CURSOR FROM sql_text
  8      OPEN cust_curs
  9   CATCH
 10      ERROR "SQL error: ", SQLERRMESSAGE
 11      RETURN FALSE
 12   END TRY
 13   IF NOT fetch_cust(1) THEN
 14      MESSAGE "No rows in table."
 15      RETURN FALSE
 16   ELSE
 17      RETURN TRUE
 18   END IF
 19 END FUNCTION

Note:

Line 1: The function cust_select accepts as a parameter the where_clause, storing it in the local variable p_where_clause. A boolean value is returned by the function.
Line 2 defines the sql_text variable as a STRING.
Lines 3 thru 5 concatenate the entire text of the SQL statement into the local STRING variable sql_text.
Line 6 to 12 define a TRY/CATCH block, to handle SQL errors when the SELECT statement is executed.
Line 7 declares a SCROLL cursor with the identifier cust_curs, using the STRING variable sql_text.
Line 8 opens the cursor, positioning before the first row of the result set. These statements are physically in the correct order within the module.
Line 13 calls the function fetch_cust, passing as a parameter the literal value 1, and returning a boolean value. 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 14 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 15 returns TRUE to the calling function.
Line 17 returns FALSE to the calling function, indicating that no rows were found by fetch_cust.