Progress
DataServer
for ORACLE Guide


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, Progress generates an error. When this type of error occurs, Progress 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 the Progress Programming Handbook for more information about error handling.

Because the DataServer is accessing a non-Progress database, it cannot detect duplicate key errors until the end of a transaction block. As a result, if the error occurs in a subtransaction, Progress 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 Progress and DataServer error handling:

rep-blk:
REPEAT:
  PROMPT-FOR customer.cust-num.
  FIND customer USING cust-num NO-ERROR.
  IF AVAILABLE customer THEN 
    UPDATE customer.cust-num name.
  do-blk:
  DO ON ERROR UNDO do-blk, RETRY do-blk:
    FIND state WHERE st.st = customer.st.
    DISPLAY state.
    SET state.
  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 Progress is processing the DO block. If a duplicate key entry occurs in the DO block, Progress returns control to the DO block. After Progress displays a message that the record exists, it prompts the user for the state abbreviation.

With the DataServer for ORACLE, if a duplicate key entry occurs in the DO block, Progress returns control to the REPEAT block. The procedure prompts the user to enter the customer number after the inner transaction completes.

When two users simultaneously attempt to create records with duplicate keys, another difference in behavior occurs. Progress 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 © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095