WebSphere Message Brokers
File: ak05840_
Writer: Bill Oppenheimer

Task topic

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

Capturing database state

This topic describes your options if an error occurs when accessing an external database.

If an error occurs when accessing an external database, you have two options:
  • Let the broker throw an exception during node processing
  • Process the exception within the node itself using ESQL statements

The first option is the default; ESQL processing in the current node is abandoned. The exception is then propagated backwards through the message flow until an enclosing catch node, or the input node for this message flow, is reached. If the exception reaches the input node, any transaction is rolled back.

The second option requires an understanding of database return codes and a logical course of action to take when an error occurs. To enable this inline database error processing, you must clear the Filter, Database, or Compute node's Throw Exception On Database Error property. If you do this, the node sets the database state indicators SQLCODE, SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT, with appropriate information from the database manager instead of throwing an exception.

The indicators contain information only when an error (not a warning) occurs, unless you have selected the Treat Warnings As Errors property. In the case of successful and success with information database operations, the indicators contain their default success values.

You can use the values contained in these indicators in ESQL statements to make decisions about the action to take. You can access these indicators with the SQLCODE, SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT functions.

If you are attempting inline error processing, you must check the state indicators after each database statement is executed to ensure that you catch and assess all errors. When processing the indicators, if you meet an error that you cannot handle inline, you can raise a new exception either to deal with it upstream in a catch node, or to let it through to the input node so that the transaction is rolled back. You can use the ESQL THROW statement to do this.

You might want to check for the special case in which a SELECT returns no data. This situation is not considered an error and SQLCODE is not set, so you must test explicitly for it. This is described in Checking returns to SELECT.

Using ESQL to access database state indicators

The following ESQL example shows how to use the four database state functions, and how to include the error information that is returned in an exception:

DECLARE SQLState1 CHARACTER;   
DECLARE SQLErrorText1 CHARACTER;   
DECLARE SQLCode1 INTEGER;   
DECLARE SQLNativeError1 INTEGER;  
  
-- Make a database insert to a table that does not exist --
INSERT INTO Database.DB2ADMIN.NONEXISTENTTABLE (KEY,QMGR,QNAME) 
                              VALUES (45,'REG356','my TESTING 2');

--Retrieve the database return codes --
SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;

--Use the THROW statement to back out the database and issue a user exception--
THROW USER EXCEPTION MESSAGE 2950 VALUES
( 'The SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , 
SQLErrorText1 );

You do not have to throw an exception when you detect a database error; you might prefer to save the error information returned in the LocalEnvironment tree, and include a Filter node in your message flow that routes the message to error or success subflows according to the values saved.

The following sample program provides another example of ESQL that uses these database functions: You can view samples only when you use the information center that is integrated with the Message Brokers Toolkit.
Related concepts
Message flows overview
ExceptionList tree structure
ESQL overview
Message modeling
Coding ESQL to handle errors
Related tasks
Designing a message flow
Defining message flow content
Handling errors in message flows
Managing ESQL files
Checking returns to SELECT
Related reference
Compute node
Database node
Filter node
ESQL reference
ESQL database state functions
DECLARE statement
DECLARE HANDLER statement
INSERT statement
SET statement
THROW statement
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:33

ak05840_ This topic's URL is: