getoper (function) Statement—Get Comparison Operators
This statement gets comparison operators from fields and columns displayed in query mode.
Syntax
getform formname (oper_variable = getoper(fieldname))
getrow formname tablename [row]
(oper_variable = getoper(columnname))
Description
The getoper function returns an integer code indicating which comparison operator was entered into a form field or table field column. When a form or table field is in query mode, users can enter comparison operators, such as > or <, into the form's fields or table field columns. In addition, the user can enter data into fields. The program uses the operators and data entered by the user to build a query. While the actual data can be retrieved for the query with any statement that retrieves data from a form field or table field row, you must use the getoper function, in conjunction with these statements, to retrieve comparison operators.
The getoper function can appear within the context of all the forms statements that retrieve data from a form field or a table field column. Those statements are:
• getform
• finalize
• getrow
• unloadtable
For a discussion of the syntax of each of these, see their individual statement descriptions. The syntax does not differ when the getoper function is used with these statements.
There are three problems in constructing a query based on what the user has entered on a form:
• The program must find out whether a user has entered any data in the fields or columns. Any fields that the user left empty must be ignored in constructing retrieval qualifications (where clauses).
• The program must be able to find what operator, if any, the user typed alongside the data in a particular field or column.
• Given the data in the field or column, the program must construct a where clause to qualify a database retrieval.
The getoper function provides the solution to the second problem. This function returns to the integer variable oper_variable a query operator code that represents the operator it finds on the specified field or column. The following are the codes that getoper returns:
If a user types data without an explicit operator, this implies an operator of =, and getoper returns the value 1. If the form is not displayed in query mode, the value 0 is returned.
The getoper function can appear in a forms statement together with the retrieval of other objects. In fact, in most cases, a program retrieves both the operator and value of a particular field or column in a single statement. For example,
exec frs getform empform
(name_oper = getoper(ename), vname = ename,
age_oper = getoper(age), vage = age);
The information returned by getoper, together with the other values retrieved from the form, can be used by a program to construct a character string that represents a qualification.
Getoper causes the FRS to perform data type checking on the field. If the data in the field is not the correct data type, for example, if the user has entered character data in a numeric field, then a runtime error is issued and the user variable is not updated. However, program flow is not affected by data type checking errors.
When you initialize a table field in query mode, the FRS automatically takes care of the bookkeeping chores when the table field scrolls. For example, if a number of rows are displayed, and the user scrolls in a new row, the first row with its data and its comparison operators are scrolled up. Consequently, when the getoper function is used with the unloadtable statement, the columns and operators being retrieved need not be currently displayed. A general rule to follow when constructing queries from the getoper function used within an unloadtable statement is to qualify columns and or rows.
For example, the following data set constructs the specified qualification:
Qualification:
where (e.eno <= 20) or
(e.sal > 50000) or
(e.eno >= 40 and e.sal < 35000)
If a table field is not in query mode, then the value 0 is returned and a runtime error is issued.
When the getoper function is used in conjunction with a Dynamic FRS using clause and its associated SQLDA, the function name is abbreviated to gop.
Example—getoper (function) statement:
Example 1:
Build a query using the getoper function. This example uses a query operator array that can map the integer codes of FRS query operators to their corresponding query language operators. Array subscripts begin at 1.
exec sql begin declare section;
oper_chars array(6) of character_string(2);
oper integer;
eno integer;
enum character_string(10);
where_clause character_string(30);
exec sql end declare section;
oper_chars = ('=', '!=', '<', '>', '<=', '>=');
exec frs display empform query; /* Use query mode */
exec frs activate menuitem 'Retrieve';
exec frs begin;
exec frs getform empform (:eno = eno,
:oper = getoper(eno));
/*
** assign value in eno to enum and convert
** type to character
*/
if (oper > 0) then
/* Construct where clause like:
** employee.eno = 18
*/
where_clause = 'employee.eno '
+ oper_chars(oper) + enum;
else
/* Nothing entered, use 'truth' default */
where_clause = '1 = 1';
end if;
exec sql select eno, ename, age
into :eno, :ename, :age
from employee
where :where_clause;
exec sql begin;
process rows;
exec sql end;
exec frs end;