Example: custquery.4gl (Function get_cust_cnt)

This function is called by the function query_cust to return the count of rows that would be retrieved by the SELECT statement. The criteria previously entered by the user and stored in the variable where_clause is used.

Function get_cust_cnt:

  1 FUNCTION get_cust_cnt(p_where_clause STRING) RETURNS INTEGER
  2   DEFINE sql_text STRING,
  3          cust_cnt INTEGER
  4   LET sql_text = "SELECT COUNT(*) FROM customer WHERE " || p_where_clause
  5   PREPARE cust_cnt_stmt FROM sql_text
  6   EXECUTE cust_cnt_stmt INTO cust_cnt
  7   FREE cust_cnt_stmt
  8   RETURN cust_cnt
  9 END FUNCTION

Note:

Line 1: The function accepts as a parameter the value of where_clause, and returns a single INTEGER value.
Line 2 defines a function variable, sql_text, to hold the complete text of the SQL SELECT statement.
Line 3 defines a function variable cust_cnt to hold the count returned by the SELECT statement.
Lines 4 builds the string containing the complete SQL SELECT statement, concatenating p_where_clause at the end using the || operator. Notice that the word WHERE must be provided in the string.
Line 5 uses the PREPARE instruction to convert the string into an executable SQL statement, parsing the statement and storing it in memory. The prepared statement is modular in scope. The prepared statement has the identifier cust_cnt_stmt.
Line 6 executes the SQL SELECT statement contained in cust_cnt_stmt, using the EXECUTE ... INTO syntax to store the value returned by the statement in the variable cust_cnt. This syntax can be used if the SQL statement returns a single row of values.
Line 7: The FREE statement releases the memory associated with the prepared statement, since this statement is no longer needed.
Line 8 returns the value of cust_cnt to the calling function.