In the first example, the records in the customer table are
loaded into the program array and the user uses the form to scroll through
the program array. In this example, the user is actually scrolling through
the result set created by a SCROLL CURSOR. This
SCROLL CURSOR retrieves only the store number,
and another SQL SELECT statement is used to retrieve the
remainder of the row as needed.
Module
cust_lib2.4gl:
01 SCHEMA custdemo
02
03 FUNCTION display_custarr()
04
05 DEFINE cust_arr DYNAMIC ARRAY OF RECORD
06 store_num LIKE customer.store_num,
07 store_name LIKE customer.store_name,
08 city LIKE customer.city,
09 state LIKE customer.state,
10 zip_code LIKE customer.zip_code,
11 contact_name LIKE customer.contact_name,
12 phone LIKE customer.phone
13 END RECORD,
14 ret_num LIKE customer.store_num,
15 ret_name LIKE customer.store_name,
16 ofs, len, i SMALLINT,
17 sql_text STRING,
18 rec_count SMALLINT,
19 curr_pa SMALLINT
20
21 OPEN WINDOW wcust WITH FORM "manycust"
22
23 LET rec_count = 0
24 SELECT COUNT(*) INTO rec_count FROM customer
25 IF (rec_count == 0) THEN
26 RETURN 0, NULL
27 END IF
28
29 LET sql_text =
30 "SELECT store_num, store_name, city,"
31 || " state, zip_code, contact_name,"
32 || " phone"
33 || " FROM customer WHERE store_num = ?"
34 PREPARE rec_all FROM sql_text
35
36 DECLARE num_curs SCROLL CURSOR FOR
37 SELECT store_num FROM customer
38 OPEN num_curs
39
40 DISPLAY ARRAY cust_arr TO sa_cust.*
41 ATTRIBUTES(UNBUFFERED, COUNT=rec_count)
42
43 ON FILL BUFFER
44 LET ofs = FGL_DIALOG_GETBUFFERSTART()
45 LET len = FGL_DIALOG_GETBUFFERLENGTH()
46 FOR i = 1 TO len
47 WHENEVER ERROR CONTINUE
48 FETCH ABSOLUTE ofs + i - 1 num_curs
49 INTO cust_arr[i].store_num
50 EXECUTE rec_all INTO cust_arr[i].*
51 USING cust_arr[i].store_num
52 WHENEVER ERROR STOP
53 IF (SQLCA.SQLCODE = NOTFOUND) THEN
54 MESSAGE "Row deleted by another user."
55 CONTINUE FOR
56 ELSE
57 IF (SQLCA.SQLCODE < 0) THEN
58 ERROR SQLERRMESSAGE
59 CONTINUE FOR
60 END IF
61 END IF
62 END FOR
62
64 AFTER DISPLAY
65 IF (INT_FLAG) THEN
66 LET ret_num = 0
67 LET ret_name = NULL
68 ELSE
69 LET curr_pa = ARR_CURR()- ofs + 1
70 LET ret_num = cust_arr[curr_pa].store_num
71 LET ret_name = cust_arr[curr_pa].store_name
72 END IF
73
74 END DISPLAY
75
76 CLOSE num_curs
77 FREE num_curs
78 FREE rec_all
79
80 CLOSE WINDOW wcust
81 RETURN ret_num, ret_name
82
83 END FUNCTION
Note:
- Lines 16 thru 19 define some new variables to be used,
including cont_disp to indicate
whether the function should continue.
- Line 24 uses an embedded SQL statement to store the
total number of rows in the
customer table in the variable
rec_count.
- Lines 25 thru 27 If the total number of rows is zero,
function returns immediately 0
and NULL.
- Lines 29 thru 33 contain the text of an SQL
SELECT statement to retrieve
values from a single row in the
customer table. The
? placeholder will be replaced with the
store number when the statement
is executed. This text is assigned to a string
variable, sql_text.
- Line 34 uses the SQL PREPARE
statement to convert the string into an executable
statement, rec_all. This
statement will be executed when needed, to
populate the rest of the values in the row of the
program array.
- Lines 36 thru 37
DECLARE a SCROLL
CURSOR
num_curs to retrieve only the
store number from the
customer table.
- Line 38 opens the SCROLL CURSOR
num_curs.
- Lines 40 and 41 call the DISPLAY ARRAY
statement, providing the
COUNT to let the statement know
the total number of rows in the SQL result
set.
- Lines 43 thru 62 contain the logic for the ON FILL BUFFER clause of the DISPLAY
ARRAY statement. This control block will be executed automatically whenever a new page of
data is required.
- Line 44 uses the built-in function to get the offset
for the page, the starting point for the retrieval
of rows, and stores it in the variable
ofs.
- Line 45 uses the built-in function to get the page
length, and stores it in the variable
len.
- Lines 46 thru 62 contain a FOR loop
to populate each row in the page with values from
the customer table. The
variable i is incremented to
populate successive rows. The first value of
i is 1.
- Lines 48 and 49 use the SCROLL CURSOR num_curs with the syntax
FETCH ABSOLUTE <row_number> to retrieve the store number from
a specified row in the result set, and to store it in row i of the program array.
Since i was started at 1, the following calculation is used to determine the row
number of the row to be
retrieved:
(Offset for the page) PLUS i MINUS 1
Notice that rows 1
thru (page_length) of the program array are filled each time a new page is
required.
- Lines 50 and 51 execute the prepared statement
rec_all to retrieve the rest of
the values for row i in the
program array, using the store
number retrieved by the SCROLL
CURSOR. Although this statement is within
the FOR loop, it was prepared
earlier in the program, outside of the loop, to
avoid unnecessary reprocessing each time the loop
is executed.
- Lines 53 thru 61 test whether fetching the entire row
was successful. If not, a message is displayed to
the user, and the CONTINUE FOR
instruction continues the FOR
loop with the next iteration.
- Lines 64 thru 72 use an AFTER DISPLAY
statement to get the row number of the row in the
array that the user had selected. If the dialog
was canceled, ret_num is set to 0
and ret_name is set to blanks.
Otherwise the values of ret_num
and ret_name are set based on the
row number. The row number in the SCROLL
CURSOR result set does not correlate
directly to the program array number, because the
program array was filled starting at row 1 each
time. So the following calculation is used to
return the correct row number of the program
array:
(Row number returned by ARR_CURR) MINUS
(Offset for the page) PLUS 1
- Line 74 is the end of the DISPLAY
ARRAY statement.
- Lines 76 and 77
CLOSE and FREE
the cursor.
- Line 78 frees the prepared statement.
- Line 81 closes the window.
- Line 82 returns the values of the variables
ret_num and
ret_name to the calling
function.