Release 10.1A: OpenEdge Development:
Progress 4GL Reference

> ## 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, FIND NEXT behaves like FIND FIRST. 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 yet been found, FIND PREV behaves like FIND LAST. 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.

> Progress converts this FIND statement with the `constant` option of 1.

>     FIND customer WHERE cust-num = 1.

> The cust-num 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.order-num.
>     FIND order USING order-num.
>     DISPLAY order.
>     FIND customer OF order.
>     DISPLAY customer.

> The OF option relates the order table to the customer table, telling Progress 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. Progress converts the FIND statement with the OF option to the following:

>     FIND customer WHERE customer.cust-num = order.cust-num.

> 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 ODBC or OpenEdge Data Management: DataServer for ORACLE , for additional information on how this feature will perform.

> Note: You cannot reference a BLOB or CLOB field in a WHERE clause.

> `USE-INDEX `index``

> Identifies the index you want to use while selecting records. If you do not use this option, Progress 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.cust-num.
>     FIND customer USING cust-num.

> This FIND statement is the same as the following statement:

>     FIND customer WHERE customer.cust-num = INPUT customer.cust-num.

> The cust-num field is a non-abbreviated index. However, look at this example:

>     PROMPT-FOR customer.name.
>     FIND customer USING cust-name.

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

>     FIND customer WHERE customer.name BEGINS INPUT name.

> Note that field can be expanded to be FRAME `frame` `field.`

> `SHARE-LOCK`

> Tells Progress 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, Progress 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 Progress tries to read a record that is EXCLUSIVE-LOCKed by another user, Progress waits to read the record until the EXCLUSIVE-LOCK is released. Progress 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, Progress disregards the SHARE-LOCK option.

> `EXCLUSIVE-LOCK`

> Tells Progress 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. Progress 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, Progress 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, Progress disregards the EXCLUSIVE-LOCK option.

> `NO-LOCK`

> Tells Progress 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, Progress 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.
>     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-fund that record using NO-LOCK, Progress 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 cust-num of 0 if another user’s active transaction has created a record and assigned a value to the indexed field cust-num that is greater than 100.

>     FOR EACH customer WHERE cust-num > 100 NO-LOCK:
>       DISPLAY cust-num.
>     END.

> If you are using a record from a work table, Progress 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, Progress waits until the record is available.

