OpenEdge Development: Progress 4GL Handbook

Procedure Blocks and Data Access

In the first three chapters of this book, you learn about the basic structure of the Progress 4GL and many of its language constructs. The next two chapters focus on the user interface of a GUI application as you learn to use the AppBuilder to build an application window, examining the language syntax that defines the elements in the window along the way. This chapter returns you to writing 4GL procedures on your own. These procedures contain some business logic that demonstrates in detail a few of the concepts touched on in the previous chapters.

The important concepts of this chapter are ones you’ve already seen in action and learned something about. Indeed, as discussed with the very simplest Progress 4GL procedure—FOR EACH Customer: DISPLAY Customer.—you can hardly write any 4GL code at all without defining blocks and using data access statements.

Nonetheless, this chapter goes into a lot more detail in these areas so that you have a more thorough understanding of these basic building blocks of Progress 4GL procedures. In this chapter, you’ll learn:

All the basic syntax for defining blocks of various kinds in the language, including some that iterate through a set of statements and some that just group them as a common action.
About the scoping of blocks and of the variables and objects you define in them and how scoping affects the way your procedures behave.
A variety of ways to access database data and integrate it into your procedures.

This chapter includes the following sections:

Blocks and block properties
Procedure block scoping
Language statements that define blocks

Blocks and block properties

You saw several different kinds of blocks in the example procedures from the first two chapters. To review them:

Every procedure itself constitutes a block, even just the simplest RUN statement executed from an editor window.
Every call to another procedure, whether internal or external, starts another block. An external procedure can contain one or more internal procedures, each of which forms its own block.
Progress 4GL statements such as FOR EACH and DO define the start of a new block.
A trigger is a block of its own.

Procedure block scoping

Scope is the duration that a resource such as a variable or a button is available to an application. Blocks determine the scope of the resources defined within them.

This section describes some of the basic rules pertaining to procedures and scope. Variable and object definitions are always scoped to the procedure they are defined in. In this book, the word object refers to the various kinds of visual controls you can define, such as buttons and browses, as well as queries and other things you’ll work with later.

You wrote some variable definitions in your very first procedure:

/* h-CustSample.p -- shows a few things about the Progress 4GL */ DEFINE VARIABLE cMonthList AS CHARACTER NO-UNDO      INIT "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC". DEFINE VARIABLE iDays   AS INTEGER   NO-UNDO.   /* used in calcDays proc */

Progress scopes those variables to the procedure. They are available everywhere within that main procedure block and every block it contains, including any internal procedures and triggers. For instance, you could write a line of code inside the calcDays internal procedure that is part of h-CustSample.p, and that code would compile and execute successfully. It would use the same copy of the variable that the enclosing procedure uses.

If you define variables or other objects within an internal procedure, then they are scoped to that internal procedure only and are not available elsewhere in the external procedure that contains it. You can use the same variable name both in an internal procedure and in its containing external procedure. You’ll get a second variable with the same name but a distinct storage location in memory and therefore its own distinct value.

Here are a few simple examples to illustrate this point. In the first, the variable cVar is defined in the external procedure and therefore available, not only within it, but within the internal procedure subproc as well:

/* mainproc.p */ DEFINE VARIABLE cVar AS CHARACTER NO-UNDO. cVar = "Mainproc". /* This is scoped to the whole external procedure. */ RUN subproc. PROCEDURE subproc: DISPLAY cVar. END PROCEDURE.

If you run this procedure, you see the value the variable was given in mainproc as displayed from the contained procedure subproc, as shown in Figure 6–1.

Figure 6–1: Result of variable defined in main procedure only

By contrast, if you define cVar in the subprocedure as well, it can have its own value:

/* mainproc.p */ DEFINE VARIABLE cVar AS CHARACTER NO-UNDO. cVar = "Mainproc". /* This is scoped to the whole external procedure. */ RUN subproc. DISPLAY cVar. PROCEDURE subproc: DEFINE VARIABLE cVar AS CHARACTER NO-UNDO. cVar = "Subproc". END PROCEDURE.

Run this code and you get the same result you did before, as shown in Figure 6–2.

Figure 6–2: Result of variable defined in both main and subprocedures

You assign a different value to the variable in the subprocedure, but because the subprocedure has its own copy of the variable, that value exists only within the subprocedure. Back in the main procedure, the value Mainproc is not overwritten even after the subprocedure call.

As a third example, if you define a new variable in the internal procedure, it won’t be available in the main procedure at all:

/* mainproc.p */ DEFINE VARIABLE cVar AS CHARACTER NO-UNDO. cVar = "Mainproc". /* This is scoped to the whole external procedure. */ RUN subproc. DISPLAY cVar cSubVar. PROCEDURE subproc: DEFINE VARIABLE cVar AS CHARACTER NO-UNDO. DEFINE VARIABLE cSubVar AS CHARACTER NO-UNDO. cVar = "Subproc". cSubVar = "LocalVal". END PROCEDURE.

Here cSubVar is defined only in the internal procedure, so when you try to display it from the main procedure block you get an error, as shown in Figure 6–3.

Figure 6–3: Result of variable defined in the subprocedure only

The main procedure block where the DISPLAY statement is located never heard of cSubVar, because it’s defined in a procedure block inside of that. Even though it’s defined within the same source procedure file, it’s as separate from the main procedure block as it would be if it were in a completely separate external procedure file.

