OpenEdge Development: Progress 4GL Handbook

Managing Transactions

This chapter continues the discussion of database transactions. It includes the following sections:

Controlling the size of a transaction
Using the UNDO statement

Controlling the size of a transaction

You’ve already learned which statements start a transaction automatically. To summarize, these are:

FOR EACH blocks that directly update the database.
REPEAT blocks that directly update the database.
Procedure blocks that directly update the database.
DO blocks with the ON ERROR or ON ENDKEY qualifiers (which you’ll learn more about later) that contain statements that update the database.

You can also control the size of a transaction by adding the TRANSACTION keyword to a DO, FOR EACH, or REPEAT block. This can force a transaction to be larger, but because of the statements that start a transaction automatically, you cannot use it to make a transaction smaller than Progress would otherwise make it.

Take another look at the update procedure saveOrder, in the sample logic procedure orderlogic.p, to see how transaction blocks are affected by changes to the procedure.

As written, there is a DO TRANSACTION block around the whole update of both the Order and any modified OrderLines:

DO TRANSACTION ON ERROR UNDO, LEAVE:   DO:          /* Order update block */   END.    FOR EACH ttOline:           /* OrderLine update block */    END. END.  /* END Transaction block. */

The update of the Order and its OrderLines happens as a single transaction. If any errors are encountered in any of the updates, the entire transaction is backed out.

To verify this, you can generate a listing file as you’ve done earlier:

When you run this COMPILE statement, your listing file tells you, among other things, where all the transactions begin and end. This is very valuable information. You should always use a listing file in any complex procedure to make sure that your record scope and transaction scope are as you intended.

Taking a look at this listing file, you can see that the DO TRANSACTION block is a top-level block within its procedure, marked with a 1. The DO block inside it, where the Order is actually updated, is block level 2:

144   1    DO TRANSACTION ON ERROR UNDO, LEAVE:     145   1       FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.     146   1       IF AVAILABLE (ttOrder) THEN            /* If this rec is there then the Order was updated on the client. */     147   1                 148   2      DO:

Further down, you can see that the FOR EACH block that operates on the OrderLines is also a nested block, at level 2 within the main DO TRANSACTION block:

164   2     FOR EACH ttOline WHERE ttOline.TransType = "":     165   2          /* Bring the updated version into the other buffer. */

Now if you look at the end of the file, you see a summary of all the blocks. Here’s an excerpt from that part of the listing. It shows that the procedure blocks for the internal procedures fetchOrder and saveOrder are not transaction blocks:

File Name       Line Blk.     Type Tran         Blk. Label -------------------- ---- --------- ---- -------------------------------- ...ter8\orderlogic.p   82 Procedure No   Procedure fetchOrder ...ter8\orderlogic.p  111 For       No ...ter8\orderlogic.p  124 Procedure No   Procedure saveOrder      Buffers: bUpdateOrder               sports2000.Order

This is a good thing. You never want your transactions to default to the level of a procedure, because they are likely to be larger than you want them to be. This means that record locks are held longer than necessary and that more records might be involved in a transaction than you intended.

Next you see that Progress identifies the first DO block at line 144 as a transaction block. This is because it has an explicit TRANSACTION qualifier on it. The nested DO block two lines later is not a transaction block because a DO block by itself does not mark a transaction.

The FOR EACH block at line 164 is also marked as a transaction block:

...ter8\orderlogic.p  144 Do        Yes ...ter8\orderlogic.p  146 Do        No ...ter8\orderlogic.p  151 Do        No ...ter8\orderlogic.p  156 Do        No ...ter8\orderlogic.p     164 For       Yes     Buffers: sports2000.OrderLine              bUpdateOline

What does this mean? Is this really a separate transaction? The answer is no, because the FOR EACH block is nested inside the larger DO TRANSACTION block. This code tells you that the FOR EACH block would be a transaction block (because this is the nature of FOR EACH blocks that perform updates). However, because it is nested inside a larger transaction, it becomes a subtransaction. Progress can back out changes made in a subtransaction within a larger transaction when an error occurs, and you can also do this yourself, as you’ll learn a little later in the "Subtransactions" .

Making a transaction larger

In this section, you experiment with increasing the size of a transaction.

To see the effect of removing the DO TRANSACTION block from saveOrder:

Comment out the DO TRANSACTION statement and the matching END statement at the end of the procedure.
Recompile and generate a new listing file.
Take a look at the final section. You can see that, without the DO TRANSACTION block, the entire saveOrder procedure become a transaction block:

File Name       Line Blk.    Type Tran       Blk. Label -------------------- ---- --------- ---- ------------------------------ ...ter8\orderlogic.p   82 Procedure No    Procedure fetchOrder ...ter8\orderlogic.p  111 For       No ...ter8\orderlogic.p  124 Procedure Yes   Procedure saveOrder     Buffers: bUpdateOrder              sports2000.Order ...ter8\orderlogic.p  146 Do        No ...ter8\orderlogic.p  151 Do        No ...ter8\orderlogic.p  156 Do        No ...ter8\orderlogic.p  164 For       Yes     Buffers: sports2000.OrderLine              bUpdateOline

Why did this happen? A DO block by itself, without a TRANSACTION or ON ERROR qualifier, does not start a transaction. Therefore, Progress has to fall back on the rule that the entire procedure becomes the transaction block. In this particular case, this does not really make a big difference because the update code for Order and OrderLine is essentially the only thing in the procedure. But, as emphasized before, this is a very dangerous practice and you should always avoid it. If you generate a listing file and see that a procedure is a transaction block, you need an explicit transaction within your code. In fact, you should always have explicit transaction blocks in your code and then verify that statements outside that block are not forcing the transaction to be larger than you intend.

Making a transaction smaller

Try one more experiment.

To decrease the size of a transaction, uncomment the DO TRANSACTION statement and its matching END statement. Move the END statement up to just after the end of the DO block for the Order record. Now your procedure structure looks like this:

DO TRANSACTION ON ERROR UNDO, LEAVE:         DO:                /* Order update block */       END. END.     /* END Transaction block. – This used to be at the end of the proc. */ FOR EACH ttOline:                /* OrderLine update block */ END.

How does this affect your transactions? Now that you removed the FOR EACH block from the larger transaction, it becomes a true transaction block of its own at block level 1, as the new listing file shows:

166     1     FOR EACH ttOline WHERE ttOline.TransType = "":   167     1    /* Bring the updated version into the other buffer. */

This means that the two transaction blocks identified by the listing are now separate and distinct transactions:

File Name         Line Blk.   Type Tran            Blk. Label -------------------- ---- --------- ---- -------------------------------- ...ter8\orderlogic.p   82 Procedure No   Procedure fetchOrder ...ter8\orderlogic.p  111 For       No ...ter8\orderlogic.p  124 Procedure No   Procedure saveOrder     Buffers: bUpdateOrder              sports2000.Order ...ter8\orderlogic.p  144 Do        Yes ...ter8\orderlogic.p  146 Do        No ...ter8\orderlogic.p  151 Do        No ...ter8\orderlogic.p  156 Do        No ...ter8\orderlogic.p  166 For       Yes   Buffers: sports2000.OrderLine            BUpdateOline

If Progress encounters an error in the update of the Order, it leaves that block and continues on with the OrderLines. If the OrderLines update succeeds, then the newly modified OrderLines are in the database, but the failed Order update is not. Likewise, if the Order block succeeds but there is an error in the OrderLines block, then the Order update is in the database but the OrderLines update is not. You must decide when you put your procedures together how large your updates need to be to maintain your data integrity. In general, you should work to keep your transactions as small as possible so that you do not lock more records or lock records for longer periods of time than is absolutely necessary. But your transactions must be large enough so that related changes that must be committed together either all get into the database or are all rejected.

Transactions and trigger and procedure blocks

If your code starts a transaction in one procedure and then calls another procedure, whether internal or external, the entire subprocedure is contained within the transaction that was started before it was called. If a subprocedure starts a transaction, then it must end within that subprocedure as well, because the beginning and end of the transaction are always the beginning and end of a particular block of code.

Since a database trigger procedure is an external procedure called under special circumstances—in response to an update event elsewhere in the application—it follows the same rule. There is always a transaction active when a database trigger is called (except in the case of the FIND trigger), so the trigger procedure is entirely contained within the larger transaction that caused it to be called.

Trigger blocks beginning with the ON event phrase are treated the same as an internal procedure call. If there is a transaction active when the block is executed in response to the event, then its code is contained within that transaction.

Checking whether a transaction is active

You can use the built-in TRANSACTION function in your procedures to determine whether a transaction is currently active. This logical function returns true if a transaction is active and false otherwise. You might use this, for example, in a subprocedure that is called from multiple places and which needs to react differently depending on whether its caller started a transaction. (When you have a single procedure, you had better not need this function to tell you whether you’ve started a transaction or not!)

The NO-UNDO keyword on temp-tables and variables

You were instructed very early on in this book to define almost all variables using the NO-UNDO keyword. Also, in this chapter’s example, the temp-tables for Customer, Order, and OrderLine are NO-UNDO. Why is this?

When you define variables, Progress allocates what amounts to a record buffer for them, where each variable becomes a field in the buffer. There are in fact two such buffers, one for variables whose values can be undone when a transaction is rolled back and one for those that can’t. There is extra overhead associated with keeping track of the before-image for each variable that can be undone, and this behavior is rarely needed. If you are modifying a variable inside a transaction block (and it is important for your program logic that sets that variable’s value that it be undone if the transaction is undone), then you should define the variable without the NO-UNDO keyword.

Here’s a trivial example of when this might be useful. This little procedure lets you create and update Customer records in a REPEAT loop, and then shows you how many were created:

DEFINE VARIABLE iCount AS INTEGER. REPEAT :     CREATE Customer.     iCount = iCount + 1.     DISPLAY Customer.CustNum WITH FRAME CustFrame 5 DOWN.     UPDATE Customer.Name WITH FRAME CustFrame. END. DISPLAY iCount "records created." WITH NO-LABELS.

The REPEAT block defines the scope of a transaction. Each time Progress runs through the block is a separate transaction and, as each iteration completes successfully, the record created in that block is committed to the database. If you run the procedure, the REPEAT loop lets you enter Customers until you press ESCAPE, which in an OpenEdge session running on MS Windows is mapped to the END-ERROR key label. Each time it goes through the block, Progress creates a Customer, displays its new CustNum (assigned by the CREATE trigger for Customer), and prompts you for a Name. It is at this point that you can press ESCAPE when you’re done entering Customers. This undoes and leaves the current iteration of the REPEAT block. Because each iteration of the REPEAT block is a separate transaction, the final Customer you created is undone—it’s erased from the database.

But what about the iCount variable? Since this was defined as undoable (the default), the final change to its value is rolled back along with everything else, and its value is the actual number of records created and committed to the database, as shown in Figure 18–1.

Figure 18–1: Example of creating and updating Customer records

As you enter the REPEAT block for the third time, Progress creates a third new Customer and increments iCount from 2 to 3. When you press ESCAPE, the final iteration of the REPEAT block is undone, and Customer 3115 is deleted from the database. The value of iCount is restored to 2, which was its value before that iteration of the block. (Note that the key value 3115 cannot be undone or reused, however, because it comes from a database sequence, and for performance reasons, these are not under transaction control).

If the variable were defined NO-UNDO, then after it is incremented from 2 to 3 its value would not be restored when the final transaction is undone, and the final DISPLAY statement would show its value as 3.

Relatively few variables need to be undone in this way, so to maximize performance you should define all other variables as NO-UNDO. (The Progress Editor macros do this for you when you type DVI, DVC, etc. into the Editor.) So why isn’t NO-UNDO the default? Quite simply, it didn’t at first occur to the developers of the language that most variables should be defined this way, so the first versions of Progress went out with undo variables as the default. Because of the commitment to maintaining the behavior of existing applications, the default has not changed with new releases.

The same consideration applies to temp-tables. Because temp-tables are really database tables that are not stored in a persistent database, they have almost all of the capabilities of true database tables, including the ability to be written to disk temporarily, the ability to have indexes, and the ability to participate in transactions. As with variables, your temp-tables are more efficient if you define them as NO-UNDO so that they are left out of transactions. Consider whenever you define a temp-table whether it really needs to be part of your transactions. If not, include the NO-UNDO keyword in its definition.

Using the UNDO statement

Progress undoes a transaction automatically if it detects an error at the database level, for example, because of a unique key violation. In many cases, your application logic also wants to undo a transaction when you detect a violation of your business logic. The UNDO statement lets you control when to cancel the effects of a transaction on your own. It also lets you define just how much of your procedure logic to undo.

Here is the syntax of the UNDO statement:

UNDO [ label ] [ , LEAVE [ label2 ] |, NEXT [ label2 ] |, RETRY [ label ]                | , RETURN [ ERROR | NO-APPLY ] [ return-value ] ]

In its simplest form, you can use the UNDO keyword as its own statement. In this case, Progress undoes the innermost containing block with the error property, which can be:

A FOR block.
A REPEAT block.
A procedure block.
A DO block with the TRANSACTION keyword or ON ERROR phrase.

You can change this default by specifying a block label to undo. You can place a block name anywhere in your procedure. The block name must adhere to the same rules as a variable name and it must be followed by a colon. If you use this block name in an UNDO statement, it identifies how far back you want Progress to undo transactional work.

The default action on an UNDO is to attempt to retry the current block. In Progress, a block of code that prompts the user for field values can be retried; that is, having entered invalid data, the user can re-enter different values. If you are writing well-structured procedures that do not mix user interface elements with database logic, then retrying a block never results in a user entering different values. Progress recognizes this and changes the action to a NEXT of an iterating block, or a LEAVE of a noniterating block, so this is effectively the default for transactions not involving user interface events.

You can change this default action as well. If you specify LEAVE, you can name the block to leave. This defaults to the block you undo.

If you specify NEXT within an iterating block, then after the UNDO Progress proceeds to the next iteration of either the block whose label you specify or the block you undo as a default. Use the NEXT option if each iteration of a repeating block is its own transaction (the default) and if you want Progress to keep trying to apply changes to other records, for example, as Progress walks through a set of records in a FOR EACH block.

