WebSphere Message Brokers
File: ak17990_
Writer: Bill Oppenheimer

Reference topic

This build: July 31, 2007 21:32:56

SQLSTATE function

SQLSTATE is a database state function that returns a 5 character data type of CHARACTER with a default value of '00000' (five zeros as a string).

Syntax

Within a message flow, you can access and update an external database resource using the available ESQL database functions in the Filter, Database, and Compute 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 check box, 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 the Throw exception on database error check box. 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.

If you choose 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

In ESQL, SQL states are variable length character strings. By convention, they are six characters long and contain only the characters 0-9, A-Z . The significance of the six characters is:
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 saying what the broker was doing at the time the exception occurred) is stepped over until the exception describing the original error is located.

The second stage is as follows:
  1. 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.
  2. 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"). In fact, 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". Using SQL states starting with the letter "U" allows a handler to match all user-defined and thrown exceptions with a LIKE’U%’ operator.
  3. 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 below.
  4. 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 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 = ‘S22004’
Null value not allowed. A null value was present in a place where null values are not allowed.
SqlState = ‘S22007’
Invalid date time format. A character string used in a cast from character to a date-time 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 date/time type resulted in a value beyond the range supported.
SqlState = ‘S22011’
SUBSTRING error. The FROM and FOR parameters, in conjunction with the length of the first operand, violate the rules of the SUBSTRING function.
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’
Invalid character value for cast.
SqlState = ‘SPS001’
Invalid target terminal. A PROPAGATE to terminal statement attempted to use an invalid terminal name.
SqlState = ‘SPS002’
Invalid target label. A PROPAGATE to label statement attempted to use an invalid label.
SqlState = 'MQW001', SqlNativeError = 0
The bit-stream does not meet the requirements for MQ messages. No attempt was made to put it to a queue. Retrying and queue administration will not succeed in resolving 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 will not succeed in resolving this problem.
SqlState = 'MQW003', SqlNativeError = 0
Request mode was specified but the "reply to" queue or queue manager names were not valid (i.e. could not be converted from unicode to the message's code page). Retrying and queue emptying will not succeed in resolving 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 will not succeed in resolving 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 will not succeed in resolving this problem.
SqlState = 'MQW101', SqlNativeError = As returned by MQ
The target queue manager or queue could not be opened. Queue administration may succeed in resolving this problem but retrying will not.
SqlState = 'MQW102', SqlNativeError = as returned by MQ
The target queue manager or queue could not be written to. Retrying and queue administration might succeed in resolving this problem.
SqlState = 'MQW201', SqlNativeError = number of destinations with an error
More than one error occurred while processing a destination list. The message may have been put to zero or more queues. Retrying and queue administration might succeed in resolving 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.
Related concepts
Message flows overview
ESQL overview
Related tasks
Defining message flow content
Developing ESQL
Capturing database state
Related reference
SQLCODE function
SQLERRORTEXT function
SQLNATIVEERROR function
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:56

ak17990_ This topic's URL is: