The .4gl File - Retrieving and Displaying Data

The example demonstrates how to define a record so you can treat variables as a group. Static SQL instructions retrieve rows from the database which are displayed to the form using the DISPLAY BY NAME statement.

Defining a Record

In addition to defining individual variables, the DEFINE statement can define a record, a collection of variables each having its own data type and name. You put the variables in a record so you can treat them as a group. Then, you can access any member of a record by writing the name of the record, a dot (known as dot notation), and the name of the member.

DEFINE custrec RECORD
          store_num  LIKE customer.store_num
          store_name LIKE customer.store_name
       END RECORD
DISPLAY custrec.store_num

Your record can contain variables for the columns of a database table. At its simplest, you write RECORD LIKE tablename.* to define a record that includes members that match in data type all the columns in a database table. However, if your database schema changes often, it's best to list each member individually, so that a change in the structure of the database table won't break your code. Your record can also contain members that are not defined in terms of a database table.

Using SQL to Retrieve the Data

A subset of SQL, known as Static SQL, is provided as part of the BDL language and can be embedded in the program. At runtime, these SQL statements are automatically prepared and executed by the runtime System.

SELECT store_num, store_name INTO custrec.* FROM customer

Only a limited number of SQL instructions are supported this way. However, Dynamic SQL Management allows you to execute any kind of SQL statement.

Displaying a Record: DISPLAY BY NAME

A common technique is to use the names of database columns as the names of both the members of a program record and the fields in a form. Then, the DISPLAY BY NAME statement can be used to display the program variables. By default, a screen record consisting of the form fields associated with each database table column is automatically created. BDL will match the name to the name of the form field, ignoring any record name prefix:

DISPLAY BY NAME custrec.*

The program variables serve as the intermediary between the database and the form that is displayed to the user. Values from a row in the database table are retrieved into the program variables by an SQL SELECT statement, and are then displayed on the form. In Tutorial Chapter 6: Add, Update and Delete you will see how the user can change the values in the form, resulting in changes to the program variables, which could then be used in SQL statements to modify the data in the database.

Figure: Flow between the database, program, and form


This figure shows how values from a row in the database table are retrieved into program variables by a SELECT statement and then displayed to a form.