Kbase P14367: How to change a key field for all records in a table without causing an infinite loop
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/05/2007 |
|
Status: Verified
FACT(s) (Environment):
Progress 8.x
Progress 9.x
OpenEdge 10.x
SYMPTOM(s):
Infinite loop changing BREAK-BY field into a FOR EACH loop
How to change a key field for all records in a table without causing an infinite loop
FIX:
When using a FOR EACH statement similar to the following where the field being modified is part of the index that was selected by the compiler, an infinite loop can happen because the FOR EACH goes back to the indexes in the database to find the next record to process and if the change to the key field moved the record further down in the index then the record will be processed again:
FOR EACH Customer EXCLUSIVE-LOCK:
ASSIGN CustNum = CustNum + 5000. /* This Record Will Now Be Further Down In The Index And Will Cause An Infinite Loop */
END.
There are two ways to get around this problem.
The first is to use the USE-INDEX phrase in the FOR EACH statement to specify an index to use which does not contain the field(s) you are going to change. For example:
FOR EACH Customer USE-INDEX Name EXCLUSIVE-LOCK:
ASSIGN CustNum = CustNum + 5000. /* CustNum Is Not Contained In The Name Index So No More Infinite Loop Problems */
END.
The second option is to use the DEFINE and OPEN QUERY statements with the PRESELECT option instead of the FOR EACH statement. This works nicely when the table has only one index defined (and that index contains the field being changed):
DEFINE QUERY NoInfiniteLoopsWanted FOR Customer.
OPEN QUERY NoInfiniteLoopsWanted PRESELECT EACH Customer.
GET FIRST NoInfiniteLoopsWanted.
REPEAT WHILE QUERY-OFF-END("NoInfiniteLoopsWanted") = FALSE:
ASSIGN CustNum = CustNum + 5000.
GET NEXT NoInfiniteLoopsWanted.
END.
CLOSE QUERY NoInfiniteLoopsWanted.