loadtable Statement--Append a Row of Data
This statement appends a row of data to a table field's data set.
Syntax
Non-dynamic version:
loadtable formname tablename
(columnname = value {, columnname = value})
[with (attribute(columnname) = 0 | 1 |color
{, attribute(columnname) = 0 | 1 | color})]
Dynamic version:
loadtable formname tablename
using [descriptor] descriptor_name
Description
The loadtable statement loads values into a table field's data set. The dynamic version enables the program to do this using values determined at run time. The loadtable statement is typically executed before an initialize statement or inside the initialize loop, to load a form's table field with a set of database values retrieved using a select loop or a cursor.
You can specify columnname using a quoted or unquoted string or host string variable. The data type of the value must be compatible with the data type of the column. The formname must specify the name of the form in which the table field tablename is displayed. You can specify formname and tablename using quoted or unquoted strings or host string variables.
Each time the loadtable statement is executed, one row is appended to the data set. As rows are added, they are displayed until the table field display is full. After the table field display is full, newly-loaded values continue to be added to the end of the data set; the user can access these rows by scrolling to them.
The non-dynamic loadtable statement loads values into columns in the table field. The list of columnnames identifies the columns receiving values and the values they are to receive. You can specify any hidden, invisible, or displayed column in the table field; you cannot load values into derived columns. Values for any derived columns are automatically calculated and loaded, if possible, when the source columns are loaded.
To set the state of a row in table field, specify the constant _state for columnname. Only specified columns receive values. Any column not included receives a null if it is nullable or a default value (a blank for character columns or 0 for numeric columns).
If you want to load a null into a column, there are two ways to do so. First, you can specify the column's associated value as the key word null. This method loads a null into the column whenever the statement executes. The alternate, and more flexible method, uses 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.
An indicator variable is a two-byte integer variable associated with the variable used to assign values into the column. To specify an indicator variable, use the following syntax:
loadtable formname tablename (columnname = var:indicator_var)
You can only use an indicator variable when you use a variable for var. Also, you must have previously declared the indicator variable in a host variable declaration section.
When an indicator variable is set to -1, any value in its associated variable is ignored and a null is assigned to the specified column. You must set the indicator variable before executing the loadtable statement and the receiving column must be nullable. See your host language companion guide for a complete description of the use of indicator variables.
If the columnname is the constant _state, value must evaluate to one of the following:
0 — UNDEFINED
1 — NEW
2 — UNCHANGED
3 — CHANGED
You must not assign values to any visible columns when you load a row and specify a _state of UNDEFINED. Ingres issues a runtime warning when this happens but does load the column values. It is never possible to assign the DELETED state when loading a row.
By default, if you do not explicitly assign a row state to a loaded row, the new row has the state of UNCHANGED. This state changes to CHANGED as soon as the user or the program alters any of the row's values. For a description of each row state, their meanings and interactions, see
Table Fields.
Each column in the loaded row (except hidden columns) has its change variable cleared (set to 0).
The loadtable statement can only be performed on a table field associated with a data set. Therefore, it must follow an inittable statement for the table field.
The dynamic version of the statement loads the table using column names and values found in descriptor_name. 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 execute a dynamic loadtable you must allocate the SQLDA, describe the table field, and allocate and set the necessary variables. See the SQL Reference Guide and your host language companion guide for instructions on these procedures. (The dynamic version of loadtable is not available in QUEL.)
To turn display attributes on or off when the row is loaded, use the optional with attribute clause; specify 0 to turn the attribute off, 1 to turn the attribute on; if you are assigning color, you must specify a color code in the range 0 to 7. The attribute(s) you specify are assigned to the value, and scroll with the value.
Attribute must be one of the values listed in the following table:
blink
Specifies that the field blinks on and off
color
Specifies that the field is displayed in the specified color (0 - 7)
intensity
Specifies that the field is displayed in half or bright intensity, depending on terminal
normal
Specifies no special attributes
reverse
Specifies that the field is displayed in reverse video
underline
Specifies that the field is underlined
Examples--loadtable statement:
Example 1:
Load data from variables into the ename and eno columns of the employee table field.
exec frs loadtable empform employee
(ename = :ename, eno = :eno);
Example 2:
Append a row of constant values to the end of the data set.
exec frs loadtable empform employee
(ename = 'johnson', eno = 25);
Example 3:
Load all values from the employee database table into the employee table field.
exec sql declare empcursor cursor for
select eno, ename, age, sal, dept
from employee;
exec sql open cursor for read only;
exec sql whenever not found goto done;
loop until no more rows
exec sql fetch empcursor into
:eno, :ename, :age, :sal, :dept;
exec frs loadtable empform employee
(eno = :eno, ename = :ename, age = :age,
sal = :sal, dept = :dept);
end loop;
done:
exec sql close empcursor;
Example 4:
Load information about an employee (gathered from another form) into a table field.
exec frs loadtable empform employee
(ename = :name, spouse = :spouse:indicator_var,
manager = null);
Example 5:
Using dynamic statements, retrieve data from a database and load it into a table field.
exec sql describe stmt into sqlda;
...
exec sql declare csr cursor for stmt;
exec sql open csr for readonly;
loop until no more rows
exec sql fetch csr using descriptor sqlda;
exec frs loadtable :form_var :table_var using
descriptor sqlda;
end loop;
exec sql close csr;