Coding ESQL to handle errors
When you process messages in a message flow, errors can have a number of different causes and the message flow designer must decide how to handle those errors.
- External causes; for example, the incoming message is syntactically invalid, a database used by the flow has been shut down, or the power supply to the machine on which the integration node is running fails.
- Internal causes; for example, an attempt to insert a row into
a database table fails because of a constraint check, or a character
string that is read from a database cannot be converted to a number
because it contains alphabetic characters.
Internal errors can be caused by programs storing invalid data in the database, or by a flaw in the logic of a flow.
Using default error-handling
The simplest strategy for handling ESQL errors is to do nothing, and use the integration node's default behavior. The default behavior is to cut short the processing of the failing message, and to proceed to the next message. Input and output nodes provide options to control exactly what happens when processing is cut short.
- The input message that has apparently been taken from the input queue is put back.
- Any output messages that the flow has apparently written to output queues are discarded.
- The input message that was taken from the input queue is not put back.
- Any output messages that the flow has written to output queues remain on the output queues.
Each of these strategies has its advantages. The transactional model preserves the consistency of data, while the non-transactional model maximizes the continuity of message processing. In the transactional model, the failing input message is put back onto the input queue, and the integration node attempts to process it again. The most likely outcome of this scenario is that the message continues to fail until the retry limit is reached, at which point the message is placed on a dead letter queue. The reason for the failure to process the message is logged to the system event log (Windows) or syslog (UNIX). Therefore, the failing message holds up the processing of subsequent valid messages, and is left unprocessed by the integration node.
Most databases operate transactionally so that all changes that are made to database tables are committed if the processing of the message succeeds, or rolled back if it fails, therefore maintaining the integrity of data. An exception to this situation is if the integration node itself, or a database, fails (for example, the power to the computers on which they are running is interrupted). In these cases, changes might be committed in some databases, but not in others, or the database changes might be committed but the input and output messages are not committed. If these possibilities concern you, make the flow coordinated and configure the databases that are involved.
Using customized error handling
- If you require something better than default error handling, the first step is to use a handler; see DECLARE HANDLER statement. Create one handler per node to intercept all possible exceptions (or all those that you can predict).
- Having intercepted an error, the error handler can use whatever logic is appropriate to handle it. Alternatively, it can use a THROW statement or node to create an exception, which could be handled higher in the flow logic, or even reach the input node, causing the transaction to be rolled back; see Throwing an exception.
- If a node generates an exception that is not caught by the handler,
the flow is diverted to the Failure terminal, if one is connected,
or handled by default error-handling if no Failure terminal is connected.
Use Failure terminals to catch unhandled errors. Attach a simple logic flow to the Failure terminal. This logic flow could consist of a database or Compute node that writes a log record to a database (possibly including the message's bit stream), or writes a record to the event log. The flow could also contain an output node that writes the message to a special queue.
The full exception tree is passed to any node that is connected to a Failure terminal; see Exception list tree structure.
- Your error handlers are responsible for logging each error in an appropriate place, such as the system event log.
For a detailed description of the options that you can use to process errors in a message flow, see Handling errors in message flows. For examples of what you can do, see Throwing an exception and Capturing database state.
Writing code to detect errors
- IF statements that are inserted specifically to detect situations that should not occur
- The ELSE clause of a case expression or statement to trap routes through the code that should not be possible
Using your own logic to handle input messages that are not valid
Input messages that are syntactically invalid (and input messages that appear to be not valid because of erroneous message format information) are difficult to deal with, because the integration node cannot determine the contents of the message. Typically, the best way to deal with these messages is to configure the input node to fully parse and validate the message. However, this configuration applies only to predefined messages; that is, MRM or IDoc.
- The input message never emerges from the node's normal output terminal (it goes to the Failure terminal).
- The input message never enters the main part of the message flow.
- The input message never causes any database updates.
- No messages are written to any output queues.
To deal with a failing message, connect a simple logic flow to the Failure terminal. The only disadvantage to this strategy is that if the normal flow does not require access to all of the message's fields, the forcing of complete parsing of the message affects performance.
Using your own logic to handle database errors
- The database is not working (for example, it is off line).
- The database is working but refuses your request (for example, a lock contention occurs).
- The database is working but it cannot do what you request (for example, read from a non-existent table).
If you require something better than default error handling, the first step is to use a handler (see DECLARE HANDLER statement) to intercept the exception. The handler can determine the nature of the failure from the SQL state that is returned by the database.
- A database is not working
- If a database is not working at all, and is essential to the processing
of messages, there is typically not much that you can do. The handler,
having determined the cause, might complete one or more of the following
- Use the RESIGNAL statement to re-throw the original error, therefore allowing the default error handler to take over
- Use a different database
- Write the message to a special output queue
Be careful if you use an approach similar to this technique; the handler absorbs the exception, therefore all changes to other databases, or writes to queues, are committed.
- A database refuses your request
- The situation when a lock contention occurs is similar to the
Database not workingcase because the database will have backed out all the database changes that you have made for the current message, not just the failing request. Therefore, unless you are sure that this was the only update, default error-handling is typically the best strategy, except possibly logging the error or passing the message to a special queue.
- Impossible requests
- An impossible request occurs when the database is working, but cannot complete the requested action. This type of error covers a wide variety of problems.
- If, as discussed in the previous example, the database does not
have a table of the name that the flow expects, default error-handling
is typically the best strategy, except possibly logging the error
or passing the message to a special queue.
Many other errors might be handled successfully, however. For example, an attempt to insert a row might fail because there is already such a row and the new row would be a duplicate. Or an attempt to update a row might fail because there is no such row (that is, the update action updated zero rows). In these cases, the handler can incorporate whatever logic you think appropriate. It might insert the missing row, or use the existing one (possibly making sure the values in it are suitable).
If you want an update of zero rows to be reported as an error, you must set the Treat warnings as errors property on the node to true, which is not the default setting.
Using your own logic to handle errors in output nodes
Errors that occur in MQOutput nodes report the nature of the error in the SQL state and give additional information in the SQL native error variable. Therefore, if something better than default error handling is required, the first step is to use a handler (see DECLARE HANDLER statement) to intercept the exception. Such a handler typically surrounds only a single PROPAGATE statement.
Using your own logic to handle other errors
- The error causes an exception that is either handled or left to roll back the transaction.
- The failure is recorded as a special value that is tested for later.
In the absence of a type constraint, an attempt to access a non-existent message field results in the value null. Null values propagate through expressions, making the result null. Therefore, if an expression, however complex, does not return a null value, you know that all the values that it needed to calculate its result were not null.
Cast expressions can have a default clause. If there is a default clause, casts fail quietly; instead of throwing an exception, they simply return the default value. The default value could be an innocuous number (for example, zero for an integer), or a value that is clearly invalid in the context (for example, -1 for a customer number). Null might be particularly suitable because it is a value that is different from all others, and it will propagate through expressions without any possibility of the error condition being masked.
Handling errors in other nodes
Exceptions that occur in other nodes (that is, downstream of a PROPAGATE statement) might be caught by handlers in the normal way. Handling such errors intelligently, however, poses a problem: another node was involved in the original error, therefore another node, and not necessarily the originator of the exception, is likely to be involved in handling the error.
To help in these situations, the Database and Compute nodes have four terminals called Out1, Out2, Out3, and Out4. In addition, the syntax of the PROPAGATE statement includes target expression, message source, and control clauses to give more control over these terminals.