Progress
Language Tutorial
for Character


Retrieving Data from a Database

An enterprise’s data is a vital asset. Progress has a system for protecting that data called the relational database management system (RDBMS). When you start a Progress application, the RDBMS uses the commands in the startup script to connect the necessary database or databases. All the objects (fields, tables, indexes, and so on) contained in the connected databases make up the set of objects that your application can reference. Your procedures interact with the RDBMS behind the scenes to access these database objects.

Earlier in the chapter, you learned that your procedures cannot interact with database data until you copy data into a record buffer. This section teaches you how to copy a single record or a group of fields to a record buffer with the database access form shown earlier. This is the basic syntax of a database request. Note that the combination of the table-name and record-phrase-options in the syntax expression comprise the record phrase.

SYNTAX
statement-name
  table-name [ record-phrase-options ] [ statement-options ] 

Retrieving Field Lists

When you request data from a database record, you can request the entire record or a specific subset of fields from the record using a field list. A field list is a subset of the fields that define a database record. Field lists can be beneficial when you are running Progress on a network; if you only retrieve the fields you need, you can reduce network traffic. However, field lists are only useful if you are reading records without making any changes.

You can use field lists in your code with the FIELDS option in the following Progress 4GL statements:

It is a good rule of thumb to use field lists only when you are referencing less than 90% of the record. When you are referencing more than 90%, it makes more sense to fetch the entire record. Also keep in mind these rules when you use field lists:

The lt-08-08.p procedure shows a revision of an example from "Representing Data with Widgets," that uses the FIELDS option of the FOR statement to read only the fields referenced in this application. This is the revised code:

lt-08-08.p
      /**********  DEFINE WIDGETS  **********/
      DEFINE VARIABLE Reps AS CHARACTER VIEW-AS SELECTION-LIST 
          INNER-CHARS 25 INNER-LINES 9 SORT.
      DEFINE VARIABLE Stat AS LOGICAL.
      DEFINE BUTTON btn-Exit LABEL "Exit".
 
      /**********  DEFINE FRAMES  **********/
      DEFINE FRAME Frame1
          Reps NO-LABEL      AT ROW 2 COLUMN 3
          Salesrep.Sales-Rep AT ROW 2 COLUMN 35
         Salesrep.Rep-Name  FORMAT "x(20)" AT ROW 3 COLUMN 35
         Salesrep.Region    AT ROW 4 COLUMN 38
          btn-Exit           AT ROW 9 COLUMN 3 SKIP(1)
        WITH SIDE-LABELS CENTERED ROW 2 TITLE "Update Sales Rep Info"
         THREE-D.
 
      /**********  DEFINE TRIGGERS  **********/
      ON DEFAULT-ACTION OF Reps
       DO:
          ASSIGN Reps.
          FIND FIRST Salesrep WHERE Salesrep.Rep-Name = Reps.
          DISPLAY Salesrep.Sales-Rep Salesrep.Rep-Name Salesrep.Region 
              WITH FRAME Frame1.
       END.
 
      /**********  MAIN LOGIC  **********/
      Reps:DELIMITER = "*".
/*1*/  FOR EACH Salesrep FIELDS (Rep-Name) BY Salesrep.Rep-Name:
          Stat = Reps:ADD-LAST(Salesrep.Rep-Name).
       END.
      FIND FIRST Salesrep.
/*2*/  DISPLAY Reps Salesrep.Sales-Rep Salesrep.Rep-Name Salesrep.Region
          WITH FRAME Frame1.
      ENABLE ALL WITH FRAME Frame1.
      WAIT-FOR CHOOSE of btn-Exit. 

NOTE: The THREE-D option is relevant only on a Windows client; it is ignored by a character client.

These notes help explain the code:

  1. This FOR statement uses the FIELDS option to specify a field list instead of fetching the entire record.
  2. The Sales-Rep and Region fields of the Salesrep table are also referenced in the application, but they do not appear in the field list because they are referenced outside of the FOR statement.
Referencing Database Records

The record phrase is the language structure for referencing the table you want to work with and for helping to specify an individual record or a set of records.

The reference to the table name is both a reference to the database table and the record buffer associated with the table. When two connected databases contain identically named tables, you also need to specify the database name in the record phrase to make the reference unambiguous. The diagram below shows the correct syntax for unambiguous record references. Note that the period separates the names.

SYNTAX
database-name.table-name 

Progress flags all ambiguous references at compilation time. Many programmers always specify the full reference to avoid ambiguity and make more readable code.

Referencing Database Fields

After you retrieve a record, you’ll want to work selectively with some of the fields in the record buffer. To avoid ambiguity at this level, use the syntax shown below.

SYNTAX
database-name.table-name.field-name 

It is also important to remember that the expanded reference to a database field is also a reference to the widget that represents it. In most cases, Progress knows from the context of your code whether you are working with the record buffer (database value) or screen buffer (widget screen value).

The following code, which comes from the include file, sets up the database access form:

lt-08-f1.i
/* Form definition for Chapter 8 programming examples */ 
        .
        . 
        .
DEFINE FRAME Frame1
    SKIP(1) 
    sports.Item.Item-Num LABEL "Item No." COLON 15
    sports.Item.Item-Name LABEL "Name" COLON 15
    sports.Item.Price COLON 15
    sports.Item.On-Hand LABEL "On Hand" COLON 15
    sports.Item.Allocated  COLON 15
    sports.Item.Re-Order LABEL "Reorder At" COLON 15
    sports.Item.On-Order LABEL "On Order" COLON 15
    sports.Item.Cat-Page LABEL "Catalog Page" COLON 15
    sports.Item.Cat-Description LABEL "Description" FORMAT "x(40)"  
        COLON 15  SPACE(2) SKIP(1)
    btn-Prev TO 12 
    btn-Next 
    btn-Update 
    btn-Add
    btn-Delete
    btn-Exit SKIP(1)
        WITH SIDE-LABELS CENTERED ROW 2 THREE-D
            TITLE "Database Access Form for the Item Table". 

The frame in this code fragment is the main display for the database access form. Notice how the database references here refer to the widgets associated with the database fields. You can think of this frame as a formatted representation of the screen buffer.

About Queries

A query is a request for database data that results in one or more records. A results or results list is the record or set of records defined by a query. Some Progress statements query the database directly. Other statements work with the results list of a predefined query to query the database. A results list points to all the records that satisfy a predefined query. For example, the FIND statement queries the database directly, while the GET statement queries through the results list of a predefined query.

In general, the Progress documentation uses “query” to refer to a predefined query created with the DEFINE QUERY and OPEN QUERY statements.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095