> Progress 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, Progress 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`

> Tells Progress not to display error messages for any errors it might encounter. Instead, error information is passed the ERROR-STATUS system handle. Possible errors include: not finding a record that satisfies the `record-phrase,` finding more than one record that satisfies the `record-phrase` for a unique find, or finding a record that is locked with the NO-WAIT option on the FIND. If you use the NO-ERROR option, you can also use the AVAILABLE function to test if FIND found a record.

> 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.item-num.
>           FIND item USING item-num.
>           DISPLAY item-num item-name.
>          REPEAT:
>              FIND NEXT order-line OF item.
>              FIND order OF order-line.
>              FIND customer WHERE customer.cust-num = order.cust-num.
>              DISPLAY customer.name order.order-num order-line.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 LIKE customer.name.
>     REPEAT:
>       SET start-name.
>       FIND FIRST customer WHERE name >= start-name.
>       REPEAT:
>         DISPLAY name.
>         FIND NEXT customer USE-INDEX name.
>       END.
>     END.

> #### Notes

> - If a FIND statement fails, it indicates that the buffer named in `record` contains no record.

> - If Progress finds an old record in the record buffer when executing a FIND, it validates the record then writes it out. (If the record fails validation, Progress returns an error message.) Then it clears the buffer and stores the located record in the record buffer.

> - A FIND statement that does not supply FIRST, LAST, NEXT, or PREV is a unique FIND and must be able to locate, at most, one record based solely on the conditions in the expression or WHERE clause it is using.

> - Fields referenced in the WHERE clause do not have to be indexed.

> - WHERE conditions can include Boolean operations.

> - If a FIND NEXT or FIND PREV does not find another record, Progress takes the end-key action. By default, this action is UNDO, LEAVE for a FOR EACH, REPEAT, or procedure block.

> - See the DEFINE BUFFER statement reference entry for a description of how to use FIND on a PRESELECTed set of records.

> - When you use the FIND statement, Progress selects an index to use based on the WHERE condition or the USE-INDEX option.

> - Your position in an index is established when you find a record and is only modified by subsequent record retrievals, not by CREATEs or by changing indexed field values.

> - If you are using the FIND statement to find a record in a work table, you must use the FIRST, LAST, NEXT, or PREV option with the FIND statement.

> - In a REPEAT block, if you use the FIND NEXT statement to find a record and then do an UNDO, RETRY of a block, the FIND NEXT statement reads the next record in the table, rather than the one found in the block iteration where the error occurred:

>     REPEAT:
>       FIND NEXT order.
>       DISPLAY order.
>       SET order-num.
>       SET order-date promise-date.
>     END.

> Progress 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, Progress 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:

>     REPEAT:
>       IF NOT RETRY THEN FIND NEXT order.
>       DISPLAY order.
>       SET order-num.
>       SET order-date promise-date.
>     END.

> - When you use FIND NEXT or FIND PREV to find a record after updating another record, be careful not to lose your updates in case the record you want to find is unavailable.

>     FIND FIRST customer.
>     REPEAT:
>       UPDATE customer.
>       FIND NEXT customer.
>     END.

> 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 FIRST customer.
>     REPEAT:
>       UPDATE customer.
>       FIND NEXT customer NO-ERROR.
>       IF NOT AVAILABLE customer THEN LEAVE.
>     END.

> - After you use the FIND LAST statement to find the last record in a table, Progress positions the index cursor on that record. Any references within the same record scope to the next record fail.

>     FIND LAST customer.
>     RELEASE customer.
>     DISPLAY AVAILABLE customer.
>     REPEAT:
>       FIND NEXT customer.
>       DISPLAY 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.

> - If you use FIND . . . WHERE ROWID `rowid = . . .` on a PRESELECTed list of records, the temporary preselect index cursor is not reset. So, FIND NEXT does not find the record that follows record `rowid` in the preselected list. (See the DO statement and REPEAT statement reference entries for details.)

> - When you use a FIND NEXT or FIND PREV statement in a subprocedure to access a record from a shared buffer, keep the following in mind:

> - When you run a Progress procedure, Progress creates a cursor indicator for each index accessed through a FIND statement in the procedure and each NEW buffer defined in the procedure. A cursor indicator serves as an anchor for index cursors associated with a table or buffer. An index cursor is attached to the cursor indicator when you enter a block of code where a record buffer is scoped. If two different indexes are used for the same record buffer within a single block of code, two index cursors are attached to the same cursor indicator. When the program control leaves the block where a record buffer is scoped, all index cursors attached to the cursor indicator are released.
> - When Progress encounters a subprocedure in a procedure, it constant, field name, variable name, or checks through the existing index cursors before creating any other index cursors required by the statements in the subprocedure.
> - If the USE-INDEX of the FIND NEXT or FIND PREV statement in a subprocedure accesses an index cursor for a shared buffer that existed prior to the beginning of the subprocedure, the FIND NEXT or FIND PREV statement returns the next or previous record for the shared buffer, based upon the last record found in that buffer and the USE-INDEX of the FIND statement.
> - If the USE-INDEX of the FIND NEXT or FIND PREV statement in a subprocedure accesses an index cursor created for a shared buffer at the beginning of the subprocedure, the FIND NEXT or FIND PREV statement returns the first or last record for the shared buffer, based upon the USE-INDEX of the FIND statement.
> - If a field or variable referenced with FIND is used in more than one frame, then Progress uses the value in the frame most recently introduced in the procedure. To make sure you are using the appropriate frame, use the FRAME option with the FIND function to reference a particular frame.

> - When a FIND statement executes, any FIND trigger defined for the table is executed.

> - The FIND CURRENT statement is useful for maintaining small transaction size in updates. For an example, see the CURRENT-CHANGED function reference entry.

> - FIND triggers do not execute for a FIND CURRENT statement.

> - Progress does not allow a FIND statement within a FOR EACH block unless you specify a different table than the one referenced in the FOR EACH block. When you attempt to compile the following example, Progress returns the error message “FIND cannot be processed for a FOR EACH mode record.”

>     FOR EACH customer:
>      FIND CURRENT customer.
>     END.

> - Progress restricts the FIND statement within a PRESELECT block in the following situations:

> - You cannot specify a lock option on the FIND statement. You must specify it in the PRESELECT phrase. Attempting to compile the following example produces the error message “LOCK keyword illegal on FIND within a PRESELECT for the same table”.

>     DO PRESELECT EACH customer:
>      FIND NEXT customer NO-LOCK.
>     END.

> - You cannot specify a unique FIND or a FIND CURRENT for the same table. The following example produces the error message “Unique FIND not allowed within a PRESELECT on the same table” when you try to compile it.

>     DO PRESELECT EACH customer:
>      FIND customer 5.
>     END.

> #### 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, PRESELECT phrase

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095