# CHAPTER 4: Data in Tables

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.

## 4.1 Opening and Closing Tables

### 4.1.1 OPEN Statement

Most applications access data within a database at some point. In order to do this, you must open the tables that the application needs to access. The script statement to perform this is:

       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.

### 4.1.2 Table

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.

### 4.1.3 Mode

When opening a table, it is important to consider the mode that is to be used. There are three possibilities:

- read
- update
- deferred

With a table opened in `read` mode, you cannot perform any update operations against the tables. Only selection operations will be allowed. This is suitable for a browsing application that will only be used for queries.

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.

### 4.1.4 Table Instances

As well as the mode of a table, the table instances are also important. When you open a table, a table instance is created. By default this instance is given the same name as the table. However, you can override this name by using the `AS tabinst` option to the `OPEN` statement. In fact, you can open several instances for a table by specifying a list of table instance names.

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.

### 4.1.5 CLOSE Statement

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.

## 4.2 Selecting and Contexts

Selecting records from the database is an important part of an Empress 4GL application. The `SELECT`, `next_rec`, and `prev_rec` statements are the statements that accomplish this.

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.

### 4.2.1 WHERE Clause

A `SELECT` statement can have a `WHERE` clause to restrict the records selected or create a proper join. A useful feature of Empress 4GL is that the `WHERE` clause can contain comparisons between attributes and fields. If the value of the field is null at the time of the select, then the condition is ignored in the `WHERE` clause. This allows you to let the user select on different criteria without programming all the various combinations.

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.

### 4.2.3 SORT Clause

There is also a `SORT` clause that lets you determine the order in which the records will be retrieved when using the `next_rec` and `prev_rec` statements. It is the same as the Empress SQL version of the `SORT` clause. The syntax is as follows:

       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.

### 4.2.5 Moving through a Context

When a `SELECT` statement executes, there is no current record defined for the table instances involved. The `next_rec` and `prev_rec` statements are used to set the current record. The first `next_rec` will make the first records in the appropriate selected contexts current. Each following call will move the current records as necessary.

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 context

This 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' = 10

In 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.

### 4.2.7 Defining a Context

A `SELECT` statement will often be used by several different scripts or will need to be available before its context is actually selected. Both the `DEFINE SELECT` and `SELECT` statements are used for this.

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.

## 4.3 Accessing Attributes

Within an Empress 4GL application it is necessary to access various attributes from the tables. The attributes will sometimes be assigned values that will eventually be used to modify the tables and other times they will be used to obtain the values from the tables.

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.attr

As 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.

### 4.3.1 Assigning and Retrieving Values

The simplest way of using an attribute value is to have the `LET` statement assign its value to another variable or to the attribute. However, there are Empress 4GL functions which allow you to retrieve and set attribute values in ways that are more flexible.

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)

## 4.4 Updating Tables

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.

### 4.4.1 INSERT Statement

The `INSERT` statement will take a list of table instance names as arguments. It has the following syntax:

       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.

### 4.4.2 UPDATE Statement

To change an existing record, you must use the `UPDATE` statement. The syntax of the statement is similar to the `INSERT` statement.

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.

### 4.4.3 DELETE Statement

The final updating statement is the `DELETE` statement which has the following syntax:

       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.