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.
Internal errors can be caused by programs storing invalid data in the database, or by a flaw in the logic of a flow.
The simplest strategy for handling ESQL errors is to do nothing, and use the broker'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.
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 broker 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 broker.
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 broker 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.
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.
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.
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 broker 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.
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.
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.
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.
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.
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.
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.
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.