A query defines a set of data to retrieve from the database. It provides similar functionality as the data access blocks DO, FOR, and REPEAT. The main difference is that the result set defined by a query is not scoped to the block where it is defined. Using the handle to a query, you can access the query and its result set from anywhere in your application. This gives you the ability to modularize your application in ways that can't be done with block-oriented result sets.

Queries give your data access language these important characteristics:

  • Scope independence — You can refer to the records in the query anywhere in your application.
  • Record retrieval independence — You can move through the result set under complete control of either program logic or user events.
  • Repositioning flexibility — You can position to any record in the result set at any time.

To get a query to retrieve data, you need to open it. When you are done with a query you should close it to free the system resources used by the query.

Static queries

Static queries are used when the definition of the query is known during development. You define a static query using the DEFINE QUERY statement to create the details for the query. The query must be opened with an OPEN QUERY statement before it can be used.

The following code defines and opens a query. It then cycles through all of the customer records and counts them as it goes.
DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
DEFINE QUERY qCust for Customer.

OPEN QUERY qCust FOR EACH Customer.
GET FIRST qCust.

DO WHILE AVAILABLE Customer:
  iCount = iCount + 1.
  GET NEXT qCust.
END.
DISPLAY iCount.

CLOSE QUERY qCust.

Dynamic queries

Dynamic queries are used when the definition of the query is not known until runtime. For example you might want the user to input a query string. To construct a dynamic query you define a handle variable for the query and you use the CREATE QUERY statement to create an empty query at runtime. You then set the buffers using the SET-BUFFERS method and then prepare the query using the QUERY-PREPARE( ) method, where you pass the dynamic query string as a parameter. Finally, the query must be opened using the QUERY-OPEN( ) method before it can be used.

DEFINE INPUT PARAMETER bufHandle AS HANDLE.
DEFINE INPUT PARAMETER qryString AS CHARACTER.
DEFINE VARIABLE hQuery AS HANDLE  NO-UNDO.

CREATE QUERY hQuery.
hQuery:SET-BUFFERS(bufHandle). 
hQuery:QUERY-PREPARE(qryString).
hQuery:QUERY-OPEN().
See also

Closing a query

Query object handle

Queries versus block-oriented data access

GET statement

The GET statement returns one record, and optionally related records, from an opened query.

Syntax

GET { FIRST | NEXT | PREV | LAST | CURRENT } query
  [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ].
FIRST | NEXT | PREV | LAST | CURRENT
  • FIRST returns the first record from the query.
  • NEXT returns the first or next record from the query.
  • PREV returns the preceding or last record from the query.
  • LAST returns the last record from the query.
  • CURRENT refreshes the current record or records from the query.
query
The name of the query.
SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK
The specified lock is applied to the record. Overrides the default locking of the OPEN QUERY statement.