If you specify RETRY, then you can retry either the current block (the default) or the same block you applied the UNDO statement to. Again, in a properly structured application, you do not need to use the RETRY option.

Finally, you can RETURN out of the current procedure. You can RETURN ERROR, which raises the Progress error condition, or you can use RETURN NO-APPLY to cancel the effect of the last user-interface event. Once again, this is not an option you would normally use in server-side business logic. The RETURN option can also specify a return-string, which becomes the RETURN-VALUE in the procedure you return to.

You can also specify UNDO as an option on a DO, FOR, or REPEAT block, as you did in your saveOrder example procedure:

DO TRANSACTION ON ERROR UNDO, LEAVE:

Using the UNDO statement in the sample procedure

In this section, you see how the UNDO statement can affect the operation of the saveOrder procedure in orderlogic.p. You’ll try several variations to the business logic, in succession, to illustrate the ways you can control the scope of your transaction and how to react when it fails.

Using UNDO, LEAVE on a block

In this section, you try an example that uses UNDO, LEAVE on a block.

To undo and leave the block that updates the OrderLine table:

To make this a transaction block of its own, put the END statement for the DO TRANSACTION block after the Order is updated, as you did earlier:

.           .           .         ELSE DO: /* FIND the updated tt rec and save the changes. */             FIND bUpdateOrder WHERE bUpdateOrder.TransType = "U".             BUFFER-COPY bUpdateOrder TO Order. /* Save our changes. */         END.    /* END ELSE DO */     END.        /* END OF AVAILABLE ttOrder */   END.          /* END DO Transaction */

This makes the FOR EACH block that follows a separate transaction.
Add a new variable definition in saveOrder for an error message to return:

DEFINE VARIABLE cMessage AS CHARACTER NO-UNDO.

Add the highlighted lines, shown below, to the FOR EACH block that updates OrderLines:

FOR EACH ttOline WHERE ttOline.TransType = "":           .           .           . /* Find corresponding bUpdateOline */         FIND OrderLine WHERE OrderLine.OrderNum = ttOline.OrderNum AND              OrderLine.LineNum = ttOline.LineNum EXCLUSIVE-LOCK.           .           .           .         BUFFER-COPY bUpdateOline TO OrderLine. /* Save our OrderLine changes. */           RELEASE OrderLine.      /* Re-find the db record to capture any changes made by a trigger. */     FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND           OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.         BUFFER-COPY OrderLine TO bUpdateOline.       IF bUpdateOline.ExtendedPrice > (ttOline.ExtendedPrice * 1.2) THEN         DO:             cMessage = "Line " + STRING(OrderLine.LineNum) +                        ": Can't increase price by more than 20%.".           UNDO, LEAVE.         END.           .           .           .   END.           /* END DO FOR EACH ttOline */

This code checks to make sure that the ExtendedPrice for an OrderLine is not increased by more than 20%. If this limit is exceeded, then the current iteration of the block is undone and the block is left.
On each iteration, the FOR EACH block makes a ttOline record current. Your code uses the second buffer, bUpdateOline, to locate the updated version of that OrderLine temp-table record. It then finds the OrderLine in the database and copies the updates to it. Next it releases the database record to force its WRITE trigger to fire, which recalculates the ExtendedPrice field. Then it again finds the database record and copies it back into the bUpdateOline buffer to capture the effects of the trigger code, in particular the new ExtendedPrice value. Only now can your program logic compare this to the original value in the ttOline buffer to see if it exceeds the limit. If it does, then you store off a message, then undo and leave the FOR EACH block.
At this point, following the UNDO statement, the whole database change that you wrote out and then read back in is gone—Progress has restored the database record to its values before the transaction began.
Add code so that after leaving the block, you check if you have an error message. If so, your code needs to re-find the OrderLine record with its original values, and copy it back into the bUpdateOline buffer to return to the client for display. It then returns the message as the return value for the procedure:

IF cMessage NE "" THEN    DO:     FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND               OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.         BUFFER-COPY OrderLine TO bUpdateOline.         RETURN cMessage.    END.

Why did you have to re-find the OrderLine record from the database? The UNDO released it from its buffer, so it’s no longer available after the block ends. Then why did you not have to re-find the temp-table record? You defined the temp-table as NO-UNDO so it was left out of normal transaction handling. The temp-table buffer is scoped to the whole procedure, so the record it contains is still available after the block ends. If you had defined the temp-table without NO-UNDO, then the bUpdateOline record would have been released, as well as the database OrderLine record, and you would have had to re-establish it in the temp-table as well. This is an illustration of the kind of overhead you save by using NO-UNDO temp-tables, and also of the sometimes unintended consequences of having undo capability on a temp-table that doesn’t require it.

The simple diagram in Figure 18–2 illustrates the scope of the transactions.

Figure 18–2: Example transaction scope

