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:

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 Figure 4-50 Application Manager

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

Figure 4-51 Menu 2 Figure 4-51 Menu 2

Press the Default key to generate the default application.

Figure 4-52 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 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 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 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 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 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 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 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 Figure 4-60 number Field

The name field in the FIELD TABLE.

Figure 4-61 name field 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 Figure 4-62 phone Field

The credit_limit field in the FIELD TABLE.

Figure 4-63 credit_limit field 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-64 Select Key Script
 
Figure 4-65 Insert Key Script Figure 4-65 Insert Key Script
 
Figure 4-66 Update Key Script Figure 4-66 Update Key Script
 
Figure 4-67 Delete Key Script Figure 4-67 Delete Key Script

Figure 4-68 Next Key Script Figure 4-68 Next Key Script

Figure 4-69 Previous Key Script Figure 4-69 Previous Key Script

Figure 4-70 Clear 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 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