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 an error is generated.

When this type of error occurs, ABL run time engine tries to resolve it by working back through the procedure, looking at each block header until the closest block with the error-handling property is found, and then undoing and retrying the block. (See Develop ABL Applications for more information about error handling.) However, because the DataServer is accessing a non-OpenEdge data source, duplicate-key errors can not be detected until the end of a transaction block. Therefore, if an error occurs in a subtransaction, it is not detected until the end of the entire transaction block and default error handling must be performed for the entire transaction block.

The following example illustrates OpenEdge database and DataServer error handling:

rep-blk:
REPEAT:
  PROMPT-FOR customer.custnum. /* User input */
  FIND customer USING custnum .
  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 EQ 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 ABL is processing the DO block. When this duplicate-key entry occurs for an OpenEdge database, ABL 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, ABL 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 a MS SQL Server data source creates or updates a record later than an OpenEdge database 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:

CREATE customer .
ASSIGN customer.custnum = 45 .
ASSIGN customer.name = "Smith" .

The VALIDATE statement causes the DataServer to send requests to your MS SQL Server data source, so incorporate it into your error-handling technique, as in the following example:

DEFINE VAR jx AS INTEGER.

DO TRANSACTION:
  CREATE customer .
  ASSIGN customer.custnum = 45 .
  VALIDATE customer.
  IF ERROR-STATUS:ERROR THEN DO:
    MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES.
    DO jx = 1 TO ERROR-STATUS:NUM-MESSAGES:
      MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
        ERROR-STATUS:GET-MESSAGE (jx).
    END.
    UNDO, LEAVE.
  END.

  ASSIGN customer.name = "Smith" .
  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 OpenEdge keys. An OpenEdge database 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.