One common data-entry error is attempting to add a duplicate record; that is, trying to create a record using a unique key that already exists in the database. Suppose, for example, that a user tries to add a customer with custnum = 1 (where custnum is a unique key), but a customer record with this custnum value already exists. The attempt fails and ABL generates an error.
When this type of error occurs, ABL tries to resolve it by working back through the procedure, looking at each block header until it finds the closest block with the error-handling property, and then undoing and retrying the block. (See OpenEdge Getting Started: ABL Essentials for more information about error handling.) However, because the DataServer is accessing a non-OpenEdge data source, OpenEdge cannot detect duplicate-key errors until the end of a transaction block. Therefore, if an error occurs in a subtransaction, ABL cannot detect it until the end of the entire transaction block and must perform default error handling for the entire transaction block.
The following example illustrates ABL and DataServer error handling:
rep-blk:
REPEAT:
PROMPT-FOR customer.custnum. /* User input */
FIND customer USING customer.custnum NO-ERROR.
IF AVAILABLE customer THEN
UPDATE customer.custnum customer.name customer.state. /* User input */
do-blk:
DO ON ERROR UNDO do-blk, RETRY do-blk:
FIND state WHERE st.state = customer.state.
DISPLAY state.
SET state. /* User input */
END.
END.
This procedure displays the following screen, in which the user is prompted to enter data into the custnum field and then the state field:
Suppose that the user enters an existing state (for example, NH) while Progress is processing the DO block. When this duplicate-key entry occurs for an OpenEdge database, Progress returns control to the DO block, displays a message that the record exists, and reprompts the user for a state abbreviation.
However, with the DataServer, if a duplicate key entry occurs in the DO block, Progress returns control to the REPEAT block rather than the DO block. As a result, the procedure reprompts the user for a customer number after the inner transaction completes:
If you use NO–ERROR to do your own error handling, you must account for the fact that an ODBC data source creates or updates a record later than Progress does. For example, the following code does not trap data-source errors, because the requests to perform the operations have not yet been sent to the data source:
The VALIDATE statement causes the DataServer to send requests to your ODBC data source, so incorporate it into your error-handling technique, as in the following example:
DEFINE VARIABLE ix AS INTEGER NO-UNDO.
DO TRANSACTION:
CREATE customer NO-ERROR.
ASSIGN customer.custnum = 45 NO-ERROR.
VALIDATE customer.
IF ERROR-STATUS:ERROR THEN DO:
MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES.
DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
MESSAGE "error" ERROR-STATUS:GET-NUMBER(ix)
ERROR-STATUS:GET-MESSAGE (ix).
END.
UNDO, LEAVE.
END.
ASSIGN customer.name = "Smith" NO-ERROR.
VALIDATE customer.
IF ERROR-STATUS:ERROR THEN . . .
END.
This code returns data-source errors after the VALIDATE statement.
Another difference in behavior occurs when two users simultaneously attempt to create records with duplicate keys. Progress raises an error immediately, but the data source raises an error after the first transaction commits and only if the second transaction does not roll back. To avoid this difference, either change the scope of the transaction so that it completes more quickly or make the key nonunique and enforce uniqueness at the application level. Another technique is to use a RELEASE or VALIDATE statement when you check for the key's uniqueness.
