Using Retrieve to a Form or Table Field
The variant retrieve to a form or table field is always associated with an assignment statement. The retrieve statement itself is a database expression, because it is equivalent to the values it retrieves. The syntax for retrieve to a form or table field is shown below:
objectname := [repeat] retrieve [unique]
([fieldname = expression] | tablename.columnname
{, [fieldname = expression] |
tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[begin | {
submenu
end | }]
The retrieve statement makes up the right side of an assignment statement, while the name of the form or table field to which the retrieved values are assigned appears on the left side. If the retrieve returns no rows, then the previous contents of the field into which you are retrieving the data do not change.
There are four types of retrievals to a form or table field: the singleton query, the table-field query, the simple attached query, and the Master/Detail query. The latter two are attached queries, which use a form of the retrieve statement that includes a submenu. See Submenus.
Singleton Query
A singleton query retrieves and assigns a single row of values solely to the simple fields of the current form, without the use of a submenu. The query is stopped after the first row is retrieved, and only one row is displayed.
The following example of a singleton query retrieves a single row of values from the personnel table into a form named deptform:
deptform := retrieve (personnel.lname,
personnel.fname)
where personnel.empnum = 7;
Table-Field Query
A table-field query retrieves a set of forms and table fields from the database into a table field on a form, without the use of a submenu. The maximum number of rows that fit within the table field are displayed, and the remainder are buffered in the data set by the FRS. The user can view all the rows retrieved by scrolling through the table field.
The following is an example of a table-field query. It shows the retrieval of values from the projects table into a table field.
emptable = retrieve (projects.project,
projects.hours)
where projects.empnum = empnum;
Simple Attached Query and Submenu
A simple attached query retrieves and assigns several rows of data to the simple fields of a form. A simple attached query performs these functions:
• It attaches the as-yet-undisplayed rows to the form.
• It displays the first row in the simple fields of the form and uses a submenu to provide access to each of the subsequent rows.
• It displays a submenu of operations that can be carried out for each returned row.
• It provides submenu operations for displaying subsequent rows through a next statement. Submenus are discussed in more detail later in this section. See also
Next.
An example of a simple attached query is shown below:
"Find" =
begin
editparts := retrieve (partname =
part.partname, partno = part.partno,
descr = part.descr, cost = part.cost)
where part.partno > 2666
begin
"Change" =
begin
/*code to replace the database entry */
/*for the part with the new values */
next;
end
"Delete" =
begin
/*code to delete the displayed part */
/*from the database */
next;
end
"IncreasePrice" =
begin
cost = cost * 1.1;
end
"Next" =
begin
next;
end
"End" =
begin
endloop;
end
end;
message "Done with query";
sleep 2;
clear field all;
end
When the user chooses Find, the application displays on the form the first row retrieved, along with this submenu:
Change Delete IncreasePrice Next End
A single retrieve statement both starts the database retrieval and displays the new list of operations. 4GL combines these two actions within the statement because they are typically tied together in forms-based applications.
Master/Detail Query and Submenus
A Master/Detail query is a form of the attached query that retrieves data into the simple fields of a form (the master row) and also into a table field (the detail rows), then displays a submenu of operations that can be carried out for each master row retrieved. The first statement is the parent or master query; the second is the child or detail query.
Inclusion of a submenu offers the user a choice of operations that can be carried out for each master row retrieved, and provides the means for displaying the next master row. If the submenu is omitted, a single master row and all the details for that master are displayed at once; there is no way to see the next master row.
The Master/Detail query requires this syntax:
formname := [repeat] retrieve [unique]
([fieldname =] tablename.columnname
{, [fieldname =] tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[formname.]tablefieldname :=
[repeat] retrieve [unique]
([fieldname =] tablename.columnname
{, [fieldname =] tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[begin | {
submenu
end | }]
The only difference between this syntax and that of a simple attached query is that two queries are specified rather than one. Note that there is no semicolon between the two assignment statements. Only two assignment statements are allowed, and each must be to a form or table field.
An example of a Master/Detail query is as follows:
"Find" = begin addorders :=
retrieve (orders.orderno,
customer.custname)
where qualification (orders.orderno =
orderno, customer.custname = custname)
and orders.custno = customer.custno
partstbl := retrieve (part.partname,
orderitems.qty, part.cost)
where part.partno = orderitems.partno
and orderitems.orderno = orderno
begin
"Cancel" = begin
/* code to delete the order */
next;
end
"Old" = begin
/* code to change the order status */
/* to old */
end
"Back" = begin
/* code to change the order status */
/* to back
end
"Next" = begin
next;
end
"End" = begin
endloop;
end
end;
clear field all;
end
Each time a master row from the first query is displayed, the second query is run, based on the values from the new master. Once the master and the detail rows are displayed, 4GL prints this submenu on the window:
Cancel Old Back Next End
Choosing End returns the application user to the previous menu.
In a Master-Detail query, the where clause for the detail query is used to specify how the two queries are joined together. For instance, the detail query in the example shown above includes a reference to the orderno field, whose value was retrieved during the first query.
The value of the rowcount Ingres constant is always the number of master rows retrieved, whether or not a submenu is included. For a Master/Detail query with a submenu, the count is defined after the submenu ends and is set to the number of master records that were retrieved. The only exception to this is nested queries within submenus. See the section, Nesting Attached Queries.
Submenus
A submenu is a separate menu of choices that appears following the display of each master row in the retrieved data set for an attached query. Choosing an operation from the submenu causes the designated action to be performed, such as incrementing a salary field or displaying the next master row. Submenu operations always apply to the master row.
When a query with a submenu is executed, the query runs to completion, and a data set is created in a temporary file to hold the retrieved rows. A submenu appears at the bottom of the frame. Each next statement (associated with an item on the submenu) causes the next row in the data set to be displayed. If this is a Master/Detail query, the master query is performed as above, and another query runs to completion to retrieve the matching detail rows for each master row.
The syntax used to create a submenu is:
objectname := retrieve statement
[objectname := retrieve statement]
begin | {
initialize = begin | {
/* statements executed before first row */
/* is displayed */
end | }
"menuitem1" = begin | {
/* code for menuitem1 operation */
end | }
"menuitem2" = begin | {
/* code for menuitem2 operation */
end | }
end | }
The submenu is separated from the rest of the retrieve statement by braces or by a begin and end pair. In the syntax of a Master/Detail query, the submenu appears after both assignment statements.
Submenus can contain all the same components as main menus. Generally, at least one of the menu operations should contain the 4GL next statement, which causes the next row retrieved to be displayed.
The user stays within the submenu until either (1) a next statement is executed after all the rows have been displayed, or (2) an endloop statement is executed. When either situation occurs, control returns to the statement immediately following the assignment statement that contained the submenu.
The following example of a simple attached query retrieves a series of row-typed values from the database and creates a submenu for the current form:
range of e is employee;
empform := retrieve (e.lname, e.age, e.empnum)
where e.empnum <=10
begin
initialize =
begin
/* Statements here are executed once */
/* before the first row is displayed */
message "Now within submenu";
sleep 3;
end
field "lname" =
begin
message "You just entered value: " +
lname;
sleep 3;
resume next;
end
"Increment" =
begin
age := age + 1;
end
"NextRow" =
begin
next;
end
"End" =
begin
endloop;
end
end;
When the user chooses an operation in the main menu containing this assignment statement, the following submenu appears at the bottom of the window:
Increment NextRow End
The first row is retrieved in the three fields specified. Each time the user chooses the NextRow operation from the submenu, the next row is displayed. When there are no more rows, the message "No more rows" appears at the bottom of the frame, and the user exits from the current submenu. The main menu for the frame is again displayed.
If no rows in the database satisfy the query, the message "No rows retrieved" appears at the bottom of the frame, and control is transferred to the statements immediately following the assignment containing the submenu.
The submenu definition can contain any legal operations, including menu, key, and field activations. The first submenu operation definition can be an initialize block. Unlike the initialize block for a frame, the initialize block in an attached query cannot contain variable declarations. The initialize block for the attached query is executed only once, after the master row and matching details have been selected from the database and before their values have been displayed. Both 4GL and QUEL statements can appear in the initialize block.
The code in the submenu can contain any legal 4GL code. This includes query statements, such as replace and delete, as well as other assignment statements involving retrieves. A return or exit statement within the submenu has the same effect as a corresponding statement from the main menu of the frame. The resume and validate statements operate in the same manner as they do in the main menu; however, if they close the current operation, the submenu is still displayed.
While the submenu is running, the value of the rowcount FRS constant is undefined. Rowcount only has a value when the submenu ends. The single exception is when a query statement in the submenu code is executed, then rowcount is set for that query statement.
Note that when the assignment statement containing the submenu is executed, Application By Forms displays the current form in update mode. This mode can be changed in the initialize section of the submenu. When the end user exits from the submenu, the display mode in effect prior to the submenu again takes effect.
Nesting Attached Queries
4GL provides the capability to nest attached queries to any level. Each nested query is placed within the submenu of the higher-level query. Thus, the following syntax is valid:
formobject := database_expression
begin
"menuitem" =
begin
formobject := database_expression
begin
{submenu}
end
end
end;
For nested queries with a submenu, the value of the rowcount FRS constant is undefined while the submenu is running. Rowcount only has a value when the submenu ends. The single exception when a query statement in the submenu code is executed; then the rowcount is set for that query statement.