getrow Statement—Get Values from a Row

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.)

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;

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;

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;

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;