Passing a Query
4GL allows a query to be passed as a parameter to a form, utilizing the retrieve statement.
Use this syntax for the parameterlist to pass values from a database table:
calledformname := retrieve [unique]
([fieldname =] tablename.columnname
{, [fieldname = ] tablename.columnname})
[where qual]
[sort [by] sortlist]
calledformname
Specifies the name of the form in the called frame
fieldname
Specifies the name of a simple field or table field in the called frame
tablename
Specifies the name of the database table from which values are being retrieved
columnname
Specifies the name of a column in the database table. If columnname is the same as fieldname, including fieldname is not necessary.
qual
Specifies a qualification clause or other logical expression that restricts the scope of the database query. The logical expression can not include 4GL names.
sortlist
Specifies a list of column names by which to sort the results of the retrieve statement
Only one query is allowed in the parameter list. Master/Detail queries cannot be passed as parameters.
A query passed as a parameter is similar to a query with a submenu. When the query is executed, it runs to completion, and the retrieved rows are read into a temporary file. Each next statement in the called frame causes the next row from the temporary file to be displayed.
Use the qualification function in a where clause, and enclose the entire where clause in a variable.
The section on the retrieve statement contains more detailed information on writing queries. However, some special rules apply when writing queries to be passed as parameters. To write the results of a query to a table field you must reference both the form and the table-field name, as in the example:
callframe newframe (newform.personnel :=
retrieve ...) ;
Place the names of 4GL fields in the called frame on the left side of the target list, while names of components in the calling frame must appear on the right in the where clause. For example, consider the callframe statement:
callframe newframe (newform := retrieve
empnum = projemps.empnum
where projemps.projnum = :projnum) ;
Here Empnum is the name of a field in the called frame and therefore appears on the left side of the target list, while Projnum is a field in the calling frame and appears on the right in the where clause. 4GL does not generate errors until run time if fields in the left-hand side do not exist in the called frame.
To All Simple Fields from the Database
Use the syntax below for the parameterlist to retrieve values from the table into all the simple fields of the called form:
calledformname := retrieve [unique] (tablename.all)
[where qual]
[sort [by] sortlist]
To use the option all, you must be sure that each simple field in the called form corresponds by name to a column in the database table. There can be columns in the database table not corresponding to fields, but not vice versa.
To a Table Field from the Database
Use the syntax below for the parameterlist to pass values from a database table to a row (or rows) in a called table field:
calledformname.tablefieldname := retrieve [unique]
([tablefieldcolumn =] tablename.columnname
{, [tablefieldcolumn =] tablename.columnname})
[where qual]
[sort [by] sortlist]
calledformname
Specifies the name of the form on the called frame
tablefieldname
Specifies the table field in the form on the called frame
tablefieldcolumn
Specifies the name of a table-field column on the called form to which database information is being assigned
The tablename, columnname, qual, and sortlist parameters have the same definitions as in the previous section.
If the database column has the same name as the table-field column, you need not specify tablefieldcolumn explicitly.
To Table-Field Columns from the Database
Use the following syntax for the parameterlist to retrieve values from the database table into all the columns of a table field:
calledformname.tablefieldname := retrieve
[unique] (tablename.all)
[where qual]
[sort [by] sortlist]
All parameters have the same definitions as in the previous sections.
To use the option .all, you must make sure that each column in the table field corresponds by name and data type to a column in the table. The database table can contain columns that do not correspond to columns in the table field, but not vice versa.
Examples
Call Newframe and pass values into the Projnum and Name simple fields in Newform from the database:
callframe newframe (newform := retrieve
(projinfo.projnum,
name = projinfo.projname)
where projinfo.projnum = projnum);
Call Newframe, passing values into the Projnum simple field and the Personnel table field of Newform. The example retrieves information from the Projemps table concerning all employees assigned to a particular project. The Personnel table field has at least the columns Empnum and Hours:
callframe newframe (newform.projnum :=
projnum;
newform.personnel := retrieve
(projemps.empnum, hours = projemps.emphours)
where projemps.projnum = projnum);
Call Newframe, placing a value in the status field upon returning to the calling frame:
status := callframe newframe;