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