Back to Summary

Tutorial Chapter 8: Array Input

Summary:

• INPUT ARRAY statement
• WITHOUT DEFAULTS clause
• The UNBUFFERED attribute
• COUNT and MAXCOUNT attributes
• Control Blocks
• Built-in Functions  - ARR_CURR()
• Predefined Actions - insert and delete
• Example: Using a Screen Array to Modify Data
  • Form Specification File
  • The Main Block
  • Function load_custall
  • Function inparr_custall
  • Function store_num_ok
  • Function insert_cust
  • Function update_cust
  • Function delete_cust

This program uses a form and a screen array to allow the user to view and change multiple records of a program array at once.  The INPUT ARRAY statement and its control blocks are used by the program to control and monitor the changes made by the user to the records. As each record in the program array is Added, Updated, or Deleted,  the program logic makes corresponding changes in the rows of the customer database table.

The example window shown below has been re-sized to fit on this page.

                                             Display on Windows platform

The INPUT ARRAY statement

The INPUT ARRAY statement supports data entry by users into a screen array, and stores the entered data in a program array of records. During the INPUT ARRAY execution, the user can edit or delete existing records, insert new records, and move inside the list of records.  The program can then use the INSERT, DELETE or UPDATE SQL statements to modify the appropriate database tables. The INPUT ARRAY statement does not terminate until the user validates or cancels the dialog.

   INPUT ARRAY cust_arr WITHOUT DEFAULTS FROM sa_cust.* 
        ATTRIBUTE (UNBUFFERED, COUNT=idx)

The example INPUT ARRAY statement binds the screen array fields in sa_cust to the member records of the program array cust_arr. The number of variables in each record of the program array must be the same as the number of fields in each screen record (that is, in a single row of the screen array). Each mapped variable must have the same or a compatible data type as the corresponding field.

WITHOUT DEFAULTS clause

The WITHOUT DEFAULTS clause prevents BDL from displaying any default values that have been defined for form fields. You must use this clause if you want to see the values of the program array.

The UNBUFFERED attribute

As in the INPUT statement, when the UNBUFFERED attribute is used, the INPUT ARRAY statement is sensitive to program variable changes. If you need to display new data during the execution, use the UNBUFFERED attribute and assign the values to the program array row; the runtime system will automatically display the values to the screen.  This sensitivity applies to ON ACTION control blocks, as well.

COUNT and MAXCOUNT attributes

Some of the attributes of the INPUT ARRAY statement are:

• The COUNT attribute of INPUT ARRAY defines the number of valid rows in the program array to be  displayed as default rows. When using a static array, if you do not use the COUNT attribute, the runtime system cannot determine how much data to display, so the screen array remains empty. When you use a dynamic array, the COUNT attribute is ignored: The number of elements in the dynamic array is used.
• The MAXCOUNT attribute defines the maximum number of data rows that can be entered in the program array. In a dynamic array, the user can enter an infinite number of rows if the MAXCOUNT attribute is not set.

Control Blocks

Control blocks in the INPUT ARRAY statement allow your program to control and monitor any changes the user makes. The control blocks that are used in the example program are:

• The BEFORE INPUT block - executed one time, before the runtime system gives control to the user. You can implement initialization in this block.
• The BEFORE ROW block  - executed each time the user moves to another row, after the destination row is made the current one.
• The ON ROW CHANGE block  - executed when the user moves to another row after modifications have been made to the current row.
• The ON CHANGE <fieldname> block  - executed when the cursor leaves a specified field and the value was changed by the user since the field got the focus.
• The BEFORE INSERT block -  executed each time the user inserts a new row in the array, before the new row is created and made the current one.
• The AFTER INSERT block - executed each time the user inserts a new row in the array, after the new row is created. You can cancel the insert operation with the CANCEL INSERT keywords.
• The BEFORE DELETE block - executed each time the user deletes a row from the array, before the row is removed from the list. You can cancel the delete operation with the CANCEL DELETE keywords.

See INPUT ARRAY for a complete list of control blocks.

Built-in Functions - ARR_CURR

The language provides several built-in functions to use in an INPUT ARRAY statement. The example program uses the ARR_CURR function to tell which array element is being changed.  This function returns the row number within the program array that is displayed in the current line of a screen array.

Predefined actions

There are some pre-defined actions that are specific to the INPUT ARRAY statement, to handle the insertion and deletion of rows in the screen array automatically:

• The insert action inserts a new row before current row. When the user has filled this record, BDL inserts the data into the program array.
• The delete action deletes the current record from the display of the screen array and from the program array, and redraws the screen array so that the deleted record is no longer shown.
• The append action adds a new row at the end of the list. When the user has filled this record, BDL inserts the data into the program array.

As with the pre-defined actions accept and cancel actions discussed in Chapter 4, if your form specification does not contain action views for these actions, default action views (buttons on the form) are automatically created. Control attributes of the INPUT ARRAY statement allow you to prevent the creation of these actions and their accompanying buttons. 

Example: Using a Screen Array to modify Data

The Form Specification File

The custallform.per form specification file displays multiple records at once, and is similar to the form used in chapter 7.  The item type of field f6, containing the state values, has been changed to COMBOBOX to provide the user with a dropdown list when data is being entered.

01 SCHEMA custdemo
02
03 LAYOUT
04  TABLE
05  {
06   Id   Name         ..  Zipcode   Contact          Phone
07  [f01][f02         ]   [f07     ][f08            ][f09         ]
08  [f01][f02         ]   [f07     ][f08            ][f09         ]
09  [f01][f02         ]   [f07     ][f08            ][f09         ]
10  [f01][f02         ]   [f07     ][f08            ][f09         ]
11  [f01][f02         ]   [f07     ][f08            ][f09         ]
12  [f01][f02         ]   [f07     ][f08            ][f09         ]
13  }
14  END
15 END
16
17 TABLES
18  customer
19 END
20
21 ATTRIBUTES
22 EDIT     f01 = customer.store_num, REQUIRED;
23 EDIT     f02 = customer.store_name, REQUIRED; 
24 EDIT     f03 = customer.addr;
25 EDIT     f04 = customer.addr2;
26 EDIT     f05 = customer.city;
27 COMBOBOX f6  = customer.state, ITEMS = ("IA", "IL", "WI");
28 EDIT     f07 = customer.zipcode;
29 EDIT     f08 = customer.contact_name;
30 EDIT     f09 = customer.phone;
31 END
32
33 INSTRUCTIONS
34 SCREEN RECORD sa_cust (customer.*);
35 END

The Main block

The single module program custall.4gl allows the user to update the customer table using a form that displays multiple records at once.

01 SCHEMA custdemo
02
03 DEFINE cust_arr DYNAMIC ARRAY OF RECORD
04         store_num    LIKE customer.store_num,
05         store_name   LIKE customer.store_name,
06         addr         LIKE customer.addr,
07         addr2        LIKE customer.addr2,
08         city         LIKE customer.city,
09         state        LIKE customer.state,
10         zipcode      LIKE customer.zipcode,
11         contact_name LIKE customer.contact_name,
12         phone        LIKE customer.phone
13        END RECORD
14 
15
16 MAIN
17   DEFINE idx SMALLINT
18
19   DEFER INTERRUPT
20   CONNECT TO "custdemo"
21   CLOSE WINDOW SCREEN
22   OPEN WINDOW w3 WITH FORM "custallform"
23
24   CALL load_custall() RETURNING idx
25   IF idx > 1 THEN
26      CALL inparr_custall(idx)
27   END IF
28
29   CLOSE WINDOW w3
30   DISCONNECT CURRENT
31
32 END MAIN

Notes:

• Lines 03 thru 13 define a  dynamic array cust_arr having the same structure as the customer table.  The array is modular is scope.
• Line 17 defines a local variable idx, to hold the returned value from the load_custall function.
• Line 20 connects to the custdemo database.
• Line 22 opens a window with the form manycust.  This form contains a screen array sa_cust which is referenced in the program.
• Line 24 thru 27 call the function load_custall to load the array, which returns the index of the array.  If the load was successful (the returned index is greater than 1) the function inparr_custall is called, passing the value of idx. This function contains the logic for the Input/Update/Delete of rows.
• Line 29 closes the window.
• Line 30 disconnects from the database.

Function load_custall

This function loads the program array with rows from the customer database table. The logic to load the rows is identical to that in Chapter 7. Although this program loads all the rows from the customer table, the program could be written to allow the user to query first, for a subset of the rows.  A query-by-example, as illustrated in chapter 4, can also be implemented using a form containing a screen array such as manycust.

01 FUNCTION load_custall()
02   DEFINE cust_rec RECORD LIKE customer.*,
03          idx SMALLINT
04
05   DECLARE custlist_curs CURSOR FOR
06      SELECT store_num, 
07          store_name, 
08          addr,
09          addr2,
10          city, 
11          state, 
12          zipcode, 
13          contact_name, 
14          phone
15        FROM customer
16        ORDER BY store_num
17
18   LET idx = 0
19   CALL cust_arr.clear()
20   FOREACH custlist_curs INTO cust_rec.*
21      LET idx = idx + 1
22      LET cust_arr[idx].* = cust_rec.*
23   END FOREACH
24    
25   IF (idx == 0) THEN 
26      MESSAGE "No rows loaded." 
27   END IF
28
29   RETURN idx
30
31 END FUNCTION

Notes:

• Line 02 defines a local record variable, cust_rec, to hold the rows fetched in FOREACH.
• Line 03 defines a local variable, idx, to hold the value of the index of the array.
• Lines 05 thru 16 declare the cursor custlist_curs to retrieve the rows from the customer table.
• Lines 18 thru 22 retrieve the rows from the result set into the program array.
• Lines 25 thru 27  If idx is zero, we display a warning message. 
• Line 29 returns the number of rows to the MAIN function.

Function inparr_custall

This is the primary function of the program, driving the logic for inserting, deleting, and changing rows in the customer database table.  Each time a record is added, deleted, or changed on the form, the values from the current record in the program array are used to update the customer table.  

01 FUNCTION inparr_custall(idx)
02   DEFINE idx SMALLINT,
03         curr_pa SMALLINT,
04         opflag  CHAR(1)
05
06  INPUT ARRAY cust_arr WITHOUT DEFAULTS
07      FROM sa_cust.* 
08      ATTRIBUTE (UNBUFFERED, COUNT=idx)
09
10   BEFORE INPUT
11    MESSAGE "OK exits/" ||
12    "Cancel exits & cancels current operation"
13
14   BEFORE ROW
15     LET curr_pa = ARR_CURR()
16     LET opflag = "U"
17
18   BEFORE INSERT
19     LET opflag = "I"
20 
21   AFTER INSERT
22     CALL insert_cust(curr_pa)
23
24   BEFORE DELETE
25     IF NOT (delete_cust(curr_pa)) THEN
26       CANCEL DELETE
27     END IF
28
29   ON ROW CHANGE
30     CALL update_cust(curr_pa)
31
32   BEFORE FIELD store_num
33    IF (opflag <> "I") THEN
34      NEXT FIELD store_name
35    END IF
36
37   ON CHANGE store_num
38    IF (opflag = "I") THEN  
39       IF NOT store_num_ok(curr_pa) THEN
40        MESSAGE "Store already exists"
41        LET cust_arr[curr_pa].store_num = NULL
42        NEXT FIELD store_num
43      END IF 
44    END IF
45
46  END INPUT
47
48  IF (INT_FLAG) THEN
49    LET INT_FLAG = FALSE
50  END IF
51
52 END FUNCTION -- inparr_custall

Notes:

• Line 02 defines the variable idx to hold the number of rows loaded in the array, passed by the MAIN function.
• Line 03 defines the variable curr_pa, to hold the index number of the current record in the program array.
• Line 04 defines the variable opflag, to indicate whether the operation being performed on a record is an Insert ("I") or an Update ("U").
• Lines 06 thru 46 contain the INPUT ARRAY statement, associating the program array cust_arr with the sa_cust screen array on the form.  The attribute WITHOUT DEFAULTS is used so the same statement can handle both Updates and Inserts of new records.  The UNBUFFERED attribute insures that values entered into the program array are automatically displayed in the screen array on the form.
• Lines 10 thru 12  BEFORE INPUT control block: before the INPUT ARRAY statement is executed a MESSAGE is displayed to the user.
• Lines 14 and 16 BEFORE ROW control block: when called in this block, the ARR_CURR function returns the index of the record that the user is moving into (which will become the current record).  This is stored in a variable curr_pa, so the index can be passed to other control blocks.
  We also initialize the opflag to "U":  This will be its value except during the time an Insert of a new record is being performed.
