Example of paged mode

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.