| Author |
Message
|
| Vitor |
 Posted: Mon Mar 26, 2012 11:53 am Post subject: Re: SQL Exception Scenario |
 |
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| EricCox wrote: |
| I'll look into that more. |
I'd start with this:
| EricCox wrote: |
| Code: |
2012-03-26 15:31:58.918468 10868 RecoverableException BIP2580E: ('.DB_EXCEPTION_HANDLER_RQ_Compute.Main', '59.15') : No external data source specified for this node.
An attempt was made to access an external database table, but no database inputs have been specified for this Compute, Filter or Database node. |
|
If you're missing the message written in the previous step, check it's being put outside the transaction & not being rolled back when this 2nd error occurs.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
 |
| rekarm01 |
| Posted: Mon Mar 26, 2012 3:14 pm Post subject: Re: Basic SQL Exception CONTINUE HANDLER |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
The XML parser is deprecated. Don't use it, especially for XML messages with namespaces. Use XMLNSC instead.
An exception handler has limited scope; it can only handle exceptions that occur within the block where it's declared.
If an uncaught exception occurs outside the scope of the handler, the Root message reverts to a prior state; any changes get backed out, including the saving of SQLSTATE from a previously handled exception.
The elements that use the "cfg-env" or "cfg-hdr" namespaces require double-quotes. None of the other elements do. |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Mar 26, 2012 11:43 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
rekarm01 said:
| Quote: |
| The XML parser is deprecated. Don't use it, especially for XML messages with namespaces. |
Absolutely right. I'm very surprised to see a message flow that uses the XML parser these days. I guess it's quite an old message flow? |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 27, 2012 12:04 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| kimbert wrote: |
rekarm01 said:
| Quote: |
| The XML parser is deprecated. Don't use it, especially for XML messages with namespaces. |
Absolutely right. I'm very surprised to see a message flow that uses the XML parser these days. I guess it's quite an old message flow? |
I was very surprised to see in V8, V7 and V6.1 InfoCenters that JMSStream messages are still parsed with XML parser  |
|
| Back to top |
|
|
| EricCox |
| Posted: Wed Mar 28, 2012 6:30 am Post subject: ESQL for Success and Exception Handling and the Exception |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
Now I am able to enhance the message with results from the database when the ODBC DSN is correct.
However, when the ODBC DSN is incorrect, when the SQLState Exception occurs as shown below the message flow does not produce any output message. The DECLARE HANDLER does not produce the expected results as shown in the documentation.
Can you guys help me understand what my next step would be to turn these ExceptionList details into a block of XML appended to the original message?
Can someone explain why the trace shows no calls to the HANDLER code?
My goal is simply to return to the service consumer the details of the exception appended to the InputRoot XML request message. I don't want the flow to halt processing. That is why I have tried to use a CONTINUE HANDLER.
| Code: |
CREATE COMPUTE MODULE DB_EXCEPTION_HANDLER_RQ_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
--CALL CopyMessageHeaders();
CALL CopyEntireMessage();
SET OutputRoot.Properties.MessageFormat = 'XML';
DECLARE ref_CustInfo REFERENCE TO "InputRoot"."XML"."soapenv:Envelope"."soapenv:Body"."getCustomerInfoForTellerRs"."CustInfo";
DECLARE ref_CustInfoOut REFERENCE TO "OutputRoot"."XML"."soapenv:Envelope"."soapenv:Body"."getCustomerInfoForTellerRs"."CustInfo";
DECLARE prodDescription CHARACTER;
SET OutputRoot.MQMD."Encoding" = MQENC_NATIVE;
SET OutputRoot.MQMD.CodedCharSetId = MQCCSI_Q_MGR;
SET OutputRoot.MQMD.UserIdentifier = 'N016438';
SET OutputRoot.MQMD.ReplyToQMgr = 'BKRD01';
SET OutputRoot.MQMD.ReplyToQ = 'Q1';
SET OutputRoot.MQMD.Report = BITOR(InputRoot.MQMD.Report, MQRO_PASS_MSG_ID);
SET OutputRoot.MQMD.Report = BITOR(OutputRoot.MQMD.Report, MQRO_PASS_CORREL_ID);
SET OutputRoot.MQMD.MsgType = MQMT_REPLY;
SET OutputRoot.MQMD.Format = MQFMT_STRING;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE'%'
BEGIN
SET OutputRoot.XMLNSC.Top.WHILE.mySQLCODE = SQLCODE;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLSTATE = SQLSTATE;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLNATIVEERROR = SQLNATIVEERROR;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLERRORTEXT = SQLERRORTEXT;
END;
DECLARE prodCode character;
DECLARE prodDesc1 ROW;
IF ref_CustInfo."RelInfo"[0]."AcctType"."Code" = 'AL' THEN
SET prodCode = COALESCE(ref_CustInfo."RelInfo"[0]."AcctInfo"."Product"."Code", '000') ;
SET "Environment"."prodDesc"[] =
(SELECT p.PROD_DESC
FROM Database.GIABKR.PRODCODES AS p
WHERE p.ACCT_TYPE = 'CHK' AND p.PROD_CODE = prodCode);
SET prodDescription = "Environment"."prodDesc"."PROD_DESC";
END IF;
END;
SET ref_CustInfoOut."RelInfo"."AcctInfo"."Product"."Desc"
=COALESCE(prodDescription, 'Product Description Unknown');
RETURN TRUE;
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
CREATE COMPUTE MODULE DB_EXCEPTION_HANDLER_RQ_Compute2
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
DECLARE Result BLOB;
DECLARE options INTEGER BITOR(FolderBitStream, ValidateContent, ValidateValue);
SET Result = ASBITSTREAM(InputRoot OPTIONS options CCSID 1208);
DECLARE MessageLength Integer LENGTH(Result);
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
|
| Code: |
See the following messages for details of the error.
2012-03-28 09:38:09.376882 12668 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2012-03-28 09:38:09.376922 12668 DatabaseException BIP2322E: Database error: SQL State ''28000''; Native Error Code '1017'; Error Text ''[DataDirect][ODBC Oracle driver][Oracle]ORA-01017: invalid username/password; logon denied ''.
The error has the following diagnostic information: SQL State ''28000'' SQL Native Error Code '1017' SQL Error Text ''[DataDirect][ODBC Oracle driver][Oracle]ORA-01017: invalid username/password; logon denied ''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
2012-03-28 09:38:09.377121 12668 UserTrace BIP2539I: Node 'DB_EXCEPTION_HANDLER_RQ.Trace1': Evaluating expression ''ExceptionList'' at ('', '1.3'). This resolved to ''ExceptionList''. The result was ''ROW... Root Element Type=16777216 NameSpace='' Name='Root' Value=NULL''.
2012-03-28 09:38:09.377304 12668 UserTrace BIP4060I: Data ''( ['MQROOT' : 0x128732968]
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDataFlowNode.cpp' (CHARACTER)
(0x03000000):Line = 616 (INTEGER)
(0x03000000):Function = 'ImbDataFlowNode::createExceptionList' (CHARACTER)
(0x03000000):Type = 'ComIbmComputeNode' (CHARACTER)
(0x03000000):Name = 'DB_EXCEPTION_HANDLER_RQ#FCMComposite_1_3' (CHARACTER)
(0x03000000):Label = 'DB_EXCEPTION_HANDLER_RQ.ComputeException' (CHARACTER)
(0x03000000):Catalog = 'BIPv600' (CHARACTER)
(0x03000000):Severity = 3 (INTEGER)
(0x03000000):Number = 2230 (INTEGER)
(0x03000000):Text = 'Node throwing exception' (CHARACTER)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbOdbc.cpp' (CHARACTER)
(0x03000000):Line = 228 (INTEGER)
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner' (CHARACTER)
(0x03000000):Type = '' (CHARACTER)
(0x03000000):Name = '' (CHARACTER)
(0x03000000):Label = '' (CHARACTER)
(0x03000000):Catalog = 'BIPv600' (CHARACTER)
(0x03000000):Severity = 3 (INTEGER)
(0x03000000):Number = 2321 (INTEGER)
(0x03000000):Text = 'Root SQL exception' (CHARACTER)
(0x01000000):Insert = (
(0x03000000):Type = 2 (INTEGER)
(0x03000000):Text = '-1' (CHARACTER)
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbOdbc.cpp' (CHARACTER)
(0x03000000):Line = 357 (INTEGER)
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner' (CHARACTER)
(0x03000000):Type = '' (CHARACTER)
(0x03000000):Name = '' (CHARACTER)
(0x03000000):Label = '' (CHARACTER)
(0x03000000):Catalog = 'BIPv600' (CHARACTER)
(0x03000000):Severity = 3 (INTEGER)
(0x03000000):Number = 2322 (INTEGER)
(0x03000000):Text = 'Child SQL exception' (CHARACTER)
(0x01000000):Insert = (
(0x03000000):Type = 5 (INTEGER)
(0x03000000):Text = '28000' (CHARACTER)
)
(0x01000000):Insert = (
(0x03000000):Type = 2 (INTEGER)
(0x03000000):Text = '1017' (CHARACTER)
)
(0x01000000):Insert = (
(0x03000000):Type = 5 (INTEGER)
(0x03000000):Text = '[DataDirect][ODBC Oracle driver][Oracle]ORA-01017: invalid username/password; logon denied ' (CHARACTER)
)
)
)
)
)
'' from trace node 'DB_EXCEPTION_HANDLER_RQ.Trace1'.
The trace node 'DB_EXCEPTION_HANDLER_RQ.Trace1' has output the specified trace data.
This is an information message provided by the message flow designer. The user response will be determined by the local environment.
2012-03-28 09:38:09.377353 12668 UserTrace BIP4067I: Message propagated to output terminal for trace node 'DB_EXCEPTION_HANDLER_RQ.Trace1'. |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Mar 28, 2012 6:41 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| EricCox wrote: |
| Can you guys help me understand what my next step would be to turn these ExceptionList details into a block of XML appended to the original message? |
I'd start from something like this untested example:
| Code: |
| CREATE LASTCHILD OF OutputRoot.ErrorMessage FROM InputExceptionList; |
I'm also interested in why you have double quotes round all the field names in your code, and why you seem to be using 'XML' as a message domain. That's not a good domain to use as indicated above. Whatever you're doing, don't do that. Seriously. Take 5 minutes and fix that, even though that's almost certainly nothing to do with your problem.
_________________
Honesty is the best policy.
Insanity is the best defence.
Last edited by Vitor on Wed Mar 28, 2012 6:43 am; edited 1 time in total |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 28, 2012 6:42 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
I'd start from something like this untested example:
| Code: |
| CREATE LASTCHILD OF OutputRoot.ErrorMessage FROM InputExceptionList; |
|
InputExceptionList has no parser associated with it. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Mar 28, 2012 6:45 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Vitor wrote: |
I'd start from something like this untested example:
| Code: |
| CREATE LASTCHILD OF OutputRoot.ErrorMessage FROM InputExceptionList; |
|
InputExceptionList has no parser associated with it. |
The assumption is that OutputRoot.ErrorMessage is already associated with an XML parser. If you just want the message then yes you need:
| Code: |
| CREATE LASTCHILD OF OutputRoot DOMAIN ('XMLNSC') FROM InputExceptionList; |
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 28, 2012 6:46 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| Maybe if you had said "OutputRoot.XMLNSC.ErrorMessage" I would have understood that. |
Doh! 
I said it was untested.
Mineral water for me for the rest of the day. And serious pre-posting review.
(It'll be a refreshing change to actually read what I'm posting...) |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Mar 28, 2012 6:55 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| However, when the ODBC DSN is incorrect, when the SQLState Exception occurs as shown below the message flow does not produce any output message. The DECLARE HANDLER does not produce the expected results as shown in the documentation. |
There is a special case of connecting to the DSN on the node, which is done by the node when it is first entered before any of the ESQL code you wrote is run. If this fails, then you will not see this exception in your handler, as it happened before any of your ESQL in this node was run. The exception that is thrown will by default be written to the event/sys log, but if you connect the Catch terminal of the Input node (or use a try-catch node) then you will be able to see this exception within the flow.
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| EricCox |
| Posted: Wed Mar 28, 2012 7:17 am Post subject: |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
| mgk wrote: |
| Quote: |
| However, when the ODBC DSN is incorrect, when the SQLState Exception occurs as shown below the message flow does not produce any output message. The DECLARE HANDLER does not produce the expected results as shown in the documentation. |
There is a special case of connecting to the DSN on the node, which is done by the node when it is first entered before any of the ESQL code you wrote is run. If this fails, then you will not see this exception in your handler, as it happened before any of your ESQL in this node was run. The exception that is thrown will by default be written to the event/sys log, but if you connect the Catch terminal of the Input node (or use a try-catch node) then you will be able to see this exception within the flow.
Kind Regards, |
Thanks very much for that clarification. This is exactly the kind of 'expected behavior' I'm trying to identify.
Grasshopper has much to learn. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Mar 28, 2012 8:28 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| EricCox wrote: |
Can you guys help me understand what my next step would be to turn these ExceptionList details into a block of XML appended to the original message?
| Code: |
DECLARE ref_CustInfoOut REFERENCE TO "OutputRoot"."XML"."soapenv:Envelope"."soapenv:Body"."getCustomerInfoForTellerRs"."CustInfo";
...
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE'%'
BEGIN
SET OutputRoot.XMLNSC.Top.WHILE.mySQLCODE = SQLCODE;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLSTATE = SQLSTATE;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLNATIVEERROR = SQLNATIVEERROR;
SET OutputRoot.XMLNSC.Top.WHILE.mySQLERRORTEXT = SQLERRORTEXT;
END; |
|
You create an Output message with two bodies, XML, and XMLNSC. MQOutput node will process only the first one, if I remember correctly. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Mar 28, 2012 8:33 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Esa wrote: |
| You create an Output message with two bodies, XML, and XMLNSC. MQOutput node will process only the first one, if I remember correctly. |
What?
I mean yes, the MQOutput node only serialises the first message body if you have 2 but how does that achieve what the OP is trying to achieve & why would you ever suggest the use of the XML domain for anything?
Or have I not flushed through with enough mineral water yet?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| EricCox |
| Posted: Wed Mar 28, 2012 8:56 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
| Vitor wrote: |
| Esa wrote: |
| You create an Output message with two bodies, XML, and XMLNSC. MQOutput node will process only the first one, if I remember correctly. |
What?
I mean yes, the MQOutput node only serialises the first message body if you have 2 but how does that achieve what the OP is trying to achieve & why would you ever suggest the use of the XML domain for anything?
Or have I not flushed through with enough mineral water yet? |
I appreciate your insistence on XMLNSC. However, when I change to XMLNSC the flow stops appending the one XML element to the Output message. I changed the InputNode Parser and the two REFERENCE declarations.
Are there simple steps to accomplish the switch from XML to XMLNSC? I'm new to Broker.
Of course I can read the entire suite of documentation, which has no good working examples.
The Input Message only has the standard soap namespace delcaration as such:
| Code: |
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header> . </soapenv:Header>
<soapenv:Body>
|
The elements of the body are not namespace qualified.
Thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Mar 28, 2012 9:04 am Post subject: Re: ESQL for Success and Exception Handling and the Exceptio |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| EricCox wrote: |
| However, when I change to XMLNSC the flow stops appending the one XML element to the Output message. I changed the InputNode Parser and the two REFERENCE declarations. |
Have you tried a user trace to see why? I would theorise that it's still appending the element to the XML message body while the XMLNSC message body is the one being serialised. However, my associate seems to be deliberately suggesting that as a strategy, so I'll defer to him on this.
| EricCox wrote: |
The Input Message only has the standard soap namespace delcaration as such:
| Code: |
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header> . </soapenv:Header>
<soapenv:Body>
|
|
Which is a shame because the XML domain doesn't support namespaces; one reason it's depreciated. The XMLNSC does support namespaces.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