• Lines 18 and 19 BEFORE INSERT control block: just before the user is allowed to enter the values for a new record, the variable opflag is set to "I", indicating an Insert operation.
• Lines 21 and 22 AFTER INSERT control block: calls the insert_cust function, passing the index of the current record that was just inserted into the program array, so the values of that record can be inserted into the customer database table.
• Lines 24 thru 27 BEFORE DELETE control block: after the user has indicated he wants to delete a record in the program array, but before the record is removed from the array, the index of the record is passed to the delete_cust function so the corresponding row can be removed from the database.  If this function returns TRUE, the record is removed from the program array.  The delete of the program record is cancelled using the CANCEL DELETE statement if the delete_cust function returns FALSE.
• Lines 29 thru 30 ON ROW CHANGE control block: After row modification, the function update_cust is called, passing the index of the current record, so the corresponding database row can be updated.
• Lines 32 thru 35 BEFORE FIELD store_num control block:  the store_num field should not be entered by the user unless the operation is an Insert of a new row, indicated by the "I" value of opflag.  The store_num column in the customer database table is a primary key and cannot be updated.  If  the operation is not an Insert, the NEXT FIELD statement is used to move the cursor to the next field in the program array, store_name, allowing the user to change all the fields in the record of the program array except store_num.
• Lines 37 thru 44 ON CHANGE store_num control block: if the operation was an Insert, the store_num_ok function is called to verify that the value that the user has just entered into the field store_num of the current program array does not already exist in the customer database table.  If the store number does exist, the value entered by the user is nulled out, and the cursor is returned to the store_num field.
• Lines 48 thru 50 check the value of the interrupt flag INT_FLAG and re-set it to FALSE if necessary.

Function store_num_ok

When a new record is being inserted into the program array, this function verifies that the store number does not already exist in the customer database table. The logic in this function is virtually identical to that used in Chapter 5.

01 FUNCTION store_num_ok(idx)
02   DEFINE idx SMALLINT,
03         checknum LIKE customer.store_num,
04         cont_ok SMALLINT
05
06   LET cont_ok = FALSE
07   WHENEVER ERROR CONTINUE 
08   SELECT store_num INTO checknum
09     FROM customer 
10     WHERE store_num = 
11          cust_arr[idx].store_num
12   WHENEVER ERROR STOP     
13   IF (SQLCA.SQLCODE = NOTFOUND) THEN
14     LET cont_ok = TRUE 
15   ELSE
16     LET cont_ok = FALSE
17     IF (SQLCA.SQLCODE = 0) THEN 
18       MESSAGE "Store Number already exists."
19     ELSE
20       ERROR SQLERRMESSAGE
21     END IF
22   END IF
23 
24   RETURN cont_ok
25
26 END FUNCTION

Notes:

• Line 02 The index of the current record in the program array is stored in the variable idx, passed to this function from the INPUT ARRAY control block ON CHANGE store_num.
• Line 03 The variable checknum is defined to hold the store_num returned by the SELECT statement.
• Line 06 sets the variable cont_ok to an initial value of FALSE. This variable is used to indicate whether the store number is unique.
• Lines 07 thru 12 use an embedded SQL SELECT statement to check whether the store_num already exists in the customer table. The index passed to this function is used to obtain the value that was entered into the store_num field on the form.  The entire database row is not retrieved by the SELECT statement since the only information required by this program is whether the store number already exists in the table. The  SELECT is surrounded by WHENEVER ERROR statements.
• Lines 13 thru 22 test SQLCA.SQLCODE to determine the success of the SELECT statement. The variable cont_ok is set to indicate whether the store number entered by the user is unique.
• Line 24 returns the value of cont_ok to the calling function.

Function insert_cust

This function inserts a new row into the customer database table.

01 FUNCTION insert_cust(idx)
02   DEFINE idx SMALLINT
03
04   WHENEVER ERROR CONTINUE
05   INSERT INTO customer 
06       (store_num, 
07        store_name, 
08        addr, 
09        addr2, 
10        city, 
11        state, 
12        zipcode, 
13        contact_name, 
14        phone)
15     VALUES (cust_arr[idx].* )
16   WHENEVER ERROR STOP
17
18   IF (SQLCA.SQLCODE = 0) THEN
19     MESSAGE "Store added"
20   ELSE
21     ERROR SQLERRMESSAGE
22   END IF
23
24 END FUNCTION

Notes:

• Line 02 This function is called from the AFTER INSERT control block of the INPUT ARRAY statement.  The index of the record that was inserted into the cust_arr program array is passed to the function and stored in the variable idx.
• Lines 04 thru 16 uses an embedded SQL INSERT statement to insert a row into the customer database table. The values to be inserted into the customer table are obtained from the record just inserted into the program array. The INSERT  is surrounded by WHENEVER ERROR statements.
• Lines 18 thru 22 test the SQLCA.SQLCODE to see if the insert into the database was successful, and return an appropriate message to the user.

Function update_cust

This function updates a  row in the customer database table.  The functionality is very simple for illustration purposes, but it could be enhanced with additional error checking routines similar to the example in chapter 6.

01 FUNCTION update_cust(idx)
02   DEFINE idx SMALLINT
03   
04   WHENEVER ERROR CONTINUE
05   UPDATE customer 
06    SET 
07     store_name   = cust_arr[idx].store_name,
08     addr         = cust_arr[idx].addr,
09     addr2        = cust_arr[idx].addr2, 
10     city         = cust_arr[idx].city, 
11     state        = cust_arr[idx].state, 
12     zipcode      = cust_arr[idx].zipcode, 
13     contact_name = cust_arr[idx].contact_name,
14     phone        = cust_arr[idx].phone 
15    WHERE store_num = cust_arr[idx].store_num
16   WHENEVER ERROR STOP
17
18   IF (SQLCA.SQLCODE = 0) THEN
19      MESSAGE "Dealer updated."
20   ELSE
21      ERROR SQLERRMESSAGE
22   END IF
23
24 END FUNCTION

Notes:

• Line 02  The index of the current record in the cust_arr program array is passed as idx from the ON ROW CHANGE  control block.
• Lines 04 thru 16 use an embedded SQL UPDATE statement to update a row in the customer database table. The index of the current record in the program array is used to obtain the value of store_num that is to be matched in the customer table.  The customer row is updated with the values stored in the current record of the program array.  The UPDATE is surrounded by WHENEVER ERROR statements.
• Lines 18 thru 22 test the SQLCA.SQLCODE to see if the update of the row in the database was successful, and return an appropriate message to the user.

Function delete_cust

This function deletes a row from the customer database table.  A modal Menu similar to that illustrated in Chapter 6 is used to verify that the user wants to delete the row.

01 FUNCTION delete_cust(idx)
02   DEFINE idx     SMALLINT,
03         del_ok      SMALLINT
04
05   LET del_ok = FALSE
06
07   MENU "Delete" ATTRIBUTE (STYLE="dialog", 
08               COMMENT="Delete this row?")
09    COMMAND "OK"
10      LET del_ok = TRUE
11      EXIT MENU
12    COMMAND "Cancel"
13      LET del_ok = FALSE 
14      EXIT MENU
15   END MENU
16
17   IF del_ok = TRUE THEN 
18     WHENEVER ERROR CONTINUE
20     DELETE FROM customer 
21        WHERE store_num = cust_arr[idx].store_num
22     WHENEVER ERROR STOP
23
24     IF (SQLCA.SQLCODE = 0) THEN
25       LET del_ok = TRUE
26       MESSAGE "Dealer deleted."
27     ELSE
28       LET del_ok = FALSE
29       ERROR SQLERRMESSAGE
30     END IF
31   END IF
32
33   RETURN del_ok
34
35 END FUNCTION

Notes:

• Line 02 The index of the current record in the cust_arr program array is passed from the BEFORE DELETE control block of INPUT ARRAY, and stored in the variable idx.  The BEFORE DELETE  control block is executed immediately before the record is deleted from the program array, allowing the logic in this function to be executed before the record is removed from the program array.
• Line 05 sets the initial value of del_ok to FALSE.
• Lines 07 thru 15 display the modal Menu to the user for confirmation of the Delete.
• Lines 18 thru 22 use an embedded SQL DELETE statement to delete the row from the customer database table.  The variable idx  is used to determine the value of store_num in the program array record that is to be used as criteria in the DELETE statement.  This record in the program array has not yet been removed, since this delete_cust function was called in a BEFORE DELETE control block. The DELETE is surrounded by WHENEVER ERROR statements.
• Lines 24 thru 30 test the SQLCA.SQLCODE to see if the update of the row in the database was successful, and return an appropriate message to the user.  The value del_ok is set based on the success of the SQL DELETE statement.
• Line 33 returns the variable del_ok to the BEFORE DELETE control block, indicating whether the Delete of the customer row was successful.