Language statements that define blocks

You’ve seen the FOR EACH block and a simple DO block. This section reviews all the statements that define blocks so that you can then study the differences between them and how each type of block is used.

DO blocks

A DO block is the most basic programming block in the 4GL. The keyword DO starts a block of statements without doing anything else with those statements except grouping them, unless you tell it to. You’ve already used the keyword DO as a block header in a couple of ways, including your trigger blocks, such as this trigger on the Next button in h-CustOrderWin1.w:

DO: GET NEXT CustQuery. IF AVAILABLE Customer THEN DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City Customer.State WITH FRAME CustQuery IN WINDOW CustWin. {&OPEN-BROWSERS-IN-QUERY-CustQuery} END.

This block only assures that all the statements are executed together when the event occurs. If you take a closer look at this trigger, you can use another DO block inside it to correct a small error in the program logic.

To see the error in the program logic:

Run h-CustOrderWin1.w.
Choose the Last button to see the last Customer and its Orders.
Choose the Next button.

There is no next Customer, so the Customer record is not changed. The IF AVAILABLE Customer phrase in the Next button trigger assures that nothing happens if there is no Customer to display. However, the preprocessor {&OPEN-BROWSERS-IN-QUERY-CustQuery}, which opens the Order query, isn’t affected by the IF AVAILABLE Customer check, because it’s a separate statement. So the code opens the Order query even if there’s no current Customer, and therefore displays nothing, as shown in Figure 6–4.

Figure 6–4: Example of empty query result

If there’s no Customer you shouldn’t open the Order query, so you need to bring both statements into the code that is executed only when a Customer is available.

To correct the statement that opens the query:

Create another DO-END block in the trigger:

DO:   GET NEXT CustQuery.   IF AVAILABLE Customer THEN   DO:    DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City          Customer.State       WITH FRAME CustQuery IN WINDOW CustWin.       {&OPEN-BROWSERS-IN-QUERY-CustQuery}   END.      /* END DO IF AVAILABLE Customer */ END.

Now the statement that opens the query won’t execute either if there’s no Customer. This is another illustration of how to use the DO block as a way to group multiple statements together, so that they all execute together or not at all. As this example illustrates, you can nest DO blocks as much as you wish.
When you enter this code in the Section Editor, the edit control recognizes the keyword DO followed by a colon and automatically adds the matching END statement. Just move this statement to where it belongs at the end of the new block. The editor generally matches the indentation of END statements correctly with the start of the block. Make sure you indent all the statements in the block properly so that someone reading your code can easily see how the logic is organized. It’s also a good idea to get into the habit of always putting a comment with each END statement to clarify which block it’s ending. When your code gets complex enough that a single set of nested blocks takes up more than a page, you’ll be grateful you did this. It can prevent all sorts of logic errors.
Make this same DO-END correction to the trigger code for the Prev button.
Save the window as h-CustOrderWin2.w.

The DO block is considered the most basic kind of block because Progress doesn’t provide any additional services to the block by default. There is no automatic looping within the block, no record reading, and no other special processing done for you behind the scenes. However, you can get a DO block to provide some of these services by adding keywords to the DO statement. The following section provides some examples.

Looping with a DO block

If you want to loop through a group of statements some specific number of times, use this form of the DO statement:

DO variable = expression1 TO expression2 [ BY constant ]:

The following example adds up the integers from one through five and displays the total:

DEFINE VARIABLE iCount AS INTEGER    NO-UNDO. DEFINE VARIABLE iTotal AS INTEGER    NO-UNDO. DO iCount = 1 TO 5:     iTotal = iTotal + iCount. END. DISPLAY iTotal.

Figure 6–5 shows the result.

Figure 6–5: Result of looping with a DO block

The starting and ending values can be expressions and not just constants. You can use some value other than one to increment the starting value each time through the loop by using the BY phrase at the end. If the start and end values are variables or other expressions, Progress evaluates them just once, at the beginning of the first iteration of the block. If the values change inside the block, that doesn’t change how many times the block iterates. For example, the following variation uses the variable that holds the total as the starting expression, after giving it an initial value of one using the INIT (or INITIAL) phrase at the end of the definition:

DEFINE VARIABLE iCount AS INTEGER    NO-UNDO. DEFINE VARIABLE iTotal AS INTEGER    NO-UNDO INIT 1. DO iCount = iTotal TO 5:     iTotal = iTotal + iCount. END. DISPLAY iTotal.

When you run this procedure, the changes to the variable iTotal inside the loop don’t affect how the loop executes. The final total is one greater than it was before, as shown in Figure 6–6, only because the initial value of the variable is one instead of the default of zero.

Figure 6–6: Example of looping with a DO block with initial value set to 1

If you want to loop through a group of statements for as long as some logical condition is true, you can use this form of the DO block:

DO WHILE expression:

For the expression, you can use any combination of constants, operators, field names, and variable names that yield a logical (true/false) value. For example:

DEFINE VARIABLE iTotal AS INTEGER    NO-UNDO INIT 1. DO WHILE iTotal < 50:     iTotal = iTotal * 2. END. DISPLAY iTotal.

By its very nature, the DO WHILE statement must evaluate its expression each time through the loop. Otherwise, it would not be able to determine when the condition is no longer true. In this case the variable iTotal, which starts out at one and is doubled until the condition iTotal is less than 50, is no longer true. So what do you expect the final total to be? 32? Not for this code, as shown in Figure 6–7.

Figure 6–7: Example DO WHILE loop result

The reason for this is that Progress evaluates the expression at the beginning of each iteration. As long as it’s true at that time, the iteration proceeds to the end of the block. At the beginning of the final iteration, iTotal equals 32, so the condition is still true. During that iteration it is doubled one last time to 64. At the beginning of the next iteration the condition 64 < 50 is no longer true, and the block terminates.

When you write a DO WHILE block, make sure that there is always a condition that terminates the block. If the condition is true forever, Progress goes into an infinite loop. If this should happen, press CTRL-BREAK on the keyboard to interrupt Progress so that you can go back and correct your mistake.

Using a DO block to scope records and frames

You’ll learn more about record scoping in "Record Buffers and Record Scope." It’s helpful to cover all the syntax forms that can affect it before discussing the meaning of record scope in different situations. For now, you can scope or associate records in one or more tables with a DO block by using the FOR phrase:

DO FOR record [, record ] . . .

Each record names a record you want to work with in the block and scopes it to the block. References within that block to fields the record contains are automatically associated with that record.

You have already seen an example of frame scoping in the code in the enable_UI procedure of h-CustOrderWin2.w and in the button triggers you created based on that code:

DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City         Customer.State       WITH FRAME CustQuery IN WINDOW CustWin.

You can use the phrase WITH FRAME frame-name and, optionally, IN WINDOW window-name to identify which frame you’re talking about when you display fields or take other actions on objects in frames. If you wish, you can scope all the statements in a DO block with a frame by appending the WITH FRAME phrase to the DO statement itself. For example, here’s the BtnNext trigger block again with the frame qualifier moved to the DO statement:

DO:   GET NEXT CustQuery.   IF AVAILABLE Customer THEN   DO WITH FRAME CustQuery:      DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City              Customer.State.     {&OPEN-BROWSERS-IN-QUERY-CustQuery}   END. END.

Whether you name the frame in individual statements or in the block header, this makes sure that Progress understands that you want the fields displayed in the frame where you defined them. If you don’t do this, then depending on the context, Progress might display the fields in a different frame.

To see the result of not explicitly defining a frame scope:

Edit the WITH FRAME phrase out of the BtnNext trigger altogether, so it looks like this:

DO:   GET NEXT CustQuery.   IF AVAILABLE Customer THEN   DO /* WITH FRAME CustQuery */:    DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City          Customer.State.       {&OPEN-BROWSERS-IN-QUERY-CustQuery}   END. END.

Run the window and choose the Next button:

What happened here? Since the DISPLAY statement wasn’t qualified with any indication of where to display the fields, Progress created a brand new frame, laid out the fields in it, and displayed it on top of the other frame in your window. To keep this from happening, make sure that all statements that deal with frames are always clear about where actions should take place. The AppBuilder and the other tools take care of this for you most of the time, but when you add code of your own to procedures, you might need to qualify it with the frame name. Otherwise, Progress displays objects in the frame that is scoped to the nearest enclosing block that defines a frame. If there is no explicit frame definition, then you get the result you just saw here: Progress displays the data in an unnamed default frame. Except in the simplest test procedures, like the procedures this book uses to demonstrate the looping syntax, you never want to use the default frame in your applications.

There are other phrases you can use to qualify a DO block, but they mostly deal with transactions and error handling, which you’ll learn about in "Managing Transactions."

FOR blocks

You’re already familiar with starting a block definition with the FOR keyword. You’ve seen the common FOR EACH table-name form, but there are a number of variations on the FOR statement. In contrast to the DO block, every FOR block provides all of the following services for you automatically:

Loops automatically through all the records that satisfy the record set definition in the block.
Reads the next record from the result set for you as it iterates.
Scopes those records to the block.
Scopes a frame to the block, and you can use the WITH FRAME phrase to specify that frame.
Provides database update services within a transaction.

The FOR statement defines the set of records you want the block to iterate through. Typically you use the EACH keyword to specify this set:

FOR EACH Customer WHERE State = "NH": DISPLAY CustNum Name. END.

When the block begins, Progress evaluates the expression and retrieves the first record that satisfies it. This record is scoped to the entire block. Each time the block iterates, Progress retrieves the next matching record and makes it available to the rest of the block. When the set of matching records is exhausted, Progress automatically terminates the block. You don’t have to add any checks or special syntax to exit the block at this point.

Sorting records by using the BY phrase

As you’ve seen, you can sort the records by using the BY phrase. The default is ascending order, but you cannot use the keyword ASCENDING to indicate this. You’ll get a syntax error, so just leave it out to get ascending order.

To sort in descending order, add the keyword DESCENDING to the BY phrase:

BY field [ DESCENDING ] . . .

To sort on multiple fields, you can repeat the BY phrase.

Joining tables using multiple FOR phrases

You can use multiple record phrases to join records from more than one table:

FOR EACH Customer WHERE State = "NH",     EACH Order OF Customer WHERE ShipDate NE ? :     DISPLAY Customer.Custnum Name OrderNum ShipDate. END.

Figure 6–8 shows the result.

Figure 6–8: Joining records from more than one table

