insertrow Statement--Insert a Row
This statement inserts a new row into a table field.
Syntax
Non-dynamic version:
insertrow formname tablename [row]
(columnname = value [{, columnname = value}])
[with (attribute(columnname) = 0 | 1 | color
{, attribute(columnname) = 0 | 1 | color})]
Dynamic version:
insertrow formname tablename [row]
using [descriptor] descriptor_name
Description
The insertrow statement inserts a new row into the table field display. Unlike other methods of adding rows to the data set, the insertrow statement allows you to add rows to the beginning, middle, or end of the data set. However, insertrow can only insert rows into the displayed table field; it cannot insert rows into any part of the data set not currently visible on the form. For example, if you want to insert a row at the beginning of your data set, the beginning of the data set must be displayed in the table field when you issue the insertrow statement.
The insertrow statement also allows you to specify the row state of the newly inserted row (see below).
The formname is the name of the form in which the table field tablename is displayed. You can express both formname and tablename as either quoted or unquoted string literals or program variables.
Row identifies one of the rows in the table field display. If row is specified, the new row is inserted after the specified row. For example, if your table field display contains five rows, then row can be any value from 0 to 5. If you insert a row, specifying a value of 3 for row, then the new row is inserted after the currently displayed row 3.
Choosing a value of 0 for row inserts the new row as the top (first) row in the table field display.
If the table field has been initialized, the value you choose for row cannot be greater than the number of displayed rows or smaller than zero. This means that regardless of how many rows the table field is capable of displaying, row can identify only a row that is currently displayed in the table field. For example, if your table field display has the capacity to display five rows, but has only two rows loaded into it, then the row number must be either 0, 1, or 2. If row is omitted, the new row is inserted after the row on which the screen cursor is currently positioned.
When a row is inserted into a table field with a data set, all rows below the inserted row are scrolled down. If the row is inserted after the last displayed row (for instance, if the table field can display four rows and row is 4), the rows above it are scrolled up, and the new row becomes the last displayed row.
The non-dynamic insertrow statement assigns values directly to columns in the table field. The list of columnnames identifies the columns receiving values and the values they are to receive. You can assign values to hidden, invisible, or displayed column in the table field; you cannot assign values to derived columns. The data type of the value must be compatible with the data type of the column. You can specify columnname using a quoted or unquoted character string literal or a program variable.
Only the specified columns are assigned values. Omitted columns are assigned either nulls, if they are nullable, or default values (blanks for character columns and zeros for numeric columns). If you omit the column list, the inserted row is blank.
By default, rows inserted by the insertrow statement have a state of UNCHANGED. If the table field has an associated data set, you can assign the state of the new row by specifying _state=value in the column list. For example, you can specify an initial row state of UNDEFINED (0) when the row is inserted; the state is set to NEW if the user types into the row.
If you want to insert a null, there are two ways to do so. First, you can specify the column's associated value as the key word null. This method assigns a null to 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. It is specified using the syntax:
insertrow (columnname = var:indicator_var)
You can only use an indicator variable when you use a variable for value. 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 insertrow 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 insert a row and specify a _state of UNDEFINED. Ingres issues a runtime warning when this happens but does assign the column values. It is never possible to assign the DELETED state when inserting a row.
By default, if you do not assign a state to the row, the inserted row has a state of UNCHANGED. (For information about the table field row states, their meanings, and how they interact, see
Table Fields.)
Each column in the inserted row (except hidden columns) has its change variable cleared (set to 0).
If the table field contains any derived columns, the FRS calculates and displays the values for those columns, if possible, when a row is inserted.
When you execute a dynamic insertrow, 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 insertrow statement, you must allocate the SQLDA, describe the table field, and allocate the necessary variables. See your query language reference guide and to your host language companion guide for complete instructions on these procedures. (The dynamic usage of insertrow is not available in QUEL.)
To turn display attributes on or off when the row is inserted, use the optional with attribute clause; specify 0 to turn the attribute off, 1 to turn the attribute on. In the case of color, specify a color code from 0 to 7. The attributes you specify are assigned to the value and scroll with the value. To assign attributes to an entire row, explicitly specify all column names. 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--insertrow statement:
Example 1:
Insert a new row as the first row in the table field display.
exec frs activate menuitem 'TopRow';
exec frs begin;
exec frs insertrow empform employee 0
(ename = :ename, sal = :sal);
exec frs end;
Example 2:
Allow the user to insert a blank row into the table field before or after the current row.
exec frs activate menuitem 'InsertBefore';
exec frs begin;
exec frs inquire_frs table empform
(:row = rowno(employee));
row = row - 1;
exec frs insertrow empform employee :row;
exec frs end;
exec frs activate menuitem 'InsertAfter';
exec frs begin;
exec frs insertrow empform employee;
exec frs end;
Example 3:
Provide a cut and paste facility, using the deleterow and insertrow statements.
exec frs activate menuitem 'Cut';
exec frs begin;
exec frs getrow (:ename = ename, :age = age);
exec frs deleterow empform employee;
cut = true;
exec frs end;
exec frs activate menuitem 'Paste';
exec frs begin;
if (cut = false) then
exec frs message
'You must select a row first';
exec frs sleep 2;
else
exec frs insertrow empform employee
(ename = :ename, age = :age);
cut = false;
end if;
exec frs end;
Example 4:
Insert a null into the spouse field and, using a indicator_var, provide the run time ability to insert a null into the title field.
exec frs activate menuitem 'NewEmployee';
exec frs begin;
exec frs insertrow empform employee
(spouse = null, title = :title:indicator_var);
exec frs end;
Example 5:
Using dynamic statements, add a new row into the user's data set. The row is retrieved from the database. Also, assign the value 1 to the hidden column fromdb to indicate that this row is from the database.
exec frs activate menuitem 'InsertNext';
exec frs begin;
exec sql fetch csr using descriptor sqlda;
/*
** Add an SQLVAR for the hidden column setting
*/
sqlda.sqld = sqlda.sqld + 1;
hide_col = sqlda.sqld;
fromdbval = 1;
sqlda.sqlvar(hide_col).sqltype = int;
sqlda.sqlvar(hide_col).sqllent = 2;
sqlda.slqvar(hide_col).sqldata =
address(fromdbval);
sqlda.sqlvar(hide_col).sqlind = null;
sqlda.sqlvar(hide_col).sqlname = 'fromdb';
exec frs insertrow :form_var :table_var
using descriptor sqlda;
exec frs end;