The main purpose of Empress 4GL is to provide an easy interface between the end user and the tables in the database. An application will "communicate" with users to find out what they want to do and communicate with the database to accomplish the user's commands. This next section will discuss the interface between the application and the database.
OPEN table mode [as tabinst {, tabinst}]
You can use this statement as part of any script although the most common practice is for you to open all tables within the application enter script or a subscript that the enter script calls.
When specifying the name of the table, the syntax is [DB:]table, the same as it is in Empress SQL. Any expression (e.g., a variable) can be used to represent the table name.
However, in order for you to perform any operations that modify the data in the table, you must open the table in update or deferred mode. Both these modes let you perform update operations, but they handle locks differently.
When working with records in a table that have been opened in update mode, all locks will be exclusive locks. No other process will be able to retrieve those records.
Often it would be better to create shared locks until an exclusive lock is actually needed to perform an update operation. This is exactly what a table opened in deferred mode does. The deferred mode does require extra overhead, but with memory resident locking this is not a major cost.
Several OPEN statements may use the same table provided that they all use different table instances. In this way the same table can be opened with different modes. For example:
OPEN 'table' update aS 't1'or
OPEN 'table' read aS 't2'A table instance has several things associated with it. First, it has a buffer where it stores one record from the table. Second, it stores information to tell it which records have been selected from it (called the selected context) and which of those records is most current. Two different instances of the same table can have completely different selected contexts and current records.
As an example, consider a database which has information on both suppliers and customers in the same table. In an application that lets you select customers from the table, you can add a function to select all the suppliers that have been used to service a customer. If there was only one table instance, selecting all the suppliers would wipe out the selected context and current record that was being used for the customers. However, with two table instances, you can select all the suppliers with the second instance and not affect the customers instance. Keep in mind that the second instance (for suppliers) can be a read mode if the user should be restricted to browsing the suppliers, while the first instance (for customers) can allow updates. This would require two separate OPEN statements.
When an application no longer needs a table instance, you may close it. The CLOSE statement has the following syntax:
CLOSE tabinst {, tabinst}
You do not need to close a table instance within an Empress 4GL
application since this is done automatically upon exiting. However, closing
a table instance does allow it to be reopened in a different mode. It also
makes management of open files easier for Empress 4GL to
handle.
The SELECT statement is similar to the Empress SQL SELECT statement. However, in Empress 4GL, the SELECT statement does not display records. Instead, it defines the selected context of records for each of the table instances that are involved.
The syntax of the SELECT statement is:
SELECT [|DISTINCT|] [|* |]
|ALL | |select_item (, select_item)|
FROM tabinst [[ALIAS] aliasname {, tabinst [ALIAS] aliasname]}
[WHERE clause]
[GROUP BY attr {, attr} [HAVING clause]]
[SORT clause]
[WITH [DISPLAY] COUNT]
[BECOMES [CONTEXT] context];
Note that you can specify more than one table instance. This allows joins to be used in Empress 4GL applications. When you use a join, the appropriate contexts are selected from each of the table instances involved. Then, when you use the next_rec and prev_rec statements, the appropriate records are made current within each of the selected contexts.
For example, with the SELECT statement that follows, the user can select based on the first name, last name, both or neither:
SELECT FROM 'name_table'
WHERE 'name_table'.'first' = 'window'@'first' AND
'name_table'.'last' = 'window'@'last'
4.2.2 GROUP BY and HAVING Clauses
The GROUP BY and HAVING clauses of a SELECT statement allow the application
to work with a group of records rather than single records. This makes
aggregate functions which calculate maximum, minimum, sum, average or count
values available.
The GROUP BY will have a list of attributes which are used to sort the records into groups. The HAVING clause will limit the groups that are chosen in a fashion similar to the way the WHERE clause limits which records are chosen.
The syntax of the GROUP BY and HAVING clauses is the same as for the Empress SQL.
For example, to select the average salary for all departments having the minimum salary above $50,000, you would do the following:
SELECT AVERAGE ('personnel'.'salary')
FROM 'personnel'
GROUP BY 'personnel'.'department'
HAVING MIN ('personnel'.'salary') > 50000
BECOMES 'pers_context'
This would sort all the records in department order (equivalent to SORT BY 'personnel'.'department') and then take the minimum salary out of each department. For those departments where the minimum is greater than $50,000, the average salary can be displayed on the screen.
SORT BY attr [|ASC[ENDING] |] {, attr [|ASC[ENDING] |]}
|DESC[ENDING]| |DESC[ENDING]|
4.2.4 COUNT Option and Context Name
There are two parts of the Empress 4GL version that are not
part of the Empress SQL version. The first of these is the
with COUNT option. Adding the with COUNT option will set an Empress
4GL variable called records_selected. This variable is
based on the number of records that meet the conditions of the select.
It can have a positive value to indicate the number of records that have
been selected, a zero value to indicate that no records have been selected,
or a negative value if it is not possible to get an accurate count.
The last possibility is the result of records that may or may not be part of the selected contexts being locked. There is no way to tell if they should be counted or not.
If the DISPLAY option is added in addition to the variable, a message appears with each next_rec or prev_rec statement. The message will indicate which record of the set is current. You may see something like Record 3 of 15.
The second part of the SELECT statement is the BECOMES option. This allows you to name the selected context of records. By default, the name of the context is the name of the table instance used in the SELECT. However, the name of the context is different for various reasons. For example, you may wish to use a naming convention that differentiates between table instances and selected contexts or when performing a join operation dealing with multiple instances.
The syntax for the next_rec statement is:
next_rec context [number]The syntax for the prev_rec statement is:
prev_rec context [number]The context has the name defined by the SELECT statement. The selected contexts of all the table instances involved are advanced by the appropriate number of records to arrive at the next record in the SELECT. If a number is supplied the statement is repeated by that number of times. Note that the number can be any expression that evaluates to an integer.
As an example, if you want to skip 10 records from the current record:
next_rec 'example_table' 10
4.2.6 Removing a Context
There are other statements that are useful when you deal with records in
tables. One of these is the end_select.
end_select contextThis statement frees all of the selected contexts involved with a SELECT statement so that the table instances can be used in another SELECT. The end_select statement is not necessary if the same table instances are to be used over for the same context. But, if they are to be used in another context then the old context must end.
Consider the following examples.
SELECT FROM 'ex_t1', 'ex_t2'
WHERE 'ex_t1'.'key' = 'ex_t2'.'key'
BECOMES 'ex_join'
SELECT FROM 'ex_t1'In this example, the second SELECT will fail because the ex_t1 table instance is already in use by the ex_join context. There are two solutions to this problem. The first is to end the ex_join context before the second SELECT freeing ex_t1 for use. However, if it is necessary to keep the ex_join context, then the second SELECT could use a second table instance (defined by the OPEN statement) which is not part of the first context.
This is another example:
SELECT FROM 'ex_t1'
WHERE 'ex_t1'.'key' = 'window'@'key_field'
SELECT FROM 'ex_t1'
WHERE 'ex_t1'.'key' = 10In this example the second SELECT statement will work. However, it will end the context selected by the first one and replace it.
Note
The end_select is not needed in this case as Empress 4GL assumes an end_select if the same table instances and context are used, even if the WHERE and/or SORT clauses are different.
The DEFINE SELECT is an ordinary SELECT statement preceded by the word define. This will not select any records, instead, it will define a context which you can use to define a multi for multi-record applications.
Keep in mind that when using expressions in a WHERE clause, all variables must be globally defined so that they will be available when the selection is actually done. They do not have to have a value at the time of the definition. However, when you use expressions to represent table, attribute, or field names, the expressions are evaluated at the time of the definition of the SELECT and should evaluate to a legitimate value.
When you use a defined context, the SELECT statement will actually execute the SELECT and retrieve the context of records you need.
The best place to define a multi is in the application enter script for a multi-record application. However, the multi requires a context as one of its attributes. In this example, the context is defined so that the multi can be defined. However, the actual selection of records is not done until the user presses the appropriate key.
Application Enter Script:
...
DEFINE SELECT FROM 't1', 't2' WHERE
't1'.'key' = 't2'.'key' AND
't1'.'name' = 'win1'@'name' AND
't1'.'num' = 'win1'@'num'
BECOMES 'con1'
CALL define_multi ('t1', 'win1', 0, 1, 'ds', 'ls', 'fs')
...
Key Script for SELECT key:
...
SELECT 'con1'
CALL display_page ('con1')
...
Note that the same WHERE clause will be used each time SELECT 'con1' is encountered, although if you change the values of the name and num fields, different sets of records will be selected.
The attributes are treated as special Empress 4GL variables. They are the same as global variables because they are available throughout the application; however, you do not have to define them in any of the scripts since the table definition takes care of that. The naming convention is important. When using an attribute as a variable, this is the notation:
tabinst.attrAs mentioned earlier, each table instance has an allocated buffer that stores the values for one record from the table. The table instance name lets Empress 4GL know what table instance to take the value from or enter the value into.
Note
The table instance name is not optional as in Empress SQL.
The attr_update function takes three parameters: the table instance, the attribute name, and a value to assign to it. It does the same job as an assignment statement, but it is more flexible. This is because you can specify the table instance or the attribute name as an expression. In this way, generic applications can be developed where variables are used to represent the names of table instances, attributes, etc. Variables can also be used to represent table instance and attribute names in SELECT statements to accommodate this feature.
For example, LET 't1'.'name' = "Fred" can be re-written as:
CALL attr_update ('t1', 'name', "Fred")
As well, the following example would achieve the same result:
LOCAL
INTEGER tab_num;
CHAR attr_name;
CHAR value;
END;
LET tab_num = 1
LET attr_name = 'name'
LET value = "Fred"
CALL attr_update ('t' CONCAT CONVERT tab_num TO
CHAR, attr_name, value)You may need to do some more programming for the last example, but it does demonstrate how to use variables and expressions. This is handy for generic applications which will handle various different tables with the same functions.
The opposite function is the attr_value function. It returns the value of the attribute when given the table instance and attribute name as parameters. It can be used as part of any expression, such as the righthand side of an assignment or parameter to a function call.
For example, LET var1 = 't1'.'name' can be represented with the statement:
LET var1 = attr_value ('t1', 'name').
Variables and expressions can be used as well. For example, to assign the
value of the attribute that has the same name as the current field, use
the statement:
LET var1 = attr_value ('t1', current_field_name ()).
Aside from these functions, there are two other special functions which
will make the transfer of data between fields and attributes easier. If
the field names in a window correspond to the attribute names in the table
instances that are used in a selected context, you can use the following
statements:
set_attr_values (window, context [, field_num])and:
set_field_values (window, context [, field_num [, override]])Each use the window name and the selected context as parameters. The field number is an optional third parameter for both and an override value is the fourth parameter for the set_field_values function.
These functions transfer the values from the attributes to the field which has the same name in the first case and the other way in the second. Any fields that do not correspond to an attribute in the context will be set to null for the set_field_values function while the attributes for which there is no field will be set to null for the other.
These functions make it very simple to design flexible applications. You need only name the fields the same as the attribute names and data can be transferred back and forth with ease. It also makes it easier to add, change, or remove attributes since only the field definitions and form have to be changed. The rest of the functions will accommodate the change.
The field number parameter is used to deal with multi-record displays and tells Empress 4GL which line of the field to place the value in. If it is left out or a zero is entered, Empress 4GL will use the line number of the current field.
The field number must be specified when a particular line is to be accessed or when an override value should be given. An override is a value that is to be put into all of the fields rather than the values that are in the buffer. The most commonly used override value is null which used to clear the fields. The following will clear the current line on the window called "window" (or the whole screen if the fields are not multi-fields):
CALL set_field_values('window', 'context', 0, NULL)
Three different types of update operations can be performed on a table: adding new records, updating existing records and deleting existing records. There are Empress 4GL statements for each.
INSERT tabinst {, tabinst} [FROM func ([expr {, expr}])]
When this statement is encountered, the optional function is executed and
the values in the buffers for the listed table instances will be added
as new records in the tables to which the table instances refer.
The INSERT function disregards any selected context the table instance may be involved in. If there is no context, the insertion will take place without difficulty. However, if there is a context, the insertion will still take place but there will be some implications.
Note
The context will not be disturbed as far as the current record and selected context are concerned, but the buffer changes because this is a new record. The values on the screens will also change to correspond to the values in the buffers.
This can present problems as the current record is not appearing on the screen. If users change the values on the screen and update the record, they will be updating the current record rather than the one that was just inserted.
To avoid this happening, use the DISABLE statement. This makes it impossible for users to update or delete from the table instances involved in a context. It is a useful function after an insertion or clearing operation, unless the context is going to be ended or reselected.
After a context has been disabled, you can restart it with ENABLE. A next_rec or prev_rec call against the context will automatically call the ENABLE is usually followed by statements to get the screen and current record back in synchrony.
With the UPDATE statement, it is necessary for each table instance to have a context with a current record. This means a successful SELECT statement followed by at least one next_rec statement must have been previously issued. Also, the context cannot be disabled. When the UPDATE statement is issued, the contents of the current buffer for each of the table instances is used to overwrite the current record for that instance.
DELETE tabinst {, tabinst}
Note that there is no function option as there was with the INSERT and UPDATE statements. This is because the DELETE disregards what is in the buffers. You only need a current record. For each table instance, the current record is marked as "deleted" and the space that is allocated for that record will be used later by an insertion into the table.