| Author |
Message
|
| hallmark |
 Posted: Mon Feb 07, 2011 5:56 am Post subject: declare handler to catch CAST exceptions |
 |
|
Voyager
Joined: 10 Mar 2005
Posts: 76
|
Hi
While I think there is a better way to solve my real problem I have become confused with the use of HANDLERs.
I have to set an output field with the value of a field on the incoming message, but in the instance where the input message does not contain an integer value, I don't want to set the output field.
To that end, I attempt to cast the input field as an integer and use a handler to weed out those messages that fail this test.
If I use a wildcard this works, but all manner of exceptions in this flow get caught. I notice that there is an SQLSTATE for CAST errors
SqlState = 'S22018' which I have assumed I can use for this specific HANDLER.
However this does not work, and when I look at the exception list (when I don't handle the error) I don't see any mention of this sqlstate. When I query sqlstate in the handler it is empty (but not NULL?)
I do not that the exception list does contain a BIP message for the invalid cast, is there any way to make the handler react to just that?
Code below
| Code: |
CREATE COMPUTE MODULE HandlerTesting_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
--DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE'S22018' <<This does not work and a exception not caught here
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE '%'
BEGIN
DECLARE state CHARACTER SQLSTATE;
DECLARE code CHARACTER SQLERRORTEXT;
CALL CopyEntireMessage();
PROPAGATE TO TERMINAL 'Out1';
RETURN FALSE;
END;
CALL CopyEntireMessage();
DECLARE testNumber CHARACTER InputBody.test.number;
SET OutputRoot.XMLNSC.test.converted = CAST(testNumber AS INTEGER);
RETURN TRUE;
END;
|
Example message
| Code: |
| <test><number>a1191</number></test> |
Am I missing something (probably).
I am probably going to use a different method to ignore these values, this kind of feels a try-catchy and a bit defensive. However I am surprised that I didn't get the handler to work.
Thanks in advance
_________________
Rob |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Feb 07, 2011 6:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Cast has a default value - at least in recent levels.
You can use that and test the return to determine if you had to use the default.
You can also preemptively REPLACE known bad characters, or otherwise. There's been a few recent discussions, including some examples with code bits. |
|
| Back to top |
|
|
| hallmark |
| Posted: Mon Feb 07, 2011 6:50 am Post subject: |
|
|
Voyager
Joined: 10 Mar 2005
Posts: 76
|
Thanks mqjeff I will look into alternatives to solving my actual issue. Do you have any comment on my use of the HANDLER?
Looking again at the ExceptionList I can see that an error with a valid SQLSTATE has in the tree an sqlException whereas my erroneous cast has conversionException.
Am I misinterpreting S22018 in thinking that it covers Broker CAST failures? A good example of a broker failure is mis-spelling the Out terminal name (as I did in my example)  which can be caught by a handler by reference to the SQLSTATE:
| Code: |
SqlState = 'SPS001'
Target terminal not valid. A PROPAGATE to terminal statement attempted to use a terminal name that is not valid. |
Cheers
_________________
Rob |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 07, 2011 7:32 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I can't comment really on the SQLSTATE.
I suspect that CAST doesn't throw it because of the default. But I'm not sure.
Perhaps mgk will be along and have something meaningful to add...  |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Feb 07, 2011 10:50 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Unfortunately only CASTS involving Char-> Decimal and the Base64Decode function currently use the S22018 SQL state code which is why you do not see it on a CAST of a char -> integer...
Using a Default clause as suggested is good idea...
_________________
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 |
|
|
| j.f.sorge |
| Posted: Mon Feb 07, 2011 11:05 am Post subject: Re: declare handler to catch CAST exceptions |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| hallmark wrote: |
...
If I use a wildcard this works, but all manner of exceptions in this flow get caught.
... |
You may implement one FUNCTION which does the CAST and put the HANDLER there so that it will only catch the CASTing-exceptions.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| hallmark |
| Posted: Tue Feb 08, 2011 2:32 am Post subject: |
|
|
Voyager
Joined: 10 Mar 2005
Posts: 76
|
Thanks all, confusion resolved. <insert vague sarcastic comment here about CAST behaving in different ways for the same type of issue>
As to solving the actual problem, use of DEFAULT is a good way, maybe better in fact that what I was trying to do.
All failed CASTS will get DEFAULTed to -1 and this will be tested for.
Cheers
Rob
_________________
Rob |
|
| Back to top |
|
|
|
|
