unloadtable Statement—Loop Through Rows and Execute Statements

The unloadtable statement enables a program to access values in each row of the data set in turn and perform actions based on those values. When a table is unloaded, the program takes values from the first row of the data set, places them into specified program variables, and executes statements using those values. It then unloads the second row and executes statements based on values in that row. The loop continues through all the rows in the data set. The actions performed on each set of values are defined by the code inside the statement's begin/end block. (If there is no code in the block, the statement merely scans the rows, performing no actions.)

The dynamic version of this statement places the values into variables pointed to and described by descriptor_name. Descriptor_name identifies an SQL Descriptor Area, which is a host language structure allocated at run time. For information about the structure, allocation, and use of the SQLDA, see the SQL Reference Guide and your host language companion guide. (The dynamic version of unloadtable is not available in QUEL.)

You cannot place any other statements or comment lines between the unloadtable statement and it associated begin/end block.

The formname is the name of the form in which the table field tablename is displayed. Tablename must identify an initialized table field (a table field that has an associated data set). You can use a quoted or unquoted character string literal or a string variable to specify formname and tablename.

The columnnames specify the columns to be unloaded into variables. (The program code in the begin/end block can only access values from columns that are explicitly named; values in columns that are not unloaded cannot be accessed by the code.) The column can be any hidden or displayed column or the special constants, _state and _record. The _state constant returns an integer corresponding to the row's state; for a complete discussion of row states and their meanings and interactions, see Table Fields. The _record constant returns an integer record number corresponding to the row currently being unloaded, with 1 signifying the first row in the data set. If the row is a deleted row, its record number is negative. The data types of a column and its associated variable must be compatible. (See your query language reference guide for information about data types and your host language companion guide for information about compatible data types.)

If a specified column is nullable, you must use an indicator variable in conjunction with the column's variable. Ingres returns an error if it attempts to unload a null and no indicator variable is specified. Indicator variables are described in the your query language reference guide.

The unloadtable statement can access the values of deleted rows; however, when you delete undefined or new rows, they are removed from the data set, and cannot be accessed.

You can include the validate statement in an unloadtable loop, to verify the data being unloaded. If any of the data fails the validation, the unloadtable loop is aborted. You can also terminate the loop with the endloop or resume statements, described in this chapter.

Avoid using the following statements in the begin/end block of the unloadtable statement: loadtable, deleterow, insertrow, inittable, and clear field tablefield_name. Because these change the order or number of rows in the data set, using them in an unloadtable loop can have unforeseen consequences.

Because the tablename must identify an initialized table field, the unloadtable statement must follow an inittable statement for the table field. A common location for unloadtable is at the end of the form's display loop, so that the values in the data set can be processed after all changes have been made to them. An unloadtable statement cannot be nested within another unloadtable statement.

To set or inquire about change variables associated with values in a table field data set that is not currently displayed, you must use the inquire_frs row or set_frs row statement, described earlier, within an unloadtable block. Within an unloadtable block, you must omit the row number; these statements always refer to the row just unloaded.

Process the records in the data set within a database multi-statement transaction. Error handling is ignored.

exec sql savepoint startupdate;
exec frs unloadtable empform employee
    (:ename = ename, :age = age, :eno = eno,
           :state = _state);
exec frs begin;
    if (state = 0) then
        /* undefined is left alone */
        null;
    else if (state = 1) then /* new is appended */
        exec sql insert into employee
                  (eno, ename, age)
           values (:eno, :ename, :age);

    else if (state = 2) then
        /* unchanged is left alone */
        null;
    else if (state = 3) then
        /* Reflect changed data */
        exec sql update employee
            set ename = :ename, age = :age
            where eno = :eno;
    else if (state = 4) then
        /* deleted row */
        exec sql delete from employee
            where eno = :eno;
    end if;
exec frs end;
exec sql commit;
exec frs clear field employee;

In the form statistics with a table field accumulator containing columns name and number, add the integer values of number and put the average in a field called avgvalue. Stop on first error. Assume all the rows are in the state NEW.

rows = 0;
sumvals = 0;
exec frs unloadtable statistics accumulator
  (:name = name, :number = number, :record = _record);
exec frs begin;
    if (name = ' ') then
        exec frs message 'Empty numeric identifier';
        exec frs sleep 2;
        exec frs scroll statistics accumulator
                          to :record;
        exec frs resume field accumulator;
                          /* Break out of loop */
    end if;

Find the first employee over the age of 50.

found = false;
exec frs unloadtable empform employee
    (:ename = ename, :age = age, :state = _state,
     :record = _record);
exec frs begin;
    if (age > 50) and (state \ 4) then
       /* Not deleted */
        found = true;
        exec frs endloop;
    end if;
exec frs end;
if (found) then
    process the specified record;
end if;

Unload the values from a table field, using an indicator variable to allow for a potential null in its one nullable column.

exec frs activate menuitem 'PrintRoster';
exec frs begin;
    exec frs unloadtable empform employee
        (:name = empname, :age = age,
         :spouse:indicator_var = spouse);
     exec frs begin;
         /* Do processing to create roster entry. */
    exec frs end;
exec frs end;

Check the change variable of rows in a table field data set.

exec frs activate menuitem 'PrintOrders';
exec frs begin;
    exec frs unloadtable salesform saleslist
        (:ordernum = order, :item = item);
    exec frs begin;
        /* Check if item ordered has changed. */
        exec frs inquire_frs row salesform saleslist
            (:changed = change(item));
        /* If item that is ordered has changed, */
        /* update the database. */
        if (changed = 1) then
            update the database
        end if;
        process order and print out data;
     exec frs end;
exec frs end:

Using dynamic statements, unload a table field and insert the results into a database table using the same SQLDA.

exec frs describe table :form_var :table_var
    into sqlda;
build and prepare 'insert_stmt' from description of
    table field;
...
exec frs unloadtable :form_var :table_var using
                             descriptor sqlda;
exec frs begin;
    exec sql execute insert_stmt using
        descriptor sqlda;
exec frs end;