MQSeries.net :: View topic

prak · Posted: Sat Apr 27, 2002 12:13 pm Post subject:

Hi..

I am using MQSI 2.1 in WIn 2k with DB2 7.2

I've read all the info about handling database errors and creating user exceptions using a Try-Catch and ExceptionLists but it is just not working for me even for a simple case like the one below. PLEASE HELP !!

Here is what I have:

MQInput1 (out terminal)-->TryCatch1 (try)--> Compute1 (out)-->MQOutput1

The Compute Node (called Compute1) in the above flow has the following ESQL to insert a row in the USER table of a database:

-----------------------------------------------------------------
SET OutputRoot = InputRoot;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
DECLARE SQLState1 CHARACTER;
DECLARE SQLCode1 INTEGER;
DECLARE SQLText1 CHARACTER;
insert into Database.USER values(InputRoot.XML.User.UserID,InputRoot.XML.User.Password);
SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLText1 = SQLERRORTEXT;

IF SQLCode1 <> 0
THROW USER EXCEPTION MESSAGE 2950 VALUES('SQL State:', SQLState1, 'SQL Code:', SQLCode1, 'SQL Error Text:',SQLText1 );
END IF;
---------------------------------------------------------------------

The test XML message input to test the flow is:
<User><UserID>john</UserID><Password>secret</Password></User>
----------------------------------------------------------

Also, the catch terminal of the Try-Catch Node is connected to another Compute Node (called Compute2) so that the second Compute Node can look at the exception list and put in some meaningful message based on the SQLCode/SQLState. See below for that portion of the flow:

TryCatch1 (catch)-->Compute2 (out)-->MQOutput1

The second Compute Node (Compute2) has the following ESQL:

---------------------------------------------------
SET OutputRoot = InputRoot;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
SET OutputRoot.XML.(XML.Element)Status = 'Insert Failed';
SET OutputRoot.XML.(XML.Element)ErrorDetails = InputExceptionList;
---------------------------------------------------------------------------

The failure terminals of both Compute Nodes are left unconnected and for the first Compute Node (Compute1), the "Throw Exception DB Error" option is unchecked and the "Compute Mode" is set to ALL.

What is happening is that when there is a database error (such as a duplicate row insert), NO exception is being generated. The input message just passes to the MQOutput mode in its original state. Nothing is pushed back to Try-Catch node.

If I remove the "Throw User Exception.." code from the ESQL and enable the "Throw Exception on DB Err" option so that MQSI is the ONE generating the exception, then it works. In that case, an exception is generated by MQSI and the message is routed via the Catch terminal of the Try-Catch to Compute Node 2.
Unfortunately, in this case, in Compute Node 2, no ExceptionList information gets added to the message. And the message just passes to MQOutput Node with only one extra tag which is <Status>Insert Failed</Status>.

So here are my three questions:

(a) What is it that I am doing wrong that my own "Throw User Exception..." code is not working?

(b) How do I access the error message from within an Exception List in the case I am throwing a USER EXCEPTION myself and inserting some error message? i.e. if
I say THROW USER EXCEPTION MESSAGE 2950 VALUES ('Something went wrong')
how do I access this err msg later in another compute node or filter node?

(c)How do I access the various elements in an Exception List in case MQSI is the one throwing the Exception such as in case of a Database Error?

------------------------------------------------------

Thank you for help. Hopefully detailed answers to this query will serve as a guide to other newbies also.