Progress
Language Reference
FIND Statement
Locates a single record in a table and moves that record into a record buffer.
DATA MOVEMENT
![]()
SYNTAX
You can specify the OF, WHERE, USE-INDEX, and USING options in any order.
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.
Progress converts this FIND statement with the constant option of 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.
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.
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.
NOTE: The WHERE clause may not work the same way against a DataServer as it does against the Progress database. Please refer to the appropriate DataServer Guide, Progress DataServer for ODBC Guide or Progress DataServer for ORACLE Guide, for additional information on how this feature will perform.
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.
This FIND statement is the same as the following statement.
The cust-num field is a non-abbreviated index. However, look at this example.
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.
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
If a procedure finds a record and it places it in a buffer using NO-LOCK and you then refind 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.
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). 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.
EXAMPLESThis 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.
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.
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. (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.
- 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.
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.
- 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.
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.”
- 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.”
- 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.
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 © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |