Unloadtable
Unloads the contents of the data set underlying a table field or array.
Syntax
unloadtable tablefieldname
[(fieldname = columnname
{, fieldname = columnname})]
begin | {
statement {; statement}
end | }
unloadtable arrayname
[(fieldname = attributename
{, fieldname = attributename})]
begin | {
statement {; statement}
end | }
tablefieldname
Specifies the name of the table field whose data you are unloading
arrayname
Specifies the name of the array whose data you are unloading
fieldname
Specifies the name of a form field, variable, or record attribute into which you are unloading a table-field column or FRS constant
columnname or attributename
Specifies the name of the table-field column or array attribute being unloaded. A 4GL name.
You can specify the special FRS attributes _state or _record as column or attribute names.
Description
The 4GL unloadtable statement lets you scan the data set of an array or table field, one record at a time. Unloadtable causes a loop to execute once for each record in the data set of an array or table field, whether or not the record is currently displayed on the form. As each record is unloaded, all the statements associated with the unloadtable statement are performed. At this point, the next record in the data set is unloaded, and the statements are performed for that record.
The loop continues until either all the records in the data set (including records whose state is Deleted) are processed or an endloop statement is encountered. A validation error also terminates the unloadtable loop.
The statement assigns values from a table-field's columns or from the attributes of an array record, or row status from the FRS constants, _state and _record, to specified variables.
The unloadtable statement operates on arrays in a similar manner. However, because arrays do not define 'display' rows, the statement uses the value of the _record constant as an implicit reference to the index number of the array record.
The unloadtable statement also provides an implicit definition for the _state constant for the records in the array. The possible values for _state are the same as those supported for table fields, except that a state of Undefined (0) is not allowed for arrays. The values for _state in an array are completely under the control of the 4GL program code (because the end users cannot "fill" or modify an array through the FRS).
The unloadtable statement processes records in the following order:
• Non-deleted records are processed first.
Within non-deleted records, records are processed in ascending order by record number (the value of _record).
• Then deleted records (that is, those whose state is Deleted) are processed.
Within deleted records, the most recently deleted record is processed first, then the next most recently deleted record, and so on.
The unloadtable statement has the following restrictions:
• An unloadtable loop cannot include deleterow or insertrow statements against the table field or array being unloaded.
• An unloadtable loop cannot include a database retrieval to the table field being unloaded, because that clears the table field. However, you can select into a column of the table field being unloaded.
• An unloadtable loop unloading an array cannot invoke any of the following array functions: arrayclear, arrayinsertrow, arrayremoverow, or arraysetrowdeleted.
• You cannot nest unloadtable statements. For example, if array2 is an attribute of array1, you cannot use an unloadtable statement with array2.
The entire unloadtable statement, including the statement list, is considered a single 4GL statement. Use these statement conventions:
• Enclose the statement list either with braces or the keywords begin and end.
• Place the statement separator (; or ,) at the end following the closing brace or end keyword if the statement is followed by other statements.
• Do not use a statement separator before the brace (or begin) that opens the statement list.
Within the unloadtable loop, certain expressions have special meanings:
• If you are unloading the table field tablefield, then within the unloadtable loop, expressions of the form tablefield.column refer to the column in the currently unloaded row (rather than the column in the row the cursor occupies). To access a column in the row the cursor occupies, use an explicit row number.
• If you are unloading array, then within the unloadtable loop, expressions of the form array.attribute refer to the attribute in the currently unloaded record. (The compiler does not allow this type of expression in any other context.)
For information on detecting user changes to the fields in the data set, see the section Inquire_forms Row in
Inquire_forms Field. For additional information on the unloadtable statement, _state, and _record, see
Forms-Control Statements.
Copying Table Fields and Arrays
To copy an array to a table field or a table field to an array, use direct unloading and insertion. This constitutes a copy rather than an assignment, as shown in the following examples:
i = integer not null;
x = array of type of table field tf ;
'Copy_tf_to_array '=
begin
i = ArrayClear(x) ;
i = 0;
unloadtable tf
begin
if (tf._state = 4) then
insertrow x[i] (col1 = tf.col1, ...);
/* New row is x[i + 1], not x[i]. */
/* _state = 4 may not be specified */
/* on an insertrow. */
deleterow x[i + 1];
elseif (tf._state <> 0) then
insertrow x[i] (col1 = tf.col1, ...,
_state = tf._state);
/* New row is x[i + 1], not x[i]. */
/* _state = 0 may not be specified */
/* on an insertrow into an array. */
i = i + 1;
endif;
end;
end
i = integer not null;
r = integer not null;
x = array of type of table field tf;
'Copy_array_to_tf '=
begin
clear field tf;
inquire_forms table tf_form
(r = maxrow(tf));
i = 0;
unloadtable x
begin
if (x._state = 4) then
insertrow tf[0] (col1 = x.col1, ...);
/* New row is tf[1], not tf[0]. */
/* _state = 4 may not be specified */
/* on an insertrow */
deleterow tf[i] ;
else
insertrow tf[i] (col1 = x.col1, ...,
_state = x._state);
/* New row is tf[i + 1], not tf[i],*/
/* except that when i = r, */
/* the table field is scrolled */
/* up 1 row and the new row is tf[r]. */
if i < r then
i = i + 1;
endif;
endif;
end;
scroll tf to 1;
end
Using Unloadtable in Performing Calculations
The derived field functionality usually represents the best alternative when you want to total the values in a column of a table field. However, the unloadtable statement offers an alternative method, as in the following example:
'SumSalaries' =
begin
tot := 0;
unloadtable emptable
begin
tot := tot + emptable.salary;
end;
total := tot;
end;
When the application user chooses the SumSalaries menu operation, the unloadtable statement unloads each row of the table field emptable and adds the salary column to a running total in the hidden field tot. After unloading all the values, the running total is assigned to the field total.
For information about using derived fields for calculations, see
Derived Fields and Columns.
Examples
Use unloadtable with pattern matching to scroll the table field to the first row whose data starts with the characters you specify. If you enter "Smith" the pattern is set to 'Smith%' and retrieves the first row beginning with the name Smith, Smithson, Smithburg, etc. The display then scrolls to the selected row.
pattern := name + '%';
unloadtable emp (row = _record)
begin
if emp.name like :pattern then
scroll emp to row;
endloop;
endif;
end;
The example below updates a mailing list on the basis of the _state of each data set row. The hidden table-field column called maillist.old is the key to the updating, because it contains the original value of the Name field when the row was originally loaded from the database into the data set:
'Writelist' =
begin
unloadtable maillist (rowstat = _state)
begin
if rowstat = 1 then
/* Add row to database */
repeated insert into mailtable
(name, address, city, state)
values (maillist.name,
maillist.address,
maillist.city,
maillist.state);
elseif rowstat = 3 then
/* Update the row in the database */
repeated update mailtable
set name = :maillist.name,
address = :maillist.address,
city = :maillist.city,
state = :maillist.state
where name = :maillist.old;
elseif rowstat = 4 then
/* Delete the row from the */
/* database */
repeated delete from mailtable
where name = :maillist.old;
endif;
end;
commit;
end