Within a message flow, you can access and update an external database resource using the available ESQL database functions in the Compute, Database, and Filter nodes. When making calls to an external database, you might get errors, such as a table does not exist, a database is not available, or an insert for a key that already exists.
When these errors occur, the default action of the broker is to generate an exception. This behavior is determined by how you have set the property Throw exception on database error. If you select this property, the broker stops processing the node, propagates the message to the node's failure terminal, and writes the details of the error to the ExceptionList. If you want to override the default behavior and handle a database error in the ESQL in the node, clear Throw exception on database error. The broker does not throw an exception and you must include the THROW statement to throw an exception if a certain SQL state code is not expected. See THROW statement for a description of THROW.
To handle database errors in a node, you can use the database state function SQLSTATE to receive information about the status of the DBMS call made in ESQL. You can include it in conditional statements in current node's ESQL to recognize and handle possible errors.
SQL states
- Char 1
- The origin of the exception
- Chars 2 - 3
- The class of the exception
- Chars 4 - 6
- The subclass of the exception
The SQL state of an exception is determined by a two stage process. In the first stage, the exception information is examined and any wrapping exceptions (that is, information that says what the broker was doing at the time the exception occurred) is stepped over until the exception that describes the original error is located.
- If the selected exception is a database exception, the SQL state is that supplied by the database, but prefixed by the letter "D" to avoid any confusion with exceptions arising in the broker. The SQL code, native error, and error text are those supplied by the database.
- If the selected exception is a user exception (that is, it originated in a THROW statement), the SQL code, state, native error, and error text are taken from the first four inserts of the exception, in order. The resulting state value is taken as is (not prefixed by a letter such as "U"). The letter "U" is not used by the broker as an origin indicator. If you want to define a unique SQL state rather than to imitate an existing one, use SQL states starting with the letter "U". If you use SQL states that start with the letter "U", you can write an error handler to match all user-defined and thrown exceptions with a LIKE'U%' operator.
- If the selected exception originated in the message transport or in the ESQL implementation itself, the SQL code, state, native error, and error text are as described in the list later in this section.
- For all other exceptions, the SQL state is '', indicating no origin, no class, and no subclass.
Some exceptions that currently give an empty SQL state might give individual states in future releases. If you want to catch unclassified exceptions, use the "all" wildcard ("%") for the SQL state on the last handler of a scope. This wildcard will continue to catch the same set of exceptions if previously unclassified exceptions are given new unique SQL states.
The following SQL states are defined:
- Dddddd
- ddddd is the state returned by the database.
- SqlState = 'S22003'
- Arithmetic overflow. An operation whose result is a numeric type resulted in a value beyond the range supported.
- SqlState = 'S22007'
- Date time format not valid. A character string used in a cast from character to a datetime type had either the wrong basic format (for example, '01947-10-24') or had values outside the ranges allowed by the Gregorian calendar (for example, '1947-21-24').
- SqlState = 'S22008'
- Date time field overflow. An operation whose result is a datetime type resulted in a value beyond the range supported.
- SqlState = 'S22012'
- Divide by zero. A divide operation whose result data type has no concept of infinity had a zero right operand.
- SqlState = 'S22015'
- Interval field overflow. An operation whose result is of type INTERVAL resulted in a value beyond the range supported by the INTERVAL data type.
- SqlState = 'S22018'
- Character value for cast not valid.
- SqlState = 'SFN001'
- A SELECT function used in an IN predicate returned more than one column when only one column is allowed in this case.
- SqlState = 'SPS001'
- Target terminal not valid. A PROPAGATE to terminal statement attempted to use a terminal name that is not valid.
- SqlState = 'SPS002'
- Target label not valid. A PROPAGATE to label statement attempted to use a label that is not valid.
- SqlState = 'SPS003'
- PROPAGATE statement not valid in this context. Use the RETURN function instead.
- SqlState = 'MQW001', SqlNativeError = 0
- The bit stream does not meet the requirements for WebSphere® MQ messages. No attempt was made to put it to a queue. Retrying and queue administration does not resolve this problem.
- SqlState = 'MQW002', SqlNativeError = 0
- The target queue or queue manager names were not valid (that is, they could not be converted from Unicode to the queue manager's code page). Retrying and queue emptying does not resolve this problem.
- SqlState = 'MQW003', SqlNativeError = 0
- Request mode was specified but the "reply to" queue or queue manager names were not valid (that is, could not be converted from Unicode to the message's code page). Retrying and queue emptying does not resolve this problem.
- SqlState = 'MQW004', SqlNativeError = 0
- Reply mode was specified but the queue or queue manager names taken from the message were not valid (that is, they could not be converted from the given code page to Unicode). Retrying and queue emptying does not resolve this problem.
- SqlState = 'MQW005', SqlNativeError = 0
- Destination list mode was specified but the destination list supplied does not meet the basic requirements for destination lists. No attempt was made to put any message to a queue. Retrying and queue administration does not resolve this problem.
- SqlState = 'MQW101', SqlNativeError = returned by WebSphere MQ
- The target queue manager or queue could not be opened. Queue administration might resolve this problem but retrying does not.
- SqlState = 'MQW102', SqlNativeError = returned by WebSphere MQ
- The target queue manager or queue could not be written to. Retrying and queue administration might resolve this problem.
- SqlState = 'MQW201', SqlNativeError = number of destinations with an error
- More than one error occurred while processing a destination list. The message might have been put to zero or more queues. Retrying and queue administration might resolve this problem.
- Anything that the user has used in a THROW statement
- Use Uuuuuuu for user exceptions, unless imitating one of the exceptions defined above.
- Empty string
- All other errors.
Last updated Friday, 21 July 2017