17. Forms Statements : putrow Statement--Update a Table Row
 
Share this page                  
putrow Statement--Update a Table Row
This statement updates values in a table field row.
Syntax
Non-dynamic version:
putrow formname tablename [row]
    (columnname = value {, columnname = value})
Dynamic version:
putrow formname tablename [row]
    using [descriptor] descriptor_name
Description
The putrow statement updates the values in a table field row. It does not add a new row to the table field.
The formname is the name of the form in which the table field tablename is displayed. Both can be specified using character strings, with or without quotes, or as program variables.
The row is the number of the displayed row into which the data are put. Row can be specified as an integer literal or an integer program variable, and must indicate a row currently displayed on the table field. For instance, if the table field can display four rows, but currently contains only two, the row number must be either 1 or 2. To update the current row, omit row.
If you execute this statement inside an unloadtable loop, you must omit row; putrow always updates the row currently being unloaded. (Any statement that changes values in the data set must be used with caution inside an unloadtable loop.)
In the non-dynamic version, the list of columnnames identifies the columns being updated and their new values. You can specify any hidden, invisible, or displayed column in the table field except derived columns. You can use a quoted or unquoted character string literal or a program variable for columnname. Only those columns that are specified receive values. The new values overwrite any values currently in the specified columns. Values in columns which are not specified in the argument list are not changed.
The value must be either a literal or a program variable and must have a data type compatible with the data type of its associated column.
To place a null in the column, you can (1) specify the column's associated value as the key word null, or (2) use an indicator variable. Using an indicator variable allows the user or program to decide at run time whether to place a null in the column. Indicator variables are described in your query language reference guide.
When you execute a dynamic putrow, the column names and values found in the specified descriptor_name are used. The descriptor_name identifies an SQL Descriptor Area (SQLDA), a host language structure allocated at run time. An SQLDA contains an array of elements called sqlvar elements. Each sqlvar contains fields to hold the data type, length, and name of a column in the table field. Each also points to a variable that holds the value for the column it describes. Before you can execute a dynamic putrow statement, you must allocate the SQLDA, describe the table field, and allocate the necessary variables. See the SQL Reference Guide and to your host language companion guide for complete instructions on these procedures. (The dynamic version of putrow is not available in QUEL.)
The putrow statement sets the change variable for each updated column to 0.
When a putrow statement is executed on a row in the data set, it can affect the row's state. If the row is in a state of UNCHANGED, putrow alters its state to CHANGED. However, if the row is in a state of NEW, putrow has no effect on the row's state. For a detailed description of row states, see Table Fields.
The putrow statement can be used to change the state of a row in the data set. The valid values that _state can be set to depends on the current value of _state. If _state is NEW, then it can be set to NEW, UNCHANGED or CHANGED. If _state is UNCHANGED then it can be set to CHANGED. If _state is CHANGED then it can be set to UNCHANGED. The syntax for using the putrow statement to set a row's state is the same as the syntax above, simply use _state as the columnname and the value of NEW, CHANGED or UNCHANGED as the value. You can specify _state as an integer literal or an integer variable.
For example, to change the current row's state to UNCHANGED:
exec frs putrow empform employee (_state = 2);
Examples--putrow statement:
Example 1:
The PutCurrent operation places data in the table field row on which the cursor currently sits.
exec frs activate menuitem 'PutCurrent';
 exec frs begin;
    /* Put new information into the current row. */
    exec frs putrow empform employee
        (age = 52, sal = :sal);
Example 2:
The PutFirst operation puts data in the first displayed row of the table field.
exec frs activate menuitem 'PutFirst';
 exec frs begin;
    exec frs putrow empform employee 1
        (sal = :sal);
exec frs end;
Example 3:
As the employee table field is unloaded, use the putrow statement to mark the rows processed, so as not to process them again. Assume a hidden column marked specified with format integer1.
exec frs activate menuitem 'ProcessRecords';
 exec frs begin;
    exec frs unloadtable empform employee
        (:ename = ename, :age = age,
         :marked = marked, :state = _state);
    exec frs begin;
        /* Process if new, unchanged or changed */
        if ((state = 1 or state = 2 or state = 3) 
            and (marked = 0)) then
            process the data;
            exec frs putrow empform employee
                (marked = 1);
        end if;
    exec frs end;
exec frs end;
Example 4:
Update a table field row, using an indicator variable to allow for potential nulls in the specified column. Menu item UpdateOrder takes information about an order from simple fields and updates a table field row with the changed data.
exec frs activate menuitem 'UpdateOrder';
 exec frs begin;
    /* Get data from other fields */
    exec frs getform sales (:name:indicator_var =
                             salesperson);
    /* Update table field row */
    exec frs putrow sales saleslog
        (ordernumber = :order, salesperson =
        :name:indicator_var);
exec frs end;
Example 5:
Place a null into a field using the null constant.
exec frs activate menuitem 'NewOrder';
 exec frs begin;
    . . .
    exec frs putrow sales saleslist
        (salesperson = null, ordernum = :order);
 exec frs end;
Example 6:
Put values in a table field using a descriptor.
exec frs putrow :formname
            :tablename using descriptor sqlda;
Example 7:
Process the records in the data set within a database multi-statement transaction. Reset the _state of any CHANGED or NEW rows so that a user can make further changes to the data set and they are correctly processed when this menu item is chosen again. Error handling is ignored.
exec frs activate menuitem 'Save';
 exec frs begin;
    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);
            /* reset _state to UNCHANGED */
            exec frs putrow empform employee 
                (_state = 2);
        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;
            /* reset _state to UNCHANGED */
            exec frs putrow empform employee 
                (_state = 2);
        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 end;