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.