There are several things to note about this example:

Progress retrieves and joins the tables in the order you specify them in, in effect following your instructions from left to right. In this example, it starts through the set of all Customers where the State field = “NH”. For the first record, it defines a set of Orders with the same CustNum value (represented by the OF syntax in this case). For each matching pair, it establishes that Customer record and its Order record and makes them available to all the rest of the statements in the block. Because there are typically multiple Orders for a Customer, the result is a one-to-many join, where the same Customer remains current for multiple iterations through the block, one for each of its Orders, as the output in Figure 6–8 shows.
If you change the sequence of the tables in the statement, Progress might retrieve a very different set of records. For example, using the syntax FOR EACH Order WHERE ShipDate NE ?, EACH Customer OF Order, you would get a list of every Order in the Order table with a ShipDate, plus its (one) matching Customer record. Because there’s just one Customer for each Order, this would result in a one-to-one join with no repeated records.
The default join you get is called an inner join. In matching up Customers to their Orders, Progress skips any Customer that has no Orders with a ShipDate because there is no matching pair of records. The alternative to this type of join, called an outer join, doesn’t skip those Customers with no Orders but instead supplies unknown values from a dummy Order when no Order satisfies the criteria. Progress has an OUTER-JOIN keyword, but you can use it only when you define queries of the kind you’ve seen in DEFINE QUERY statements, not in a FOR EACH block. To get the same effect using FOR EACH blocks, you can nest multiple blocks, one to retrieve and display the Customer and another to retrieve and display its Orders. You did this back in the sample procedure in "Using Basic 4GL Constructs." Generally this is more effective than constructing a query that might involve a one-to-many relationship anyway, because it avoids having duplicated data from the first table in the join.
You can add a WHERE clause and/or a BY clause to each record phrase if you wish. You should always move each WHERE clause up as close to the front of the statement as possible to minimize the number of records retrieved. For example, the statement FOR EACH Customer, EACH Order OF Customer WHERE State = "NH" AND ShipDate NE ? would yield the same result but retrieve many more records in the process. It would go through the set of all Customers, retrieve each Order for each Customer, and then determine whether the State was “NH” and the ShipDate was not unknown. This code is very inefficient. The way the 4GL handles data retrieval is different from SQL, where the table selection is done at the beginning of a SELECT statement and the WHERE clause is after the list of tables. The SQL form depends on the presence of an optimizer that turns the statement into the most efficient retrieval possible. The advantage of the Progress form is that you have greater control over exactly how the data is retrieved. But with this control comes the responsibility to construct your FOR statements intelligently.
Because two records, Customer and Order, are scoped to the FOR block, you might need to qualify field names that appear in both of them. If you just write DISPLAY CustNum you get a syntax error when you try to run the procedure, as shown in Figure 6–9.

Figure 6–9: Syntax error message

Alternatives to the EACH keyword

Sometimes you just want a single record from a table. In that case, you can use the FIRST or LAST keyword in place of EACH, or possibly use no qualifier at all. For example, if you want to retrieve Orders and their Customers instead of the other way around, you can leave out the keyword EACH in the Customer phrase, because each Order has only one Customer:

FOR EACH Order WHERE ShipDate NE ?, Customer OF Order:     DISPLAY OrderNum ShipDate Name. END.

When you use this form, make sure that there is never more than one record satisfying the join. Otherwise, you get a run-time error telling you that there is no unique match.

If you’d like to see just the first Order for each Customer in New Hampshire, you can use the FIRST qualifier to accomplish that:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer:     DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

Be careful, though. This form might not always yield the result you expect, because you have to consider just what is the first Order of a Customer? Progress uses an index of the Order table to traverse the rows.

Using indexes to relate and sort data

A database index allows the database manager to retrieve records quickly by looking up only the values of one or more key fields stored in separate database blocks from the records themselves, which then point to the location where the records are stored.

And what are the indexes of the Order table?

To get the answer to this question, take another look inside the Data Dictionary:

From the AppBuilder menu, select Tools Data Dictionary.
Select the Order table from the list of tables, then choose the Indexes button:



Choose the Index Properties button. The Index Properties dialog box appears and shows the properties of the first index, CustOrder:



This is the index Progress uses to retrieve the Orders, because its first component is the CustNum field, and that is the field it has to match against the CustNum from the Customer table. Since the other component in the index is the OrderNum field, this index sorts records by OrderNum within CustNum so your request for the FIRST Order returns the record with the lowest Order number.
Exit the Data Dictionary before you continue. Otherwise, Progress won’t let you run any procedures because it has a database transaction open and ready to save any changes you might make in the Data Dictionary.

Figure 6–10 shows the beginning of the display from the block FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer.

Figure 6–10: Lowest Order number for each Customer

As expected, you see the Order with the lowest Order number for each Customer. If what you want is the earliest Order date, this output might not give you the information you are looking for.

Adding a BY phrase to the statement doesn’t help because Progress retrieves the records before applying the sort. So if you want the Order with the earliest Order date, it won’t work to do this:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer BY OrderDate: DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

This code retrieves the same Orders as before, but then sorts the whole result set by the OrderDate field, as shown in Figure 6–11.

Figure 6–11: Orders sorted by OrderDate

Using the USE-INDEX phrase to force a retrieval order

If you look at all the indexes for the Order table in the Data Dictionary, you can see that there is also an index called OrderDate that uses the Order field. You can select the index to use when the default choice is not the one you want. Progress does this by adding a USE-INDEX phrase to the record phrase. This form of the FOR EACH statement is guaranteed to return the earliest OrderDate, even if it’s not the lowest OrderNum:

FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer USE-INDEX OrderDate: DISPLAY Customer.CustNum NAME OrderNum OrderDate. END.

The result in Figure 6–12 shows that there is indeed an earlier Order for the first of your Customers that doesn’t have the lowest OrderNum.

Figure 6–12: Earliest Customer Order

Using the LEAVE statement to leave a block

Use the USE-INDEX phrase only when necessary. Progress is extremely effective at choosing the right index, or combination of multiple indexes, to optimize your data retrieval. In fact, there’s an alternative even in the present example that yields the same result without requiring you to know the names and fields in the Order table’s indexes. Take a look at this procedure:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum Name. FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

This code uses nested blocks to retrieve the Customers and Orders separately. These nested blocks allow you to sort the Orders for a single Customer BY OrderDate. You have to define the set of all the Customer’s Orders using the FOR EACH phrase so that the BY phrase has the effect of sorting them by OrderDate. But you really only want to see the first one. To do this, you use another one-word 4GL statement: LEAVE. The LEAVE statement does exactly what you would expect it to: It leaves the block (specifically the innermost iterating block to the LEAVE statement) after displaying fields from the first of the Customer’s Orders. It does not execute any more statements that might be in the block nor does it loop through any more records that are in its result set. Instead, it moves back to the outer block to retrieve the next Customer.

Because the LEAVE statement looks for an iterating block to leave, it always leaves a FOR block. It leaves a DO block only if the DO statement has a qualifier, such as WHILE, that causes it to iterate. If there is no iterating block, Progress leaves the entire procedure.

Using block headers to identify blocks

If it isn’t clear what block the LEAVE statement applies to, or if you want it to apply to some other enclosing block, you can give a block a name followed by a colon and then specifically leave that block. This variant of the procedure has the same effect as the first one:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum NAME. OrderBlock: FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE OrderBlock. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

Just to see the effect of specifying a different block, you can try this variant:

CustBlock: FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum NAME. OrderBlock: FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE CustBlock. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

If you run this code, Progress leaves the outer FOR EACH Customer block after retrieving the first Order for the first Customer because of the change to the LEAVE statement, as shown in Figure 6–13.

Figure 6–13: Specifying a different block

Using NEXT, STOP, and QUIT to change block behavior

There’s another one-word statement that works much like LEAVE and that is NEXT. As you might expect, this statement skips any remaining statements in the block and proceeds to the next iteration of the block. You can qualify it with a block name the same way you do with LEAVE.

There are two more such statements that have increasingly more drastic consequences: STOP and QUIT.

STOP terminates the current procedure, backs out any active transactions, and returns to the Progress session’s startup procedure or to the Editor. You can intercept a STOP action by including the ON STOP phrase on a block header, which defines an action to take other than the default when the STOP condition occurs.

QUIT exits from Progress altogether in a run-time environment and returns to the operating system. If you’re running in a development environment, it has a similar effect to STOP and returns to the Editor or to the Desktop. There is also an ON QUIT phrase to intercept the QUIT condition in a block header and define an action to take other than quitting the session.

Qualifying a FOR statement with a frame reference

This most recent example also has an explicit frame reference in it:

FOR EACH Customer WHERE State = "NH" WITH FRAME f: DISPLAY Customer.CustNum Name. FOR EACH Order OF Customer BY OrderDate: DISPLAY OrderNum OrderDate WITH FRAME f. LEAVE. END. /* END FOR EACH Order */ END. /* END FOR EACH Customer */

Why is this necessary? A FOR EACH block scopes a frame to the block. By default, this is an unnamed frame. Without the specific frame reference, you get two nested frames, one for the Customer and one for its Orders. You saw this already in the sample procedure in "Using Basic 4GL Constructs."

In this case, that isn’t what you want. Because there’s only one Order of interest for each Customer, you want to display all the fields together in the Customer frame. To get this effect, you have to override the default behavior and tell Progress to use the frame from the Customer block to display the Order fields. That is what these two references to WITH FRAME f do for you. Progress just keeps making room for new fields in the frame as it encounters them (unless you tell it exactly where to put each field, which is the norm in your GUI applications that use the AppBuilder to lay things out).

REPEAT blocks

There’s a third kind of iterating block that is in between DO and FOR in its effects, the REPEAT block. It supports just about all the same syntax as a FOR block. You can add a FOR clause for one or more tables to it. You can use a WHILE phrase or the expression TO expression phrase. You can scope a frame to it.

A block that begins with the REPEAT keyword shares these default characteristics with a FOR block:

It is an iterating block.
It scopes a frame to the block.
It scopes records referenced in the REPEAT statement to the block.
It provides transaction processing if you update records within the block.

By contrast, it shares this important property with a DO block: It does not automatically read records as it iterates.

So what is a REPEAT block for? It is useful in cases where you need to process a set of records within a block but you need to navigate through the records yourself, rather than simply proceeding to the next record automatically on each iteration. The sample procedure starting in the "Index cursors" shows you an example of where to use the REPEAT block.

One of the common ways to use a REPEAT block in older character applications is to repeat a data entry action until the user hits the ESCAPE key to end. Here’s a very simple but powerful example:

REPEAT: INSERT customer EXCEPT comments WITH 2 COLUMNS. END.

You haven’t seen the INSERT statement before and you won’t see much of it again, even though it’s one of the most powerful statements in the language. Figure 6–14 shows what you get from that one simple statement:

Figure 6–14: Results of using the INSERT statement

It’s a complete data entry screen, complete with initial values for fields that have one. The field help displays at the bottom of the window. Inside a REPEAT loop, this lets you create one new Customer record after another until you’re done and you press ESCAPE.

Why won’t you see the INSERT statement again? Because INSERT is one of those statements that mixes up all aspects of Progress, from the user interface to saving the data off into the database. And in a modern GUI application, you need to separate out all those things into separate parts of your application. You’ll look at database updates in "Updating Your Database and Writing Triggers," so there’s no need to go into this example any further beyond understanding what the REPEAT block does around the statement.

Using the PRESELECT keyword to get data in advance

One typical use of the REPEAT block that is still valuable is when you use it with a construct called a PRESELECT. To understand this usage, you need to think a little about what happens when an iterating block like a FOR EACH block begins. Progress evaluates the record retrieval the FOR EACH statement defines. It then goes out to the database and retrieves the first record of the set of related records that satisfies the statement and makes the record available to the block. When the block iterates, Progress goes and gets the next record. As long as it’s possible to identify what the first and the next records are by using one or more indexes, Progress doesn’t bother reading all the records in advance. It just goes out and gets them when the block needs them.

If you specify a BY clause that requires a search of the database that an index can’t satisfy, Progress has no choice but to retrieve all the records in advance and build up a list in sort order. But this is not ordinarily the case. It’s much more efficient simply to get the records when you need them.

Sometimes, though, you need to force Progress to get all the records that satisfy the FOR EACH statement in advance, even when the sort order itself doesn’t require it. For example, if it’s possible that you will modify an indexed field in some of the records in such a way that they would appear again later in the retrieval process, you need to make sure that the set of records you’re working with is predetermined. The PRESELECT keyword gives you this. It tells Progress to build up a list of pointers to all the records that satisfy the selection criteria before it starts iterating through the block. This assures you that each record is accessed only once.

In summary, a REPEAT block does everything a FOR block does, but it does not automatically advance to the next record as it iterates. You should use a REPEAT block in cases where you want to control the record navigation yourself, typically using the FIND statement described in the next section. It provides you with record, frame, and transaction scoping.

Because it provides all these services, a REPEAT block is relatively expensive compared to a DO block. Use the simpler DO block instead of a REPEAT block, unless you need the record-oriented services provided by the REPEAT block.

Data access without looping – the FIND statement

In addition to all of these ways to retrieve and iterate through a set of related records, Progress has a very powerful way to retrieve single records without needing a query or result set definition of any kind. This is the FIND statement.

The FIND statement uses this basic syntax:

FIND [ FIRST | NEXT| PREV | LAST ] record [ WHERE . . .]      [ USE-INDEX index-name ]

Using the FIND statement to fetch a single record from the database is pretty straightforward. This statement reads the first Customer and makes it available to the procedure:

FIND FIRST Customer.

This statement fetches the first Customer in New Hampshire:

FIND FIRST Customer WHERE State = “NH”.

It gets more interesting when you FIND the NEXT record or the PREV record. This should immediately lead you to the question: NEXT or PREV relative to what? Even the FIND FIRST statement has to pick a sequence of Customers in which one of them is first. Although it might seem intuitively obvious that Customer 1 is the first Customer, given that the Customers have an integer key identifier, this is the record you get back only because the CustNum index is the primary index for the table (you could verify this by looking in the Data Dictionary). Without any other instructions to go on, and with no WHERE clause to make it use another index, the FIND statement uses the primary index. You can use the USE-INDEX syntax to force Progress to use a particular index.

If you include a WHERE clause, Progress chooses one or more indexes to optimize locating the record. This might have very counter-intuitive results. For example, here’s a simple procedure with a FIND statement:

FIND FIRST Customer. DISPLAY CustNum Name Country.

Figure 6–15 shows the expected result.

Figure 6–15: Result of a simple FIND procedure

You can see that Customer 1 is in the USA. Here’s a variation of the procedure:

FIND FIRST Customer WHERE Country = "USA". DISPLAY CustNum Name Country.

Figure 6–16 shows the not-so-expected result.

Figure 6–16: Result of variation on the simple FIND procedure

What happened here? If Customer 1 is the first Customer, and Customer 1 is in the USA, then why isn’t it the first Customer in the USA? Progress uses an index in the Country field to locate the first Customer in the USA, because that’s the most efficient way to find it. That index, called the CountryPost index, has the PostalCode as its secondary field. If you rerun this procedure again and ask to see the PostalCode field instead of the Name field, you’ll see why it came up first using that index, as shown in Figure 6–17.

Figure 6–17: Result of the simple FIND procedure using PostalCode

The PostalCode is blank for this Customer, so it sorts first. Even if there is no other field in the index at all, that would only mean that the order of Customers within that index for a given country value would be undetermined. Only if the CustNum field is the next index component could you be sure that Customer 1 would come back as the first Customer in the USA.

These examples show that you must be careful when using any of the positional keywords (FIRST, NEXT, PREV, and LAST) in a FIND statement to make sure you know how the table is navigated.

Index cursors

To understand better how Progress navigates through a set of data, you need to understand the concept of index cursors. When you retrieve a record from the database using any of the statements you’ve seen in this chapter, Progress keeps track of the current record position using an index cursor—a pointer to the record, using the location in the database indexes of the key value used for retrieval.

When you execute the statement FIND FIRST Customer, for example, Progress sets a pointer to the record for Customer 1 within the CustNum index. If you execute the statement FIND FIRST Customer WHERE Country = “USA”, Progress points to Customer 1025 through the CountryPost index.

When you execute another FIND statement on the same table using one of the directional keywords, Progress can go off in any direction from the current index cursor location, depending on the nature of the statement. By default, it reverts to the primary index. Here’s an example that extends the previous one slightly:

FIND FIRST Customer WHERE Country = "USA". DISPLAY CustNum NAME Country. REPEAT:      FIND NEXT Customer NO-ERROR.      IF AVAILABLE Customer THEN           DISPLAY CustNum Name FORMAT "x(20)" Country PostalCode.      ELSE LEAVE. END.

Using the FIND statement in a REPEAT block

Notice the use of the REPEAT block to cycle through the remaining Customers. Within that block, you must write a FIND statement to get the next Customer because the REPEAT block itself, unlike the FOR EACH block, does not do the navigation for you. Also, the REPEAT block does not automatically terminate when the end of the Customers is reached, so you need to program the block with these three actions:

You must do the FIND with the NO-ERROR qualifier at the end of the statement. This suppresses the error message that you would ordinarily get when there is no next Customer.
You must use the AVAILABLE keyword to check for the presence of a Customer and display fields only if it evaluates to true.
You must write an ELSE statement to match the IF-THEN statement, to leave the block when there is no Customer available. Otherwise, your block goes into an infinite loop when it reaches the end of the Customer records. And notice that this truly is a separate statement. The IF-THEN statement ends with a period and the ELSE keyword begins a statement of its own.

All of these are actions that the FOR EACH block does for you as it reads through the set of Customers. In the REPEAT block, though, where you’re doing your own navigation, you need to do these things yourself.

Remember also that the REPEAT block scopes the statements inside the block to its own frame, unless you tell it otherwise. Therefore, you get one frame for the FIRST Customer and a new frame for all the Customer records retrieved within the REPEAT block.

The keyword AVAILABLE is a Progress 4GL built-in function, so its one argument properly belongs in parentheses, as in IF AVAILABLE (Customer). However, to promote the readability of the 4GL statement, the syntax also accepts the form as if it were a phrase without the parentheses, as in IF AVAILABLE Customer. This alternative is not generally available with other built-in functions.

Finally, the FORMAT “X(20)” phrase reduces the display size of the Name field from its default (defined in the Data Dictionary) of 30 characters, to make room for the PostalCode field.

Switching indexes between FIND statements

So what Customer do you expect to see as the next Customer after retrieving the first Customer using the CountryPost index (because of the WHERE clause)? If you remember that the default is always to revert to the primary index, then the result shown in Figure 6–18 should be clear.

Figure 6–18: Result of using the primary index

Looking at the sequence of records displayed in the frame for the REPEAT block, it’s clear that Progress is using the primary index (the CustNum index) to navigate through the records. This is unaffected by the fact that the initial FIND was done using the CountryPost index, because of its WHERE clause.

What if you want to continue retrieving only Customers in the USA? In this case, you need to repeat the WHERE clause in the FIND statement in the REPEAT block:

FIND FIRST Customer WHERE Country = "USA". DISPLAY CustNum NAME Country. REPEAT:      FIND NEXT Customer WHERE Country = "USA" NO-ERROR.      IF AVAILABLE Customer THEN           DISPLAY CustNum NAME FORMAT "x(20)" Country PostalCode.      ELSE LEAVE. END.

Each FIND statement is independent of any other FIND statement, even if it refers to the same table, so the WHERE clause does not carry over automatically. If you do this, then Progress continues to use the CountryPost index for the retrieval, as the output in Figure 6–19 shows.

Figure 6–19: Result of using the CountryPost index for record retrieval

Because the PostalCode is the second field in the index used, the remaining records come out in PostalCode order.

Using a USE-INDEX phrase to force index selection

You can also force a retrieval sequence with the USE-INDEX phrase. For instance, if you want to find the next set of Customers based on the Customer name, you can use the Name index, which contains just that one field:

FIND FIRST Customer WHERE Country = "USA". DISPLAY CustNum NAME Country. REPEAT:      FIND NEXT Customer WHERE Country = "USA" USE-INDEX NAME NO-ERROR.      IF AVAILABLE Customer THEN           DISPLAY CustNum NAME FORMAT "x(20)" Country PostalCode.      ELSE LEAVE. END.

The output shown in Figure 6–20 confirms that Progress is walking through the records in Name order, starting with the name of the first Customer in the USA.

Figure 6–20: Result of forcing index selection

This technique can be very valuable in expressing your business logic in your procedures. You might need to identify a record based on one characteristic and then retrieve all other records (or perhaps just one additional record) based on some other characteristic of the record you first retrieved. This is one of the most powerful ways in which Progress lets you define your business logic without the overhead and cumbersome syntax required to deal with all data access in terms of sets.

