Progress
Language Reference


FIND Statement

Interfaces
OS
SpeedScript
All
All
Yes

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 {*} 

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.

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 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.

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). 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

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