| Author |
Message
|
| hailokesh |
 Posted: Thu Nov 06, 2014 12:32 pm Post subject: Exception handling in Compute node using ESQL |
 |
|
Newbie
Joined: 06 Nov 2014
Posts: 3
|
Hi All,
I am new to IIB v9 and started learning the concepts and working on the new assignments. The task that i am currently working on needs some exception handling. Here is the task i get a batch of transactions, first I have to check whether this batch is a new one or already been processed by checking the batch id from the batch file and comparing to the database by calling a storeproc. If this is a new batch I will have to read every transaction in the batch file. When I am processing these transactions which are inserting some data to database, if I get any sql error then I should take that particular transaction and put it in a error queue and continue processing the remaining transactions.
Currently when I read about DECLARE CONTINUE HANDLER from info center
I tried to use it in the code
Reading the list of transactions and iterating it using while statement
Declare a integer 1;
Declare b integer;
b= list of transactions
While a<=b Do
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE '%'
Begin
-- calling the stored proc
--if the stored proc returns me errorCode then i have to put that transaction on a error queue.
END;
if(errorCode <> 0)
-- throw user exception values();
-- in the catch block i am sending it to error queue
endif;
set a= a+1;
end while;
But when i ran this code it was coming up to while loop and simply skips to end and run the if condition line
if(errorCode <>0) and then incrementing the value of 'a', it was not calling stored proc.
Is there any esql statement to send the transaction to a queue, or propagate to out terminal which will be connected to error queue whenever we get a error from stored proc.
Currently as soon as it gets an sql error it stops there, but i want it to continue processing by putting that particular transaction to a error queue.
Please advice me, am i thinking in the right or any other best ways to implement this scenario.
Thank you all for looking in and helping me.... |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Nov 06, 2014 12:52 pm Post subject: Re: Exception handling in Compute node using ESQL |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Aside from commenting on your code, which I assume to be pseudo-code and not the actual ESQL you're using:
| hailokesh wrote: |
| Is there any esql statement to send the transaction to a queue |
No, but there is a node to do that
| hailokesh wrote: |
| propagate to out terminal |
Yes - guess what it's called.
| hailokesh wrote: |
| which will be connected to error queue whenever we get a error from stored proc. |
You might want to rethink that.
| hailokesh wrote: |
| Currently as soon as it gets an sql error it stops there, but i want it to continue processing by putting that particular transaction to a error queue. |
Reasonable.
| hailokesh wrote: |
| Please advice me, am i thinking in the right or any other best ways to implement this scenario. |
The design is fine, I do feel you should seek guidance on the implementation. There's nothing to beat getting your hands dirty with the product when you're new, but IIB is not a straightforward product and this forum is not the best place to get detailed training. Find yourself a mentor who can guide you through the process, and answer questions like this.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Nov 06, 2014 12:55 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Also, MAKE USE OF THE SAMPLES. Read them, import them, look at them, study them, try them out.
GO look for redbooks and devWorks articles. |
|
| Back to top |
|
|
| hailokesh |
| Posted: Thu Nov 06, 2014 3:13 pm Post subject: |
|
|
Newbie
Joined: 06 Nov 2014
Posts: 3
|
thank you Vitor for the reply,
Is there any esql statement to send the transaction to a queue
No, but there is a node to do that
could you please tell me what is the node to use.
I am searching for the DEVWorks articles to learn.
Thanks again Vitor.. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Nov 07, 2014 5:40 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| hailokesh wrote: |
| could you please tell me what is the node to use. |
Oh really! There are 5 non-depreciated nodes in the Websphere MQ drawer; working out which one sends messages to a queue from the names alone is as hard as working out what the ESQL verb to propagate a message to a terminal is.
And that's assuming you haven't realised the nodes have tool tips when you hover over them.
| hailokesh wrote: |
| I am searching for the DEVWorks articles to learn. |
There are no DevWorks articles to learn. There are articles explaining how to do specific tasks, but no general training. Given the level of knowledge you're demonstrating on the product, learning like that will be a long and painful road for you. I believe my most worthy associate was expecting you to use all off the resources he named, most especially the samples.
A lot of which put messages on queues......
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| magvlvri |
| Posted: Fri Nov 07, 2014 7:42 am Post subject: |
|
|
Apprentice
Joined: 07 Nov 2014
Posts: 26
|
To begin with, your esql is just coded in the wrong way.
Below is an example from infocenter for continue handler.
You have got the Begin and End statements wrongly placed.
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE'%'
BEGIN
---YOUR HANDLER CODE GOES HERE
END;
---BELOW IS THE CODE THAT NEEDS TO BE CATCHED FOR ERRORS
PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Invoices' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Sales' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Parts' TO Database.DSN1;
END;
As far as sending the msg to a queue, thats pretty basic. Dont just jump to error handling assignments while learning without learning the basics of how a msg is put or read or how a WS call is made. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Nov 07, 2014 8:34 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| magvlvri wrote: |
| To begin with, your esql is just coded in the wrong way. |
| Vitor wrote: |
| which I assume to be pseudo-code and not the actual ESQL you're using |
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| hailokesh |
| Posted: Sat Nov 08, 2014 11:24 am Post subject: |
|
|
Newbie
Joined: 06 Nov 2014
Posts: 3
|
Thank you all for looking,
So I came up with 2 or 3 approaches to achieve this,
First one is making into to different flows so the first flow will read the batch file and put each transaction in a Queue. The second flow will read from that queue and calls the stored procedure and if we get a failure it will send back to failure terminal of mqInput node and then put it to Error queue using mqOutput node.
Second approach was handling the sql exception in stored procedure and returning the errorCode and errorDescription as it will not throw an exception then based on the errorCode propagate to the out1 terminal which will connected to a MQOutput (errorQueue).
As of now i tried the second approach and it was working fine while putting the error message on queue i updated it with errorCode and errorMessage so that the people who looks at error Queue, will know what was the reason of failure for that transaction.
Thank you all guys. |
|
| Back to top |
|
|
|
|
