CODEWITHSUNDEEP
openedgeprogress-4gl
Thiru
Thiru

Reputation: 251

For Each ,For First

What is the meaning of For each and For First.. Example below 

FOR EACH <db> NO-LOCK,
     FIRST <db> OF <db> NO-LOCK:

DISPLAY ..

Also why we need to use NO-LOCK for every table for every time.

Upvotes: 1

Views: 818

Answers (3)

Tom Bascom
Tom Bascom

Reputation: 14020

FOR EACH table

Selects a set of records and starts a block to process those records.

NO-LOCK means what it says, the records are retrieved from the database without any record locking. So you might get a "dirty read" (uncommitted data) and someone else might change the data while you are looking at that record.

That sounds awful but, in reality, NO-LOCK reads are almost always what you want to use. If you do need to update a NO-LOCK record you can just FIND CURRENT with a lock.

FOR EACH NO-LOCK can return large numbers of records in a single network message whereas the other lock types are one record at a time - this makes NO-LOCK quite a bit faster for many purposes. And even without the performance argument you probably don't want to be taking out large numbers of locks and preventing other users running inquiries all the time.

Your example lacks a WHERE clause so, by default, every record in the table is returned using the primary index. If you specify a WHERE clause you will potentially only have a subset of the data to loop through and the index selection may be impacted. You can also add a lot of other options like BY to specify sort order.

FOR FIRST is somewhat similar to FOR EACH except that you only return, at most, a single record. Even if the WHERE clause is empty or would otherwise specify a larger result set. BUT BE CAREFUL - the "FIRST" is deceptive. Even if you specify a sort order using BY the rule is "selection, then sorting". At most only one record gets selected so the BY doesn't matter. The index dictated by the WHERE (or lack of a WHERE) determines the sort order. So if your request something like:

FOR FIRST customer NO-LOCK BY discount:
  DISPLAY custNum name discount.
END.

You will fetch customer #1, not customer #41 as you might have expected. (Try the code above with the sports2000 database. Replace FIRST with EACH in a second run.)

FOR EACH table1 NO-LOCK,
   FIRST table2 NO-LOCK OF table1:

or

FOR EACH customer NO-LOCK,                                                                                                                                      
  FIRST salesRep NO-LOCK OF customer:                                                                                                                           
  DISPLAY custnum name customer.salesRep.                                                                                                                       
END.

Is a join. The OF is a shortcut telling the compiler to find fields that the two tables have in common to build an implied WHERE clause from. This is one of those "makes a nice demo" features that you don't want to use in real code. It obfuscates the relationship between the tables and makes your code much harder to follow. Don't do that. Instead write out the complete WHERE clause. Perhaps like this:

for each customer no-lock,                                                                                                                                      
  first salesRep no-lock where sakesRep.salesRep = customer.salesRep:                                                                                                                           
  display custnum name customer.salesRep.                                                                                                                       
end.

Upvotes: 0

Mike Fechner
Mike Fechner

Reputation: 7192

Let's answer by giving an example based on the Progress demo DB:

FOR EACH Customer WHERE Customer.Country = "USA" NO-LOCK,
    FIRST Salesrep WHERE Salesrep.salesrep = Customer.Saleserp:

/* your code block */

END.

The FOR EACH Block is an iterating block (loop) that integrates data access (and a few more features like error handling and frame scoping if you want to go that far back).

So the code in "your code block" is executed for every Customer record matching the criteria and it also fetches the matching Salesrep records. The join between Customer and Salesrep is an inner join. So you'll only be processing Customers where the Salesrep exists as well.

Upvotes: 7

nemanja228
nemanja228

Reputation: 551

FOR statement documentation (includes EACH and FIRST keywords)

NO-LOCK documentation

Google is your friend and documentation on packages is usually quite user-friendly. Try not to ask questions that can be solved by simple search on StackOverflow.

Upvotes: 2

Related Questions