Attempting to add a duplicate record is a common data-entry error. For example, the user might try to create a record, such as a customer record, using a unique key that already exists in the database. If a customer record already exists where the custnum equals 1, and the user tries to add another customer with the same custnum, ABL generates an error. When this type of error occurs, ABL tries to resolve the error by working its way back through the procedure, looking at each block header until it finds the closest block that has the error property. It then undoes and retries the block. See OpenEdge Getting Started: ABL Essentials for more information about error handling.
Because the DataServer is accessing a non-OpenEdge database, it cannot detect duplicate key errors until the end of a transaction block. As a result, if the error occurs in a subtransaction, ABL cannot detect the error until the end of the entire transaction block, so it performs default error handling for the transaction block.
This code example illustrates ABL and DataServer error handling:
/* DataServer returns control here when a duplicate state key is
encountered. */
rep-blk:
REPEAT:
/* DataServer prompts here on duplicate state key. */
PROMPT-FOR customer.custnum.
FIND customer USING customer.custnum NO-ERROR.
IF AVAILABLE customer THEN
UPDATE customer.custnum customer.name.
/* OpenEdge DB returns control here when a duplicate state key is
encountered.*/
do-blk:
DO ON ERROR UNDO do-blk, RETRY do-blk:
FIND state WHERE state.state = customer.state.
DISPLAY state.
/* OpenEdge DB prompts here on duplicate state key. */
SET state.
END.
END.
This procedure displays the screen shown below. The procedure prompts the user to enter data into the custnum field, and then into the state field. Suppose a user enters an existing state (for example, NH) while ABL is processing the DO block. With an OpenEdge database if a duplicate key entry occurs in the DO block, control returns to the DO block. After ABL displays a message that the record exists, it reprompts the user for the state abbreviation. For example:
Figure 8. OpenEdge database reprompts for the state abbreviation.
With the OpenEdge DataServer for Oracle, if a duplicate key entry occurs in the DO block, ABL returns control to the REPEAT block. The procedure reprompts the user to enter the customer number after the inner transaction completes. For example:
Figure 9. DataServer for Oracle reprompts for the customer
When two users simultaneously attempt to create records with duplicate keys, another difference in behavior occurs. ABL raises an error immediately, but Oracle raises an error after the first transaction commits, and only if the second transaction does not roll back. It is important to note that the second attempt to create a duplicate key will wait until the first user sends the transaction. Oracle does not notify the DataServer that it is waiting for the other user's transaction to end, so the DataServer cannot produce a message on the client indicating the lock wait situation.
To avoid this difference, 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.
