Passing a Query

4GL allows a query to be passed as a parameter to the called form or to a table field in the called form, utilizing the select statement. For syntax and parameters, see Select to a Complex Object.

A query passed as a parameter to the called form is similar to an attached query, except that the query submenu is the activation list for the called frame. When the query is executed, it runs to completion, and the selected 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.

You can use the qualification function in a where clause or enclose the entire where clause in a variable.

The section on the select statement contains more detailed information on writing queries. However, some special rules apply for queries to be passed as parameters.

To write the results of a query to a table field, include both the form and the table-field name, as in the example:

Also, you must place the names of fields in the called frame on the left side of the target list, while names of fields in the calling frame must appear on the right in the where clause. For example, consider the callframe statement:

callframe newframe (newform := select
empnum = empnum from projemps
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.

Important! If fields in the left-hand side do not exist in the called frame, 4GL does not generate errors until run time.

Queries passed to another frame, like any other query, are always part of a transaction. Shared locks remain on the selected data until the next commit or rollback statement. You can override this default and release locks on the selected data by one of the following methods:

• If your application has previously issued a set autocommit on, then a commit automatically takes place after every successfully executed query.

• If you have turned off read locks on the table from which you are selecting via the set lockmode statement, then locks are not held on the table from which you selected. However, a transaction is still in progress.

See the SQL Reference Guide for detailed descriptions of the set lockmode statement.

Call Newframe with style pop‑up, passing a value from the Projnum simple field of the calling frame to the Projnum simple field in the called frame. Because the value in the calling frame is not passed by reference, it is not affected by changes made to the corresponding field in the called frame.

Call Newframe, passing a value from the Employee simple field in the calling frame to the Employee simple field in the called frame. Because the value in the calling frame is passed by reference, it is affected by changes made to the corresponding field in the called frame.

Call Newframe, passing values from all columns in the current row of the Oldtable table field to corresponding simple fields in Newform:

Call Newframe and pass values into the Projnum and Name simple fields in Newform from the database:

callframe newframe (newform := select
  projnum, name = projname
  from projinfo
  where 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 := select   empnum,
  hours = emphours
  from projemps
  where projnum = :projnum);

Call Newframe, placing a value in the Status field upon returning to the calling frame:

Call a report frame, passing in parameters:

deptno = 2;
lname = 'Smith';
callframe reportframe (name = lname;
deptno = deptno);

Call Newframe, passing the name attribute of the third record of Emparray to the corresponding simple field: