FIND statement

Locates a single record in a table and moves that record into a record buffer.

Data movement



Syntax

FIND [ FIRST | LAST | NEXT | PREV ] record 
     [constant ]
     [ OF table ]
     [ WHERE expression ]
     [ USE-INDEX index ]
     [ USING [ FRAME frame ] field 
       [ AND [ FRAME frame ] field ] ...
     ]
     [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ] 
     [ NO-WAIT ]
     [ NO-PREFETCH ]
     [ NO-ERROR ]

You can specify the OF, WHERE, USE-INDEX, and USING options in any order.

FIND CURRENT record 
     [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
     [ NO-WAIT ]
     [ NO-ERROR ]
FIRST
Finds the first record in the table that meets the characteristics you might have specified with record. If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the first record in that preselected subset of records.
LAST
Finds the last record in the table that meets the specified characteristics of the record. If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the last record in that preselected subset of records.
NEXT
Finds the next record in the table that meets the specified characteristics of the record. If no record has been found, the buffer contents specified by record are unknown, with a ROWID equal to the Unknown value (?). If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the next record in that preselected subset of records.
PREV
Finds the previous record in the table. If no record has been found, the buffer contents specified by record are unknown, with a ROWID equal to the Unknown value (?). If the buffer named in the record was preselected in a DO or REPEAT statement, FIND locates the previous record in that preselected subset of records.
CURRENT
Refetches the current record in the buffer with the specified lock status.
record
Identifies the record you want to retrieve. The record parameter can be a reference to a database table or a defined buffer.
constant
The value of a single component, unique, primary index for the record you want.
FIND Customer 1.

ABL converts this FIND statement with the constant option of 1.

FIND Customer WHERE Customer.CustNum = 1.

The CustNum field is the only component of the primary index of the Customer table. If you use the constant option, you must use it once in a single Record phrase, and it must precede any other options in the Record phrase.

OF table
Qualifies the records by relating the record to a record in another table.
PROMPT-FOR Order.OrderNum.
FIND Order USING OrderNum.
DISPLAY Order.
FIND Customer OF Order.
DISPLAY Customer.

The OF option relates the order table to the Customer table, telling the AVM to select the Customer record related to the Order record currently being used. When you use OF, all fields participate in match criteria, if an index is multi-field. The relationship is based on having a UNIQUE index in one table. ABL converts the FIND statement with the OF option to the following:

FIND Customer WHERE Customer.CustNum = Order.CustNum.

You can access related tables using WHERE, whether or not the field names of the field or fields that relate the tables have the same name.

WHERE expression
Qualifies the records you want to access. The expression is a constant, field name, variable name, or expression whose value you want to use to select records. You can use the WHERE keyword even if you do not supply an expression.
FOR EACH Customer WHERE {*}

The WHERE clause may not work the same way against a DataServer as it does against the OpenEdge database. Refer to the appropriate DataServer Guide (OpenEdge Data Management: DataServer for Microsoft SQL Server or OpenEdge Data Management: DataServer for Oracle) for additional information on how this feature will perform.

Note: You cannot reference an OpenEdge BLOB or CLOB field in a WHERE clause. However, in OpenEdge DataServer for MS SQL Server, a non-legacy BLOB or CLOB server data type can be mapped to the OpenEdge CHARACTER data type in a WHERE clause operand as long as the total size of the resolved WHERE clause (which might include the CHARACTER-mapped value) does not exceed 30000 bytes.
USE-INDEX index
Identifies the index you want to use while selecting records. If you do not use this option, the AVM selects an index to use based on the criteria specified with the WHERE, USING, OF, or constant options.
USING [ FRAME frame ] field [ AND [ FRAME frame ] field ] . . .
One or more names of fields for selecting records. The field you name in this option must have been entered previously, usually with a PROMPT-FOR statement. The field must be viewed as a fill-in or text widget.

The USING option translates into an equivalent WHERE option.

PROMPT-FOR Customer.CustNum.
FIND Customer USING Customer.CustNum.

This FIND statement is the same as the following statement:

FIND Customer WHERE Customer.CustNum = INPUT Customer.CustNum.

The CustNum field is a non-abbreviated index. However, consider this example:

PROMPT-FOR Customer.Name.
FIND Customer USING Customer.CustName.

If the name field is an abbreviated index of the Customer table, ABL converts the FIND statement with the USING option into this following statement:

FIND Customer WHERE Customer.Name BEGINS INPUT Customer.Name.

Note that field can be expanded to be FRAME framefield.

SHARE-LOCK
Tells the AVM to put a SHARE-LOCK on records as they are read. Other users can still read a record that is share locked, but they cannot update it. By default, the AVM puts a SHARE-LOCK on a record when it is read, and automatically puts an EXCLUSIVE-LOCK on a record when it is modified (unless the record is already EXCLUSIVE-LOCKed).

If you use the SHARE-LOCK option and the AVM tries to read a record that is EXCLUSIVE-LOCKed by another user, the AVM waits to read the record until the EXCLUSIVE-LOCK is released. The AVM displays a message to the user of that procedure, identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the SHARE-LOCK option.

EXCLUSIVE-LOCK
Tells the AVM to put an EXCLUSIVE-LOCK on records as they are read. Other users cannot read or update a record that is EXCLUSIVE-LOCKed, except by using the NO-LOCK option. They can access that record only when the EXCLUSIVE-LOCK is released. The AVM automatically puts a SHARE-LOCK on a record when it is read and automatically puts an EXCLUSIVE-LOCK on a record when it is updated.

If a record is read specifying EXCLUSIVE-LOCK, or if a lock is automatically changed to EXCLUSIVE-LOCK by an update, a user's read or update will wait if any other user has the record SHARE-LOCKed or EXCLUSIVE-LOCKed.

When a procedure tries to use a record that is EXCLUSIVE-LOCKed by another user, the AVM displays a message identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the EXCLUSIVE-LOCK option.

NO-LOCK
Tells the AVM to put no locks on records as they are read, and to read a record even if another user has it EXCLUSIVE-LOCKed.

Other users can read and update a record that is not locked. By default, the AVM puts a SHARE-LOCK on a record when it is read (unless it is using a CAN-FIND function, which defaults to NO-LOCK), and automatically puts an EXCLUSIVE-LOCK on a record when it is updated (unless the record is already EXCLUSIVE-LOCKed). A record that has been read NO-LOCK must be reread before it can be updated, as shown in this example:

DEFINE VARIABLE rid AS ROWID NO-UNDO.

FIND FIRST Customer NO-LOCK.
rid = ROWID(Customer).
FIND Customer WHERE ROWID(Customer) = rid EXCLUSIVE-LOCK.

If a procedure finds a record and it places it in a buffer using NO-LOCK and you then re-find that record using NO-LOCK, the AVM does not reread the record. Instead, it uses the copy of the record that is already stored in the buffer.

When you read records with NO-LOCK, you have no guarantee of the overall consistency of those records because another user might be in the process of changing them. For example, when a record is updated, changes to indexed fields are written immediately, but changes to other fields are deferred. In the meantime, the record is in an inconsistent state. For example, the following procedure might display a CustNum of 0 if another user's active transaction has created a record and assigned a value to the indexed field CustNum that is greater than 100:

FOR EACH Customer WHERE Customer.CustNum > 100 NO-LOCK:
  DISPLAY Customer.CustNum.
END.

If you are using a record from a work table, the AVM disregards the NO-LOCK option.

NO-WAIT
Causes FIND to return immediately and raise an error condition if the record is locked by another user (unless you use the NO-ERROR option on the same FIND statement). For example:
FIND Customer USING cust-name NO-ERROR NO-WAIT.

Without the NO-WAIT option, the AVM waits until the record is available.

The AVM ignores NO-WAIT when it is used with work tables and databases that are only accessed by a single user.

NO-PREFETCH
Specifies that only one record is sent across the network at a time. If you are accessing a remote server and do not specify this option, the AVM might send more than one record from the server to the client in each network packet. Sending more than one packet may, in rare cases, create inconsistencies with Progress Version 6 or earlier.
NO-ERROR
Suppresses ABL errors or error messages that would otherwise occur and diverts them to the ERROR-STATUS system handle. If an error occurs, the action of the statement is not done and execution continues with the next statement. If the statement fails, any persistent side-effects of the statement are backed out. If the statement includes an expression that contains other executable elements, like methods, the work performed by these elements may or may not be done, depending on the order the AVM resolves the expression elements and the occurrence of the error.

For the FIND statement with NO-ERROR, you can use the AVAILABLE function to test if FIND found a record.

To check for errors after a statement that uses the NO-ERROR option:

  • Check the ERROR-STATUS:ERROR attribute to see if the AVM raised the ERROR condition.
  • Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if the AVM generated error messages. ABL handle methods used in a block without a CATCH end block treat errors as warnings and do not raise ERROR, do not set the ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle. Therefore, this test is the better test for code using handle methods without CATCH end blocks. ABL handle methods used in a block with a CATCH end block raise ERROR and add messages to the error object generated by the AVM. In this case, the AVM does not update the ERROR-STATUS system handle.
  • Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a particular message, where message-num is 1 for the first message.

If the statement does not include the NO-ERROR option, you can use a CATCH end block to handle errors raised by the statement.

Some other important usage notes on the NO-ERROR option:

  • NO-ERROR does not suppress errors that raise the STOP or QUIT condition.
  • A CATCH statement, which introduces a CATCH end block, is analogous to a NO-ERROR option in that it also suppresses errors, but it does so for an entire block of code. It is different in that the error messages are contained in a class-based error object (generated by the AVM or explicitly thrown), as opposed to the ERROR-STATUS system handle. Also, if errors raised in the block are not handled by a compatible CATCH block, ON ERROR phrase, or UNDO statement, then the error is not suppressed, but handled with the default error processing for that block type.
  • When a statement contains the NO-ERROR option and resides in a block with a CATCH end block, the NO-ERROR option takes precedence over the CATCH block. That is, an error raised on the statement with the NO-ERROR option will not be handled by a compatible CATCH end block. The error is redirected to the ERROR-STATUS system handle as normal.
  • If an error object is thrown to a statement that includes the NO-ERROR option, then the information and messages in the error object will be used to set the ERROR-STATUS system handle. This interoperability feature is important for those integrating code that uses the traditional NO-ERROR technique with the newer, structured error handling that features error objects and CATCH end blocks.

Examples

This procedure produces a report that shows all the customers who bought a particular item, and the quantity that they bought. The procedure finds an item record, the order-lines that use that item, the order associated with each order-line, and the customer associated with each order.

r-find.p

REPEAT:
  PROMPT-FOR Item.ItemNum.
  FIND Item USING Item.ItemNum.
  DISPLAY Item.ItemNum Item.ItemName.
  REPEAT:
    FIND NEXT OrderLine OF Item.
    FIND Order OF OrderLine.
    FIND Customer WHERE Customer.CustNum = Order.CustNum.
    DISPLAY Customer.Name Order.OrderNum OrderLine.Qty (TOTAL).
  END.
END.

The FIND FIRST statement in the following procedure finds the first record with a name field value that alphabetically follows the name supplied by the user. The FIND NEXT statement uses the name index to find the next record in the table, using the name index.

r-find2.p

DEFINE VARIABLE start-name NO-UNDO LIKE Customer.Name.

REPEAT:
  SET start-name.
  FIND FIRST Customer WHERE Customer.Name >= start-name.
  REPEAT:
    DISPLAY Customer.Name.
    FIND NEXT Customer USE-INDEX name.
  END.
END.

Notes

See also

AMBIGUOUS function, AVAILABLE function, CAN-FIND function, CURRENT-CHANGED function, DEFINE BUFFER statement, ERROR-STATUS system handle, FOR statement,GET statement, LOCKED function, NEW function (record buffers), PRESELECT phrase