Processing an Entire Table Field or Array with Unloadtable
The unloadtable statement executes a loop that unloads one row at a time from the data set of a table field or one record from an array, and performs any specified actions on the row or record. This allows the application to scan through a table field or array one row or record at a time, applying a series of 4GL statements to each. Typical examples include:
• Query statement that uses the values in the table-field rows to update the rows in a database table
• Assignment from table-field rows to array records in copying a table to an array
The unloadtable statement executes a loop once for each row in the data set. The loop continues until either all rows are processed or an endloop or resume statement is encountered.
Enclose the statement list for the unloadtable statement in braces or the keywords begin and end.
Using Unloadtable with Database Access
You can use the unloadtable statement to update a database table from a table field that is in fill or update mode.
For more information on Forms Runtime operations, see the appendixes in Character-based Querying and Reporting Tools User Guide.
The application in the following example uses the _state constant and an unloadtable statement to update a database with mailing list information.
'Writelist' =
begin
unloadtable maillist
begin
if :maillist._state = 1 then
/* Add row to database */
insert into mailtable
(name, address)
values (maillist.name,
maillist.address );
elseif :maillist._state = 3
/* Update the row in the database */
update mailtable
set name = :maillist.name,
address = :maillist.address
where name = :maillist.old;
elseif :maillist._state = 4 then
/* Delete the row from the database */
delete from mailtable
where name = :maillist.old;
endif;
end;
commit;
end
When this application is running, the user can choose Writelist from the menu to make changes to the database. Ingres, depending on the value of the _state constant, performs the appropriate database operation.
The key for the update and delete statements in this example is the table field's hidden column old, which contains the value of the database column name that was originally loaded from the database (before any updating or deleting by the application user). Note that the updated row is identified by the original value still in the hidden column old.
You must declare the hidden column in the initialization section for the frame and load the values into it along with those in the visible columns when the database retrieval occurs.
Row Processing Order
The unloadtable statement processes deleted records last. It processes the deleted records in the reverse order in which they were deleted (that is, beginning with the most recently deleted record). To process any deleted records either first or in the order in which they originally appeared, use the following procedures:
• To process deleted records first, unload the table field or array twice. The first time, process only deleted records. The second time, process all other records.
• To process deleted records in the order they originally appeared in the table field, do not use the deleterow statement to delete records. Instead, mark deleted records visually so that the user can see they are deleted. In addition, mark them internally with a hidden column or array attribute so your program can see they are deleted in the unloadtable loop.