Progress
Language Tutorial
for Windows


Retrieving Data Through a Query with the GET Statement

The GET statement works similarly to the FIND statement, but the GET statement works with a defined query. With FIND, you provide enough information for your procedure to access the database and find the record that satisfies your criteria. With a defined query, the DEFINE QUERY and OPEN QUERY statements provide enough information for Progress to build a results list of all the records that match your criteria. The GET statement works with the results list to determine the correct record and then copies that record from the database to a record buffer, as shown in Figure 8–8.

Figure 8–8: Data Movement with the GET Statement

As you can see, GET accomplishes the same thing as the FIND statement. While FIND goes directly to the database, GET works through the results list. GET manipulates the cursor of the results list. The cursor is an agreement between the query results list and the database about which record is the current one. When GET moves the cursor of the results list, Progress updates the associated record buffer. So, the position of the cursor always indicates which record is in the record buffer. This is the syntax for the GET statement.

SYNTAX
GET { FIRST | NEXT | PREV | LAST | CURRENT } query-name 

As with FIND, the four keywords specify which record you are trying to get. Following the keyword, you provide the name of the query, not the actual table name:

GET FIRST My-Query. 

Notice that the GET statement does not support the NO-ERROR option. In the last programming example, you saw that NO-ERROR is very useful for suppressing undesirable error behavior with the FIND statement. GET does not support the NO-ERROR option because a failed GET does not invoke an error response. For example, suppose you try to get the next record of the last record. The GET statement moves the results list cursor to a null position, and clears the associated record buffer, which remains empty. This condition is known as being off the end of the results list.

You can use the AVAILABLE function to check for this error condition (an empty record buffer), or you can use a special query function: QUERY-OFF-END. This is the syntax for QUERY-OFF-END.

SYNTAX
QUERY-OFF-END ( query-name ) 

The function takes the query name as a string and returns TRUE if the cursor is off either the top or bottom end of the results list. Here is a code example using this query function:

IF QUERY-OFF-END ( "My-Query" ) THEN GET FIRST My-Query. 

GET and DISPLAY Programming Example

This exercise is based on the code used to demonstrate FIND, but instead it uses a query, GET statements, and the QUERY-OFF-END function. Follow these steps to see the results:

  1. Open lt-08-02.p and run it. The database access form appears.
  2. Choose the Prev and Next buttons and notice the changes to the Item No. and Name fields. The code works precisely the same as the FIND statement version.
  3. Choose Exit, then press SPACEBAR to return to the Procedure Editor.

This the code that created the display:

lt-08-02.p
      /**********  DEFINE QUERY  **********/
/*1*/  DEFINE QUERY Item-Query FOR Item.
      /**********  DEFINE FORM  **********/
      {lt-08-f1.i}

      /**********  DEFINE TRIGGERS  **********/     
/*4*/  ON CHOOSE OF btn-Prev 
       DO:
          GET PREV Item-Query.
         IF QUERY-OFF-END(Item-Query) THEN GET LAST Item-Query.   
         DISPLAY Item.Item-Num Item-Name Price On-Hand Allocated Re-Order 
            On-Order Cat-Page Cat-Description WITH FRAME Frame1.
       END.
/*5*/  ON CHOOSE OF btn-Next 
       DO:
          GET NEXT Item-Query.
         IF QUERY-OFF-END(Item-Query) THEN GET FIRST Item-Query.
         DISPLAY Item.Item-Num Item-Name Price On-Hand Allocated Re-Order 
            On-Order Cat-Page Cat-Description WITH FRAME Frame1.
       END.
      /**********  MAIN LOGIC  **********/
/*2*/  OPEN QUERY Item-Query FOR EACH Item.
/*3*/  GET FIRST Item-Query.
      DISPLAY Item.Item-Num Item-Name Price On-Hand Allocated Re-Order 
          On-Order Cat-Page Cat-Description WITH FRAME Frame1 USE-TEXT.
      ENABLE btn-Prev btn-Next btn-Exit WITH FRAME Frame1.
      WAIT-FOR CHOOSE OF btn-Exit.
/*6*/  CLOSE QUERY Item-Query. 

These notes help explain the code:

  1. The DEFINE QUERY occurs at the beginning of the procedure.
  2. OPEN QUERY at the beginning of the main code block initializes the results list that defines the matching subset of records. You cannot use GET until after you open the query.
  3. The GET statement creates a record buffer and copies a record to the buffer from the database using the query results list.
  4. This trigger performs the “retrieve next or retrieve first record” logic, exactly as in the FIND example. Here, the GET statement replaces the FIND statement and the QUERY-OFF-END function replaces the AVAILABLE function.
  5. This trigger performs the “retrieve previous or retrieve last record” logic, exactly as in the FIND example.
  6. The CLOSE QUERY statement after the WAIT-FOR statement releases the resources used by the query.
  7. Practice Problems

    The basic form used in the programming examples can quickly be refitted for use with any other table. Use the Customer table with the problems below to practice what you have learned so far.

    Problem 8-1: lt-08-s1.p

    Using the FIND and DISPLAY statements, create a procedure that lets you review records in the Customer table.

    Problem 8-2: lt-08-s2.p

    Make a copy olist:f the procedure you created in Problem 8-1. Replace the FIND statements with DEFINE QUERY and GET statements.


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