function execDropTable(status StatusRec inOut)
try
execute #sql{
DROP TABLE EGL.CUSTOMERTEMP
} ;
onException (exception SQLException)
ConditionHandlingLib.HandleException(status, exception);
end
end
When you put code in a try block,
EGL attempts to run that code as usual. If EGL encounters an error, it stops
running the code in the try block and moves directly
to the onException block, skipping any statements
that remain in the try block. You can put code in
the onException block to recover from the error,
correct the problem, or write error information to the log file.When EGL encounters an error within a try block, it also creates an exception record to provide information about the error. It passes that record to the onException block. Within the onException block, you can use the information in that record to help determine the cause of the error and recover from it. In the above example, the onException block passes the exception record to a function that deals with the error.
Exception records can have one of several stereotypes, depending on the type of error. In the example above, the onException block expects an exception record with the SQLException stereotype, which is for SQL errors and other relational database errors. If EGL encounters a different type of error, such as reference to a null value, it creates a different type of exception record (in the case of a reference to a null value, an exception record with the NullValueException stereotype).
function execDropTable(status StatusRec inOut)
try
execute #sql{
DROP TABLE EGL.CUSTOMERTEMP
} ;
onException (exception SQLException)
ConditionHandlingLib.HandleException(status, exception);
onException (exception AnyException)
HandleOtherError(exception);
end
end
In this example, the first onException block
expects a SQLException record and the second block expects the AnyException
stereotype. In this case, if EGL encounters an SQL error, the first onException block
runs; if EGL encounters any other kind of error, the second block runs. The
AnyException stereotype is a general-user exception record stereotype, used
to respond to any error, regardless of stereotype.All exception records have at least two fields: a messageID field with the error code for the error and a message field with a brief explanation of the problem. Depending on the stereotype, exception records can have other fields. For example, the SQLRecord stereotype has a sqlState field that holds a CHAR(5) value with the status of the statement, and a sqlCode field that holds an INT return code from the DBMS.
You can try out exception handling by passing invalid information, deleting or updating a record that doesn't exist, or trying to add a record with the same primary key. The following example attempts to add a new customer record with the same ID number as an existing row in the database, which will cause a conflict with primary keys and prompt EGL to throw an SQLException exception.
For example, imagine that you are making several changes to the database, such as adding a new customer and entering order information for that customer's first order. If some of these operations succeed before another fails, you may be left with a customer without any orders, or an order without a customer who made the order. In this case, you can put a rollback in the onException block to reverse the changes and keep incomplete information out of the database:
Order added successfully. SQL exception EGL0504E message = EGL0504E ADD: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL070307095259210' defined on 'CUSTOMER'.[sqlstate:23505][sqlcode:20000] EGL0002I The error occurred in the exceptionHandlingTest program processing the main function. Performing rollback. The order was rolled back successfully.Here is the complete code of the exceptionHandlingTest.egl file. If you see any errors marked by red X symbols in the file, make sure your code matches the code in this file:Completed exceptionHandlingTest.egl file after lesson 7.