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.* 
        ATTRIBUTES (UNBUFFERED)

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 data type or a compatible data type as the corresponding field.

WITHOUT DEFAULTS clause

The WITHOUT DEFAULTS clause instructs the INPUT ARRAY statement to use and display the rows currently stored in the program array. Without this clause, the INPUT ARRAY would start with an empty list. When creating a new row with the insert or append action, the REQUIRED attribute is always taken into account by INPUT ARRAY, even if the WITHOUT DEFAULTS clause is used.

ault 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: Before executing the code corresponding to the invoked action, the content of the field is converted and assigned to the corresponding program variable.

COUNT and MAXCOUNT attributes

Some other attributes that can be used with an 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 using 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

Your program can control and monitor the changes made by the user by using control blocks with the INPUT ARRAY statement. 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 after 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.
• The AFTER ROW block - executed each time the user moves to another row,  before the current row is left. This trigger can also be executed in other situations, such as when you delete a row, or when the user inserts a new row.

For a more detailed explanation of the priority of control blocks see Input Array. 

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 > 0 THEN
26      CALL inparr_custall()
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 0) the function inparr_custall is called. 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 
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  
19   CALL cust_arr.clear()
20   FOREACH custlist_curs INTO cust_rec.*
21      CALL cust_arr.appendElement()
22      LET cust_arr[cust_arr.getLength()].* = cust_rec.*
23   END FOREACH
24    
25   IF (cust_arr.getLength() == 0) THEN 
26      DISPLAY "No rows loaded." 
27   END IF
28
29   RETURN cust_arr.getLength()
30
31 END FUNCTION

Notes:

• Line 02 defines a local record variable, cust_rec, to hold the rows fetched in FOREACH.
• Lines 05 thru 16 declare the cursor custlist_curs to retrieve the rows from the customer table.
• Lines 20 thru 23 retrieve the rows from the result set into the program array.
• Lines 25 thru 27  If the array is empty, 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 row in the array on the form is added, deleted, or changed, the values from the corresponding row in the program array are used to update the customer database table. The variable opflag is used by the program to indicate the status of the current operation:

• N - no action; set in the BEFORE ROW control block; this will subsequently be changed if an insert or update of a row in the array is performed.
• T - temporary; set in the BEFORE INSERT control block; indicates that an insert of a new row has been started.
• I -  insert; set in the AFTER INSERT control block; indicates that the insert of the new row was completed.
• U - update; set in the ON ROW CHANGE control block; indicates that a change has been made to an existing row.

The value of opflag is tested in an AFTER ROW control block to determine whether an SQL INSERT or SQL UPDATE of the database table is performed.

Since multiple control blocks are triggered by an operation, the order of execution of the blocks is important. This is discussed in detail in the INPUT ARRAY page of this manual.  The example below illustrates how the order of execution of the control blocks is used by the program to set the opflag variable appropriately:

01 FUNCTION inparr_custall(idx)
02
03   DEFINE curr_pa SMALLINT,
04          opflag  CHAR(1)
05
06  INPUT ARRAY cust_arr WITHOUT DEFAULTS
07      FROM sa_cust.* 
08      ATTRIBUTES (UNBUFFERED)
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 = "N"
17
18   BEFORE INSERT
19     LET opflag = "T"
20 
21   AFTER INSERT
22     LET opflag = "I"
23
24   BEFORE DELETE
25     IF NOT (delete_cust(curr_pa)) THEN
26         CANCEL DELETE
27       END IF
28    
29   ON ROW CHANGE
30      IF (opflag <> "I") THEN
31        LET opflag = "U"
32       END IF
33
34   BEFORE FIELD store_num
35    IF (opflag <> "T") THEN
36      NEXT FIELD store_name
37    END IF
38
39   ON CHANGE store_num
40    IF (opflag = "T") THEN  
41       IF NOT store_num_ok(curr_pa) THEN
42        MESSAGE "Store already exists"
43        LET cust_arr[curr_pa].store_num = NULL
44        NEXT FIELD store_num
45      END IF 
46    END IF
47
48    AFTER ROW
49      IF (INT_FLAG) THEN EXIT INPUT END IF
50      CASE
51      WHEN opflag = "I"
52          CALL insert_cust(curr_pa)
53        WHEN opflag = "U"
54          CALL update_cust(curr_pa)
55      END CASE
56
57  END INPUT
58
59  IF (INT_FLAG) THEN
60    LET INT_FLAG = FALSE
61  END IF
62
63 END FUNCTION -- inparr_custall

Notes:

• 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 57 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 to use and display existing records of the program array. The UNBUFFERED attribute insures that the program array the screen array of the form are automatically synchronized for input and output.
• Lines 10 thru 12  BEFORE INPUT control block: before the INPUT ARRAY statement is executed a MESSAGE is displayed to the user.
• Lines 14 thru 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 "N":  This will be its value unless an update or insert is 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 "T", indicating an Insert operation is in progress.
• Lines 21 and 22 AFTER INSERT control block sets the opflag to "I" after the insert operation has been completed.
• Lines 24 thru 27 BEFORE DELETE control block: Before the record is removed from the program array, the function delete_cust is called, which verifies that the user wants to delete the current record. In this function, when the user verifies the delete, the index of the record is used to remove the corresponding row from the database. Unless the delete_cust function returns TRUE, the record is not removed from the program array. 
• Lines 29 thru 32 ON ROW CHANGE control block: After row modification,  the program checks whether the modification was an insert of a new row. If not, the opflag is set to "U" indicating an update of an existing row.
• Lines 34 thru 37 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 "T" 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 39 thru 46 ON CHANGE store_num control block: if the operation is 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 55 AFTER ROW control block: First, the program checks INT_FLAG to see whether the user wants to interrupt the INPUT operation. If not, the opflag is checked in a CASE statement, and the insert_cust or update_cust function is called based on the opflag value. The index of the current record is passed to the function so the database table can be modified.
• Line 57 indicates the end of the INPUT statement.
• Lines 59 thru 61 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" ATTRIBUTES (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.