GET statement

Returns one record for a previously opened query.

Syntax

GET { FIRST | NEXT | PREV | LAST | CURRENT } query
    [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
    [ NO-WAIT ]

FIRST query

Finds the first record associated with the query. The query must have been previously opened in an OPEN QUERY statement. The order of the records is determined by the options specified in the Record phrase the OPEN QUERY statement.

NEXT query

Returns the first or next record associated with the query. The query must have been previously opened in an OPEN QUERY statement. The order of the records is determined by the options specified in the OPEN QUERY statement of the Record phrase.

PREV query

Returns the preceding or last record associated with the query. The query must have been previously opened in an OPEN QUERY statement. The order of the records is determined by the options specified in the OPEN QUERY statement of the Record phrase.

LAST query

Returns the last record associated with the query. The query must have been previously opened in an OPEN QUERY statement. The order of the records is determined by the options specified in the OPEN QUERY of the Record phrase.

CURRENT query

Refetches the current record or records associated with the query. The query must have been previously opened in an OPEN QUERY statement. If the query is a join, the AVM returns the current record for all tables in the join.

SHARE-LOCK

Specifies that the record is share locked. Overrides the default locking of the OPEN QUERY statement. This applies to all buffers in a join.

EXCLUSIVE-LOCK

Specifies that the record is exclusively locked. Overrides the default locking of the OPEN QUERY statement. This applies to all buffers in a join.

NO-LOCK

Specifies that no lock is applied to the record. Overrides the default locking of the OPEN QUERY statement. This applies to all buffers in a join.

NO-WAIT

Specifies that the GET statement returns immediately if the record cannot be accessed because it is locked by another user. If you do not use the NO-WAIT option, the GET statement waits until the record can be accessed. This applies to all buffers in a join. If you specify NO-WAIT and the record is locked by another user, the record is returned to you with NO-LOCK and the LOCKED function returns TRUE for the record.

Example

This procedure uses the GET statement to find Customer orders:

r-getord.p

DEFINE QUERY cust-order FOR Customer, Order.

OPEN QUERY cust-order FOR EACH Customer, EACH Order OF Customer.

GET FIRST cust-order.

DO WHILE AVAILABLE Customer:
  DISPLAY Customer.CustNum Customer.Name 
    WITH FRAME cust-info.
  DISPLAY Order WITH FRAME order-info SIDE-LABELS.
  PAUSE.

  GET NEXT cust-order.
END.

In the example, the GET FIRST statement fetches the first Customer record and the first Order record for that Customer. The GET NEXT statement fetches the next Order record for the Customer. If no more Order records are found for the current Customer, then the GET NEXT statement fetches the next Customer and the first Order record for that Customer. If a Customer has no Orders, the GET statement skips that Customer.

Notes

• The query must be opened with the OPEN QUERY statement before any records are fetched.
• A query that references more than one buffer defines a join. Each GET statement returns one set of records.
• If you execute a GET NEXT statement after the last record of the query has been fetched or you execute a GET PREV statement after the first record of the query has been fetched, the ERROR condition is not raised. However, you can use the AVAILABLE function to test whether a record was returned for the query fetch. You can also use the QUERY-OFF-END function to determine if the query is positioned at the end of its result list.
• If the query is positioned before the first record, GET NEXT acts the same as a GET FIRST; similarly, if the query is positioned beyond the last record, GET PREV acts the same as GET LAST.
• The GET LAST statement can be slow unless the AVM has performed a presort or already returned the last record that satisfies the query, or you specify USE-INDEX for the query (or the query happens to only use one index). Also, GET LAST might be slow if the query involves an outer join.
• If you do not specify a lock type, the AVM uses the lock type specified in the OPEN QUERY statement. If no lock type is specified in either the GET or OPEN QUERY statement, then the default ABL locking rules apply.
• If a GET CURRENT statement fails because of a lock conflict, the AVM rereads the record with a NO-LOCK status.
• When a GET statement executes, any FIND triggers defined for the tables are executed.
• FIND triggers do not execute for a GET CURRENT statement.
• To upgrade the lock on only one table in a join, use the FIND CURRENT statement.
• A query that includes a BREAK BY phrase becomes a FORWARD-ONLY query. In this case you cannot use the GET FIRST, GET LAST, or GET PREV statements. If you do, the AVM raises ERROR.

See also

AVAILABLE function, CLOSE QUERY statement, CURRENT-CHANGED function, CURRENT-RESULT-ROW function, DEFINE QUERY statement, FIND statement, FOR statement, LOCKED function, NUM-RESULTS function, OPEN QUERY statement, QUERY-OFF-END function, REPOSITION statement