Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## Error handling

> 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 `cust-num` equals 1, and the user tries to add another `customer` with the same `cust-num`, the Progress 4GL generates an error. When this type of error occurs, the Progress 4GL 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 Development: Progress 4GL Handbook 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, the Progress 4GL 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 the Progress 4GL and DataServer error handling:

>     rep-blk:                                     /* DataServer returns control */
>     REPEAT:                                            /* here when a duplicate state */
>                                                 /* key is encountered.         */
>       PROMPT-FOR customer.cust-num.               /* DataServer prompts here on  */
>                                                   /* duplicate state key.        */
>       FIND customer USING cust-num NO-ERROR.
>       IF AVAILABLE customer THEN
>         UPDATE customer.cust-num name.
>       do-blk:                                     /* OpenEdge DB returns control */
>       DO ON ERROR UNDO do-blk, RETRY do-blk:      /* here when a duplicate state */
>         FIND state WHERE st.st = customer.st.     /* key is encountered.         */
>         DISPLAY state.
>         SET state.                                /* OpenEdge DB prompts here on */
>                                                   /* duplicate state key.        */
>       END.
>     END.

> This procedure displays the screen shown below. The procedure prompts the user to enter data into the `cust-num` field, and then into the `st-abbr` field. Suppose a user enters an existing `state` (for example, NH) while the 4GL 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 the 4GL displays a message that the record exists, it reprompts the user for the state abbreviation. For example:

> With the OpenEdge DataServer for ORACLE, if a duplicate key entry occurs in the `DO` block, the 4GL returns control to the `REPEAT` block. The procedure reprompts the user to enter the customer number after the inner transaction completes. For example:

> When two users simultaneously attempt to create records with duplicate keys, another difference in behavior occurs. The Progress 4GL 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.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095