getrow Statement--Get Values from a Row
This statement gets values from a table field row.
Syntax
Non-dynamic version:
getrow formname tablename [row]
(variable[:indicator_var] = columnname
{, variable[:indicator_var] = columnname})
Dynamic version:
getrow formname tablename [row]
using [descriptor] descriptor_name
Description
The getrow statement transfers values from columns in a table field row into program variables. It does not change any of the values in the row.
The formname is the name of the form in which the table field tablename is displayed. Both can be expressed as character strings, with or without quotes, or as program variables.
The row is the number of the displayed row from which the data is to be transferred. Row can be either an integer literal or an integer program variable. Row cannot be less than 1 nor greater than the number of currently displayed rows. For example, if the table field can display 7 rows of data, but currently only shows 5, then row can have a value ranging from 1 to 5, inclusive. If it is omitted, the values are taken from the row on which the screen cursor currently rests.
The getrow statement transfers values from the specified columnnames of the table field row into program variables. Each columnname can be expressed as a character string, with or without quotes, or as a program variable. The variable must be of a type appropriate to the column.
You must include the indicator variable if the column is nullable. If the retrieved data is null, the indicator variable is set to -1. (If the retrieved data is null and an indicator variable is not present, Ingres returns an error.)
Indicator variables also enable your application to detect string truncation. When the retrieved character string is larger than the variable to which it is assigned, the string is truncated. In such instances, if an indicator variable is present, it is set to an integer indicating the full, untruncated length of the character string.
A column can be a displayed, invisible, derived, or a hidden column. If the table field is associated with a data set, the column can also be one of the special constants _record or _state. The _record constant returns an integer representing the row's record number in the data set, with 1 signifying the first row in the data set. The data set record number is independent of the row's position in the table field display. The _state constant returns an integer corresponding to the row's state (NEW, UNDEFINED, CHANGED, and so forth). The state indicates the most recent event that has occurred to a row's displayed columns. For instance, a row that was originally loaded by the program and has since been updated by the runtime user has a state of CHANGED, signified by the value 3. Programs often use a row's state when updating a database table from the values in the table field. For a complete description of row states, see
Table Fields.
The dynamic version of the getrow statement transfers values from columns in a table field row to variables pointed at and described by descriptor_name. Descriptor_name identifies an SQL Descriptor Area. The SQLDA is a host language structure allocated at run time. The actual structure name is not required to be SQLDA and can be differently defined by the program.
Your program must describe the table field and allocate the variables pointed at by the SQLDA before it can issue a dynamic getrow statement. Read the SQL Reference Guide and your host language companion guide for information about the structure, allocation, and use of the SQLDA.
The getrow statement causes the FRS to validate the column before retrieving the values in the column. If the validation fails on any column, a runtime error results and the associated user variable is not updated. However, program flow is not affected.
Getrow does not return operators entered into forms in query mode; for example, if the user entered >1000, getrow returns 1000. To obtain the operator (>) you must use the getoper statement.
(The dynamic version of the getrow statement is not available in QUEL.)
Examples--getrow statement:
Example 1:
Get information from the first row of the table field display.
exec frs activate menuitem 'GetFirst';
exec frs begin;
exec frs getrow empform employee 1
:eno = eno, :ename = ename, :age = age,
:sal = sal, :dept = dept);
exec frs end;
Example 2:
Find out if the current row has been modified.
exec frs activate menuitem 'RowChanged?';
exec frs begin;
exec frs getrow empform employee
(:ename = ename, :state = _state);
if (state = 1 or state = 3) then
/* New or changed */
msgbuf = 'You have modified/added employee '
+ ename;
exec frs message :msgbuf;
exec frs sleep 2;
end if;
exec frs end;
Example 3:
Get values from a nullable column in a table field.
exec frs activate menuitem 'MoreInfo';
exec frs begin;
exec frs getrow empform employee
(:ename = ename, :age = age,
:spouse:indicator_var = spouse);
/* -1 means no spouse or children */
if (indicator_var <> -1) then
find information about children,
if applicable;
end if;
Display more detailed information on retrieved
employee;
exec frs end;
Example 4:
Using dynamic statements, describe a table field, retrieve a row together with the _state variable, and if it is an original row, delete the row from the database and the table field.
exec frs describe table :form_var :table_var
into sqlda;
...
exec frs activate menuitem 'Delete';
exec frs begin;
/* Add an SQLVAR for _state retrieval */
sqlda.sqld = sqlda.sqld + 1;
state_col = sqlda.sqld;
sqlda.sqlvar(state_col).sqltype = INT;
sqlda.sqlvar(state_col).sqllen = 4;
sqlda.sqlvar(state_col).sqldata =
address(state_var);
sqlda.sqlvar(state_col).slqind = null;
sqlda.sqlvar(state_col).sqlname = '_STATE';
exec frs getrow :form_var :table_var using
descriptor sqlda;
if (state_var = 3) then
/* Deleting an original row */
/* Use the SQLDA (minus the _state variable)
** for Dynamic SQL
*/
sqlda.sqld = sqlda.sqld - 1;
exec sql execute delete_stmt using
descriptor sqlda;
end if;
/* Delete the row from the table field */
exec frs deleterow :form_var :table_var;
exec frs end;