To Previous Section

## 4.7 Accessing the Database

The application that was created in the previous section does not access the database. In this section, we will show you how to retrieve information from the database.

Empress 4GL provides tools, the Default Application Generator and the Application Generator, which can automatically generate full screen interface applications capable of accessing the database and presenting the information in a visual manner. We will use these tools to show:

- how you can create applications without coding;
- how to associate data in the database with the application.

The applications created by these tools are Empress 4GL applications. You may modify and customize the forms and the scripts to suit your needs.

### 4.7.1 Default Application Generator

From the Empress 4GL `Main` Menu, press the `Application Manager` key.

Figure 4-50 Application Manager

Press the `Menu 2` key to display the second page of the key menu.

Figure 4-51 Menu 2

Press the `Default` key to generate the default application.

Figure 4-52 Default Application

We want to create an application called `employee` which allows us to manipulate data from the `personnel` table.

To do this, type in `personnel` as the table name, `employee` as the application name.

Figure 4-53 Default Application

Note: So, if you had created a previous application called `employee`, you could not use that name again here. Empress 4GL will not allow two applications with the same name.

Hit <Return> and you will see the newly created application named `employee` listed in the Application Manager.

Figure 4-54 Application Manager

Press the `Menu 1` key to return to the first key menu. Move your cursor to `employee` and press the `Run` key to see the application built by the Empress 4GL Default Application Generator. In this application, you can insert, delete, update and browse through the `personnel` table.

Figure 4-55

For the rest of this section, we will show you the complete script for the `employee` application.

#### 4.7.1.1 Form

The Default Application Generator will create a form based on the table definition of `personnel`. Each field is named and labeled by its corresponding attribute name. The length of each field is calculated from the data type of the attribute in the database.

Figure 4-56 `employee` Form

#### 4.7.1.2 Application Enter Script

When the `employee` application is run, the first script to be executed is the Application Enter Script which is defined in the APPLICATION TABLE.

Figure 4-57 Enter Script

The following are the detailed explanations of each statement in the APPLICATION TABLE Enter Script.

    set call set_key_values ('sys_default_menu');

Empress 4GL provides you with a default menu as shown in the application. This statement assigns the key label (e.g., `F1`) to each corresponding field in the menu (e.g., select).

    call show_window ('sys_default_menu');

This statement displays the window `sys_default_menu` (which is defined in the WINDOW TABLE) for the function keys.

Figure 4-58 Definition of `sys_default_menu` Window

    call show_window ('employee');

This statement displays the window `employee` which is defined in the WINDOWS TABLE.

Figure 4-59 Definition of `employee` Window

Empress 4GL allows you to display as many windows as you wish on the screen through the `show_window ()` statement.

    call current_window ('employee', 'number');

When there are multiple windows displayed on the screen, there can be only one window that is active. This window is called the current window and only the function keys defined for that window will respond. In this statement, it defines the window `employee` and field `number` as current. The cursor is positioned in the current field.

    open 'personnel' updates;

This statement opens the `personnel` table for update.

    select from 'personnel'
       with display count becomes context 'employee';

This statement selects data from the `personnel` table counts the number of qualified records, shows the message of "`record n of n`" for each record and names the select context of selected records `employee`.

    next_rec 'employee';

The `next_rec` initializes each attribute value from the next record in the select context `employee`.

    call set_field_values ('employee', 'employee');

If a field(s) name in the form has the same name as an attribute name in the table, this statement will do an automatic assignment of the attribute value to the field value. This one statement is equivalent to:

    let 'window'@'field' = 'context'.'attribute'

for each field in the form where `"field_name"="attribute_name"`. For example:

    let 'employee'@'name' = 'employee'.'name'
    let 'employee'@'address' = 'employee'.'address'
    let 'employee'@'phone' = 'employee'.'phone'

Note that the Default Application Generator created this application so that the context name and the window name are the same as the application name. This makes it a bit confusing to look at the `set_field_values` command. The first parameter `employee` is the name of the window and the second `employee` is the name of the context.

#### 4.7.1.3 Field Enter and Exit Scripts

When you move from one field to another, the Exit Script of the field you leave is executed. If an error condition is generated by this script, then the execution of all scripts is stopped and a message is usually displayed. You remain in that field to correct the error before proceeding. Only the Exit Script of the field you were in before the function key (next/previous field, key script (unless disabled) or other events which executes a script) was pressed is executed.

Enter and Exit Scripts of fields which are visited by using the `current_field` command (function) are not executed. When all scripts have finished running, the Enter Script at the new current field is executed before control is passed back to you.

Try moving the cursor from one field to another. The Enter and Exit Scripts of a field are defined in the FIELD TABLE.

The `number` field in the FIELD TABLE.

Figure 4-60 `number` Field

The `name` field in the FIELD TABLE.

Figure 4-61 `name` Field

The `check` command in the Exit Script:

    check 'employee'@'name' for 'personnel'.'name'

checks the contents of the field `'employee'@'name'` to see that its value is valid for the data type of the attribute `'personnel'.'name'`. If the value is not valid, then an error message is displayed, a beep is issued, and execution is terminated. The field remains current. The value must be changed before further processing can take place.

The `phone` field in the FIELD TABLE.

Figure 4-62 `phone` Field

The `credit_limit` field in the FIELD TABLE.

Figure 4-63 `credit_limit` Field

#### 4.7.1.4 Key Scripts

When you press each function key, the Key Script associated with the function key defined in the KEY TABLE is executed.

We use the logical key names AP_1, AP_2, ..., AP_10 in the Application Script. When the application is compiled, this logical key name will be mapped to the physical terminal function key through the 4GL terminal database `termdb`.

The following figures show the scripts for each of the function keys:
Figure 4-64 Select Key Script
Figure 4-65 Insert Key Script
Figure 4-66 Update Key Script
Figure 4-67 Delete Key Script

Figure 4-68 Next Key Script

Figure 4-69 Previous Key Script

Figure 4-70 Clear Key Script

#### 4.7.1.5 Application Exit Script

When you press the `Quit` key to exit the application, the script in the APPLICATION TABLE Exit Script is executed:

Figure 4-71 Application Exit Script

    call remove_window ('sys_default_menu');

This statement removes the menu window.

    call remove window ('employee');

This statement removes the `employee` window.

    end_select context 'employee';

This statement closes the select context `employee`.

    close 'personnel';

This statement closes the table `personnel`.

After executing the Application Exit Script, Empress 4GL returns the control to the calling application, `sys_main` in this case, or the operating system.

To Next Section