In Figure 18–2, the first transaction, which saves changes to the Order, completes at the END statement for the DO TRANSACTION block. At the end of the FOR EACH block, the transaction to save the current OrderLine ends, committing those changes to the database, releasing the OrderLine record, and then going back to the beginning of the block. Each OrderLine is saved in a separate transaction.

The UNDO statement rolls back the transaction for the current OrderLine and leaves the FOR EACH block, which skips any remaining ttOline records. But any previously committed OrderLine changes remain in the database. For example, in Figure 18–3, the user changes the Price for Line 1 from 7.49 to 7.60, for Line 2 from 23.00 to 30.00, and for Line 3 from 13.98 to 13.50.

Figure 18–3: Order Updates example

The first and third changes are valid, but the second one is not. It increases the ExtendedPrice by too much. So when the user chooses Save, the user sees an error message for Line 2, as shown in Figure 18–4.

Figure 18–4: Order Updates example message

But if the user presses the Fetch button to refresh all the OrderLines:

The change to Line 1 is committed, because it is in its own transaction.
The change to Line 2 is undone, because it failed the constraint.
The change to Line 3 is never even applied because the code left the block after the error on Line 2.

Figure 18–5 shows the result.

Figure 18–5: Order Updates example (after Fetch)

This might not be the behavior you want.

On the one hand, you might want all OrderLines to be committed together or all undone if any one fails. In another case, you might want to process each OrderLine as a separate transaction, but keep going if one of them fails. Look at both of those cases.

In the first case, you want all the OrderLine updates to either succeed or fail together. If any one fails its validation, all are rolled back.

To see an example of the first case:

Define the transaction scope to be greater than a single iteration of the FOR EACH block by putting a new DO TRANSACTION block around it. Then add a label for that new block to identify how much to undo in your UNDO statement:

OlineBlock:   DO TRANSACTION:     FOR EACH ttOline WHERE ttOline.TransType = "":

Change the UNDO statement to undo the entire larger transaction and to leave that outer block as well:

UNDO OlineBlock, LEAVE OlineBlock.

Remember that the default is to undo and leave the innermost block with the error property, the FOR EACH block.
Add another END statement to match the new DO TRANSACTION statement that begins the new block:

END.   /* END ELSE DO If we updated the OrderLine */     END.       /* END DO FOR EACH ttOline */   END.         /* END new DO TRANSACTION block */

Note that a block label, such as OlineBlock:, does not require a matching END statement. It is simply an identifier for a place in the code and does not actually start a block of its own.
Make a change to the code that restores the original values to the temp-table if there’s an error. Because the error might not be on the line that’s current when you leave the block, you need to re-read all the OrderLines for the Order and buffer-copy their values back into the update copies of each of the temp-table records, in a FOR EACH loop:

IF cMessage NE "" THEN   DO:     FOR EACH OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum NO-LOCK:         FIND bUpdateOline WHERE OrderLine.LineNum = bUpdateOline.LineNum             AND bUpdateOline.TransType = "U".         BUFFER-COPY OrderLine TO bUpdateOline.     END.     RETURN cMessage.   END.

Now if you make changes to three OrderLines, and the second of the three is invalid, then all three changes are rolled back because they’re all part of one large transaction. You see this reflected in your window.

Figure 18–6 shows a sketch of what this variation looks like.

Figure 18–6: Variation of transaction scope

Now look at the second case. You’d like each OrderLine committed independently of the others, and you’d like to keep going if one is invalid. In this case, you can use the NEXT option on the UNDO statement instead of LEAVE. If an error is found, the current transaction is undone and your code continues on to the next ttOline record.

To try this variation:

Remove the OlineBlock block label, along with the DO TRANSACTION block header and its matching END statement, from around the FOR EACH block.
Change the UNDO, LEAVE statement to UNDO, NEXT.
Since it is now possible to get errors on more than one OrderLine at a time, you should be prepared to accumulate multiple error messages in your message string.
Append each new message to the end of the string by using the plus sign (+) as a concatenation symbol for the character variable (cMessage = cMessage + . . .).
Put a newline character at the end of each message, using the CHR function to append the ASCII character whose numeric value is 10 to the string:

IF bUpdateOline.ExtendedPrice > (ttOline.ExtendedPrice * 1.2) THEN       DO:          cMessage = cMessage +                     "Line " + STRING(OrderLine.LineNum) +                ": Can't increase price by more than 20%." + CHR(10).           UNDO, NEXT.       END.

Run the window.
Enter a valid price for Line 1 and invalid prices for Lines 2 and 3. You see error messages for both of these:



You can also see that the valid change for Line 1 is kept because you’re back to making each iteration of the FOR EACH block its own transaction.

Figure 18–7 shows a sketch of this variation.

Figure 18–7: Another variation of transaction scope

Subtransactions

You separated the Order update and the OrderLine updates out into two separate transactions by moving the end of the DO TRANSACTION block up after the Order block.

To look at what happens if you combine them all again:

Define a new label for the DO TRANSACTION block:

TransBlock:   DO TRANSACTION ON ERROR UNDO, LEAVE:     FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.

Move this block’s END statement back all the way down to the end of the FOR EACH block, then change the UNDO, LEAVE statement to undo and leave that entire block:

IF bUpdateOline.ExtendedPrice > (ttOline.ExtendedPrice * 1.2) THEN      DO:         cMessage = cMessage +                    "Line " + STRING(OrderLine.LineNum) +              ": Can't increase price by more than 20%." + CHR(10).          UNDO TransBlock, LEAVE TransBlock.      END.    END.   /* END ELSE DO If we updated the OrderLine */   END.       /* END DO FOR EACH ttOline */  END.         /* END DO Transaction */

Now the transaction structure looks like the diagram in Figure 18–8.

Figure 18–8: Third variation of transaction scope

If there’s an error on any OrderLine, then the whole transaction is backed out, including any change to the Order.

To test this:

Edit one of the fields in the Order, such as the PO, and then make an invalid change to one of its OrderLines.
Choose Save. You see an error message.
Choose Fetch to refresh the Order.

The changes you made to the Order have been undone along with changes to the OrderLines. (Note that the code isn’t set up to refresh the Order display if the transaction fails. This is an exercise you can do yourself.)

But what if you want to undo a portion of a transaction? Progress supports the capability to do this. If your application has multiple nested blocks, each of which would be a transaction block if it stood on its own, then the outermost block is the transaction and all nested transaction blocks within it become subtransactions. A subtransaction block can be:

A procedure block that is run from a transaction block in another procedure.
Each iteration of a FOR EACH block nested within a transaction block.
Each iteration of a REPEAT block nested within a transaction block.
Each iteration of a DO TRANSACTION, DO ON ERROR, or DO ON ENDKEY block inside a transaction block.

If an error occurs during a subtransaction, all the work done since the beginning of the subtransaction is undone. You can nest subtransactions within other subtransactions. You can use the UNDO statement to programmatically undo a subtransaction.

In the sample logic procedure, for example, with the END statement moved to the end, the FOR EACH block is really a subtransaction within the main transaction. An error inside this inner block undoes only the change made in that block. Likewise, if you change the UNDO statement back to UNDO, NEXT, then the Order changes are saved and only the current OrderLine changes are undone, as shown in Figure 18–9.

Figure 18–9: Example subtransaction

Note that a FOR EACH, REPEAT, or procedure block that does not directly contain statements that either modify the database or read records using an EXCLUSIVE-LOCK does not start a transaction on its own. However, if contained inside a transaction block, it does start a subtransaction.

Transaction mechanics

It’s important to have at least a basic understanding of how Progress handles transactions and subtransactions, although a detailed discussion of this topic is beyond the scope of this book. During a transaction, information on all database activity occurring during that transaction is written to a before-image (or BI) file that is associated with the database and located on the server with the other database files. The information written to the before-image file is coordinated with the timing of the data written to the actual database files. That way, if an error occurs during the transaction, Progress uses the before-image file to restore the database to the condition it was in before the transaction started. Information written to the before-image file is not buffered. It is written to disk immediately, so that there is minimal loss of information in the event of a system crash.

Space in the before-image file is allocated in units called clusters. Progress automatically allocates new clusters as needed. After all changes associated with a cluster are committed and written to the database itself, Progress can reuse the cluster. Therefore, the disk space used by the before-image file depends on several factors including the cluster size, the scope of your transactions, and when physical writes are made to the database files. An action such as creating a huge number of database records in a batch procedure within a single transaction creates an enormous before-image file. You should avoid such actions.

When Progress encounters a transaction block nested within another transaction block, it starts a subtransaction. All database activity occurring during that subtransaction is written to a local-before-image (or LBI) file. Unlike the database BI file, Progress maintains one LBI file for each user. If an error occurs during the subtransaction, Progress uses this local-before-image file to restore the database to the condition it was in before the subtransaction started. In any case where a full transaction is not being backed out, Progress uses the local-before-image file to back out, not only subtransactions, but also changes to variables not defined as NO-UNDO.

Because the LBI file is not needed for crash recovery, it does not have to be written to disk in the same carefully synchronized fashion as does the before-image information. This minimizes the overhead associated with subtransactions. The LBI file is written using normal buffered I/O. The amount of disk space required for each user’s LBI file depends on the number and size of subtransactions started that are subject to being done. It is advisable that you minimize the use of subtransactions, as well as the scope of your overall transactions, not just to simplify the handling of these files but also to minimize record contention with other users.

Using the ON phrase on a block header

In addition to placing one or more UNDO statements within a transaction, you can also specify the default undo processing as part of the block header of a FOR, REPEAT, or DO block. The default action is to retry the block that was undone or, if there is no user interaction that could change the data, to leave the block.

The UNDO phrase as a part of a block header has the same syntax as the UNDO statement itself. You can specify the action to be LEAVE, NEXT, RETRY (with or without a label), or RETURN ERROR or NO-APPLY.

There are four special conditions Progress recognizes:

The ERROR condition
The ENDKEY condition
The STOP condition
The QUIT condition

Handling the ERROR condition

Your application can encounter an error condition whenever Progress cannot execute a statement properly, such as trying to find a record that does not exist or create a record with a duplicate value in a unique index. Progress has a built-in error message associated with each such error condition and, by default, displays it (or writes it to an error log on an AppServer).

For example, if your procedure executes a FIND statement for a nonexistent Customer, you get the error shown in Figure 18–10.

Figure 18–10: FIND error message

The error number in parentheses lets you locate the message number under the Help menu to get more information on the error. It can also help you when you are reporting unexpected errors to technical support.

You can also generate the ERROR condition programmatically using the RETURN ERROR statement, either as part of the ON phrase of a block header or as a statement of its own. If your application has associated a keyboard key with the ERROR condition then Progress also raises ERROR when the user presses that key.

ERROR-STATUS system handle

In most cases, you do not want raw error messages to be shown to users, even when it is their mistake that causes the error. The alert box in Figure 18–10, for example, is not a very friendly or informative way to present an error to a user. Even more important, it is essential that your procedures anticipate all possible error conditions whether they are caused by a user action or not, and respond to them, in some cases by suppressing an error message altogether. In addition, your application must define a mechanism for returning errors generated in an AppServer session back to the client, because by default Progress messages simply go to the server log file and are never seen.

To check for errors programmatically, you use the ERROR-STATUS system handle. Many Progress statements support the NO-ERROR option as the last keyword in the statement. If you specify this option, that statement does not generate the ERROR condition. Instead, if the statement cannot execute properly, execution continues with the next statement. You can then check whether an error occurred by examining the attributes of the ERROR-STATUS system handle.

The ERROR-STATUS handle contains information on the last statement executed with the NO-ERROR option. The logical attribute ERROR-STATUS:ERROR tells you whether an error occurred. Because in some cases a single error can return multiple messages, the NUM-MESSAGES attribute tells you how many messages there are. The GET-MESSAGE(<msg-num>) method returns the text of the message, and the GET-NUMBER(msg-num) method returns the internal message number. Here’s a simple example:

DEFINE VARIABLE iMsg AS INTEGER    NO-UNDO. FIND Customer WHERE CustNum = 9876 NO-ERROR. IF ERROR-STATUS:ERROR THEN DO iMsg = 1 TO ERROR-STATUS:NUM-MESSAGES:     MESSAGE "Error number: " ERROR-STATUS:GET-NUMBER(iMsg) SKIP              ERROR-STATUS:GET-MESSAGE(iMsg)         VIEW-AS ALERT-BOX ERROR. END.

Because there is no Customer 9876, you get an error and your code displays the message box shown in Figure 18–11.

Figure 18–11: Example error message

Because you are intercepting the error, you can handle it more gracefully than this and also have your program logic proceed accordingly. You can check the message number using the GET-NUMBER method and put code in your application to deal with each of the possible error conditions.

Remember also that Progress provides special built-in functions, such as AVAILABLE and LOCKED, to make it easier for you to tell when certain common errors have occurred:

FIND Customer WHERE CustNum = 9876 NO-ERROR. IF NOT AVAILABLE Customer THEN   MESSAGE "So sorry, but this Customer does not seem to be there."     VIEW-AS ALERT-BOX INFORMATION.

Figure 18–12 shows the result.

Figure 18–12: Example information message

Note that the ERROR-STATUS handle holds only error conditions and messages for the most recently executed statement with the NO-ERROR option. It does not accumulate errors over multiple statements. The ERROR-STATUS remains in effect (and checkable by your code) until the next statement with the NO-ERROR option.

When the ERROR condition occurs anywhere outside of a trigger block, Progress looks for the closest block with the error property and undoes and retries that block. As discussed earlier, if it is not meaningful to retry the block. Progress proceeds to the next iteration if it is a repeating block or else leaves the block.

Because this is the default, the following transaction header from the sample saveOrder procedure could simply be DO TRANSACTION: and have the same effect:

DO TRANSACTION ON ERROR UNDO, LEAVE:

Error handling

If an error occurs in a database trigger block, Progress undoes the trigger block and returns ERROR.

If you use the NO-ERROR option on statements within a block, you are suppressing not only the visible error message but also the ERROR condition itself. Therefore, if you do this, it becomes your responsibility to check for errors and respond to them correctly. This might include issuing an UNDO statement of your own. The ON phrase in the header simply changes the default action for untrapped conditions.

RETURN statement and ON . . . RETURN phrase

In any RETURN statement, whether it returns an ERROR or not, you can return a text string to the calling procedure. This string is accessible in the caller through the RETURN-VALUE built-in function. Thus, a procedure can use a single RETURN statement to raise the ERROR condition in the caller, return a message, or both:

RETURN [ ERROR ] [ return-value-string ] .

Likewise, the caller can check for the ERROR-STATUS:ERROR condition, or a RETURN-VALUE, or both, depending on the agreement between caller and callee as to how they communicate with one another. The RETURN-VALUE function retains its value even through multiple RETURN statements. Thus, while it is not required, it is advisable always to have a RETURN statement at the end of every procedure, if only to clear the RETURN-VALUE. A simple RETURN statement is the same as RETURN “”. If you want to pass the RETURN-VALUE up the call stack, you should do this explicitly using the statement RETURN RETURN-VALUE.

The same is true of the RETURN option as part of the ON phrase in a block header. It can return a return-value, raise ERROR, or both.

ENDKEY condition

The ENDKEY condition occurs when the user presses a keyboard key that is mapped to the ENDKEY keycode when input is enabled. It also occurs if an attempt to find a record fails or you reach the end of an input stream. Because your applications should not normally mix data input with transaction blocks in the same procedures, you do not frequently have cause to use the ON ENDKEY phrase on a transaction block.

STOP condition

Progress supports a STOP statement that lets the user terminate or restart your application altogether if an unrecoverable error occurs. You can trap the STOP condition in your block header statements as well. The STOP condition occurs when a Progress STOP statement executes or when the user presses the keyboard key mapped to that value. The STOP key, by default, is mapped to CTRL-BREAK on MS Windows and CTRL-C on UNIX.

When the STOP condition occurs, by default Progress undoes the current transaction (if any). If the user starts the application from an OpenEdge tool, such as the Procedure Editor, Progress terminates the application and returns to that tool. Otherwise, if the user starts the application using the Startup procedure (-p) startup option on the OpenEdge session, Progress reruns the startup procedure.

Progress raises the STOP condition when an unrecoverable system error occurs, such as when a database connection is lost or an external procedure that your code runs cannot be found. You cannot put the NO-ERROR condition on a RUN statement for an external procedure, as you can for an internal procedure. Therefore, the only way to trap such an error is to put the statement in a DO ON STOP block such as this:

DO ON STOP undo, LEAVE:      RUN foo.p. END. MESSAGE "The procedure to support your last action cannot be found."         SKIP "Please try another option." VIEW-AS ALERT-BOX.

If the procedure isn’t found you still get an error, as shown in Figure 18–13.

Figure 18–13: Procedure not found error message

But your procedure continues executing and you can deal with the error, as shown in Figure 18–14.

Figure 18–14: Example message for procedure not found condition

If you anticipate that this might happen, it is better to use the SEARCH function to determine in advance whether Progress can find the procedure in the current PROPATH:

IF SEARCH("foo.p") = ? THEN   MESSAGE "The procedure to support your last action cannot be found."      SKIP "Please try another option." VIEW-AS ALERT-BOX. ELSE RUN foo.p.

System and software failures

Following a system hardware or hardware failure that it cannot recover from, Progress undoes any partially completed transactions for all users. This includes any work done in any complete or incomplete subtransaction encompassed within the uncommitted transaction.

If Progress loses a database connection (for example, because a remote server fails), client processing can still continue. In this case, the following actions occur:

Progress raises the STOP condition. For this special instance of the STOP condition, you cannot change the default processing. Progress ignores any ON STOP phrases.
Progress deletes any running persistent procedure instances that reference the disconnected database.
Progress undoes executing blocks, beginning with the innermost active block and working outward. It continues to undo blocks until it reaches a level above all references to tables or sequences in the disconnected database.
Progress changes to the normal STOP condition. From this point on, Progress observes any further ON STOP phrases it encounters in your procedures.

Progress continues to undo blocks until it reaches an ON STOP phrase. If no ON STOP phrase is reached, it undoes all active blocks and restarts the top-level procedure.

QUIT condition

Progress also supports a QUIT statement to terminate the application altogether. Progress raises the QUIT condition only when it encounters a QUIT statement. The default handling of the QUIT condition differs from STOP in these ways:

Progress commits, rather than undoes, the current transaction.
Even if the user specifies the –p startup option to define the main procedure in your application, it returns to the operating system rather than trying to rerun the startup procedure. In other words, Progress quits the session unconditionally.

Summary

Having completed this chapter, you now have a thorough grounding in all the essentials of Progress programming with static statements. In the next three chapters, you’ll learn how to use dynamic constructs to define both the user interface and the data management code for parts of your application. Then, in the final chapter, you’ll learn some useful advanced 4GL constructs as well as some best practices guidelines for building efficient and maintainable procedures.