Locates a single record in a table and moves that record into a record buffer.
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.
ABL converts this FIND statement with the constant option of 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.
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:
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.
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.
The USING option translates into an equivalent WHERE option.
This FIND statement is the same as the following statement:
The CustNum field is a non-abbreviated index. However, consider this example:
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:
Note that field can be expanded to be FRAME framefield.
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.
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.
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:
If you are using a record from a work table, the AVM disregards the NO-LOCK option.
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.
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:
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:
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
REPEAT: FIND NEXT Order. DISPLAY Order. SET Order.OrderNum. SET Order.OrderDate Order.PromiseDate. END. |
The AVM does an UNDO, RETRY if there is an error and you explicitly use the UNDO, RETRY statement, or if you press END-ERROR on the second or later windows interaction in a block.
Here, if you press END-ERROR during the second SET statement, the AVM displays the next record in the table.
If you are using a FOR EACH block to read records, and do an UNDO, RETRY during the block, you see the same record again rather than the next record.
If you want to use a REPEAT block and want to see the same record in the event of an error, use the RETRY function:
In this example, if the FIND NEXT statement fails to find the customer record, any changes made during the UPDATE statement are undone. To avoid this, use the following technique:
FIND LAST Customer. RELEASE Customer. DISPLAY AVAILABLE Customer. REPEAT: FIND NEXT Customer. DISPLAY Customer.Name. END. |
In this example, the RELEASE statement releases the last Customer record from the Customer record buffer and the following DISPLAY statement displays FALSE because the Customer record is no longer available. However, the index cursor is still positioned on that last record. Therefore, the FIND NEXT statement fails.