Doing a unique FIND to retrieve a single record

Very often you just need to retrieve a single record using selection criteria that identify it uniquely. In this case, you can use a FIND statement with no directional qualifier. For example, you can identify a Customer by its Customer number. This is a unique value, so you can use the following FIND statement:

FIND Customer WHERE CustNum = 1025. DISPLAY CustNum NAME Country.

Figure 6–21 shows the result.

Figure 6–21: Result of unique FIND

You need to be sure when you do this that only one record satisfies the selection criteria. Otherwise, you get an error at run time.

There’s also a shorthand for this FIND statement:

FIND Customer 1025.

You can use this shorthand form if the primary index is a unique index (with no duplication of values), the primary index contains just a single field, and you want to retrieve a record using just that field. You can only use this form when all these conditions are true, so it’s not likely to be one you use frequently. Also, this shorthand form makes it harder to determine your criteria. It can break due to changes to the data definitions (for example, if someone went in and added another field to the CustNum index), so it’s better to be more specific and use a WHERE clause to identify the record.

Using the CAN-FIND function

Often you need to verify the existence of a record without retrieving it for display or update. For example, your logic might need to identify each Customer that has at least one Order, but you might not care about retrieving any actual Orders. To do this, you can use an alternative to the FIND statement that is more efficient because it only checks index entries wherever possible to determine whether a record exists, without going to the extra work of retrieving the record itself. This alternative is the CAN-FIND built-in function. CAN-FIND takes a single parameter, which can be any record selection phrase. The CAN-FIND function returns true or false depending on whether the record selection phrase identifies exactly one record in the database.

For example, imagine that you want to identify all Customers that placed Orders as early as 1997. You don’t need to retrieve or display the Orders themselves, you just need to know which Customers satisfy this selection criterion. Here’s a simple procedure that does this:

FOR EACH Customer WHERE Country = "USA":    IF CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98)         THEN DISPLAY CustNum Name.    ELSE DISPLAY CustNum "No 1997 Orders" @ Name. END.

This procedure uses a little display trick you haven’t seen before. If the Customer has any Orders for 1997, then the procedure displays the Customer name. Otherwise, it displays the text phrase No 1997 Orders. If you include that literal value in the DISPLAY statement, it displays in its own column as if it were a field or a variable. To display it in place of the Name field, use the at-sign symbol (@). Figure 6–22 shows the result.

Figure 6–22: Result of CAN-FIND function procedure

The CAN-FIND function takes the argument FIRST Order OF Customer WHERE OrderData < 1/1/98. Why is the FIRST keyword necessary? The CAN-FIND function returns true only if exactly one record satisfies the selection criteria. If there’s more than one match, then it returns false—without error—just as it would if there was no match at all. For example, if you remove the FIRST keyword from the example procedure and change the literal text to be No unique 1997 Order, and rerun it, then you see that most Customers have more than one Order placed in 1997:

FOR EACH Customer WHERE Country = "USA":    IF CAN-FIND (Order OF Customer WHERE OrderDate < 1/1/98)         THEN DISPLAY CustNum Name.    ELSE DISPLAY CustNum "No unique 1997 Order" @ Name. END.

After you page through the results, you see just a few records that don’t satisfy the criteria, as shown in Figure 6–23.

Figure 6–23: Result of CAN-FIND function procedure without FIRST keyword

Because you don’t get an error if there’s more than one match, it’s especially important to remember to define your selection criteria so that they identify exactly one record when you want the function to return true.

The CAN-FIND function is more efficient than the FIND statement because it does not actually retrieve the database record. If the selection criteria can be satisfied just by looking at values in an index, then it doesn’t look at the field values in the database at all. However, this means that the record referenced in the CAN-FIND statement is not available to your procedure. For example, this variation on the example tries to display the OrderDate from the Order record as well as the Customer fields:

FOR EACH Customer WHERE Country = "USA":    IF CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98)         THEN DISPLAY CustNum Name OrderDate.    ELSE DISPLAY CustNum "No 1997 Orders" @ Name. END.

This results in the error shown in Figure 6–24, because the Order record is not available following the CAN-FIND reference to it.

Figure 6–24: CAN-FIND error message

If you need the Order record itself then you must use a form that returns it to you:

FOR EACH Customer WHERE Country = "USA":    FIND FIRST Order OF Customer WHERE OrderDate < 1/1/98 NO-ERROR.    IF AVAILABLE Order THEN         DISPLAY Customer.CustNum NAME OrderDate.    ELSE DISPLAY "No 1997 Orders" @ NAME. END.

When you run this code, you see the OrderDate as well as the Customer fields except in those cases where there is no Order from 1997, as shown in Figure 6–25.

Figure 6–25: FIND FIRST Order result

The samples so far have shown the CAN-FIND function in an IF-THEN statement. You can also use it anywhere where a logical (true/false) expression is valid in a WHERE clause, such as this:

FOR EACH Customer WHERE Country = "USA" AND      CAN-FIND (FIRST Order OF Customer WHERE OrderDate < 1/1/98):      DISPLAY Customer.CustNum NAME. END.

The next chapter continues the discussion on building complex procedures, with details on record buffers and record scope.