| Author |
Message
|
| sachinramesh |
 Posted: Wed Jul 09, 2008 11:29 pm Post subject: data inserting into the database even when exception occurs |
 |
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
my flow includes mqi/p-->compute1-->compute2--->mqo/p and the error sub flow.
In my case in compute1 i am inserting data into database A
In Compute2 i am inserting into database B.
My flow is working fine now,but for testing purpose i had Quiesed the database B and when i had run the flow.
i am getting an exception while inserting into database B, and the flow is going to the error sub flow and here it is inserting into the ReprocQ instead of ErrorQ
based on one condition.
CODE:
CREATE FILTER MODULE CUP_ERROR_SOL_TMM_Filter
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE CNT INTEGER 0;
SET Environment.Variables.DBErrorCode[] =(SELECT E.Text FROM ExceptionList.RecoverableException.RecoverableException.RecoverableException.DatabaseException.DatabaseException.Insert[] AS E where E.Text IN ('23505', '08001', '40001','40003','-30081'));
SET CNT = CARDINALITY(Environment.Variables.DBErrorCode[]);
IF (CNT>0) THEN
RETURN TRUE;
ELSE
SET Environment.Variables.DBErrorCode[]=(SELECT E.Text FROM ExceptionList.RecoverableException.DatabaseException.DatabaseException.Insert[] AS E where E.Text IN ('23505', '40001', '08001','40003','-30081'));
SET CNT = CARDINALITY(Environment.Variables.DBErrorCode[]);
IF (CNT>0) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IF;
END;
END MODULE;
IF Returns TRUE ---> ReprocQ
else---> ERRORQ
In my case it returns true...
Now My message is been put into the ReprocQ,and when i check both the database my data is been inserted into Database A but not into Database B.
Transaction Properties in both the COMPUTE nodes are set TO AUTOMATIC.
I am not commmiting the data at point in the code.
AFAIK when the COMPUTE nodes properties are set TO AUTOMATIC and an excepttion throws the data willnot be commited in any of the database.
But why is it happening in my case?
The exception i am getting is :Database Agent not found or shut down forceble.
what shuld i do, so that my data is not inserted in either of the databases. |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Wed Jul 09, 2008 11:35 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Jul 09, 2008 11:37 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
Where is the exception handling plugged in ?
Can you paste the code from your first compute also.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Wed Jul 09, 2008 11:37 pm Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
SORRY....! By mistake i had posted it twice..
I thought the first time it hasnt posted ..
anyways pls help me now ...in this issue. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Wed Jul 09, 2008 11:57 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Make sure you have Throw Exception on Database error clicked on the advanced tab of the node.
I also believe if you have any failure or catch nodes wired on any nodes other than the input node, then wmb assumes you are dealing with the exception yourself and treats the flow as successful. At this point all transactions will be commited.
_________________
Cheers |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 12:27 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
Where is the exception handling plugged in ?
Can you paste the code from your first compute also.
Regards. |
The Catch terminal of mqi/p and both the failure terminals has been plugged to the error subflow ..
I am working on MB 5.0
The code in BOTH the nodes are same except it is inserting into two diff.databases
I am not able to post the code..it says service unavaialble.. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 12:34 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| AkankshA wrote: |
Make sure you have Throw Exception on Database error clicked on the advanced tab of the node.
I also believe if you have any failure or catch nodes wired on any nodes other than the input node, then wmb assumes you are dealing with the exception yourself and treats the flow as successful. At this point all transactions will be commited. |
Throw Exception on Database error has been checked in the compute node properties |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Jul 10, 2008 1:14 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
| sachinramesh wrote: |
| The Catch terminal of mqi/p and both the failure terminals has been plugged to the error subflow .. |
Both the failure terminals ? I hope you did not mean failure terminal of the compute also...
Are you handling any errors in the first compute ?
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 1:39 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
| sachinramesh wrote: |
| The Catch terminal of mqi/p and both the failure terminals has been plugged to the error subflow .. |
Both the failure terminals ? I hope you did not mean failure terminal of the compute also...
Are you handling any errors in the first compute ?
Regards. |
The failure terminal of both the compute nodes has been connected to the error sub flow.
We are not handling any errors in the compute node.The code only inserts data into the database based on some conditions(the code is same in bothe the compute nodes). |
|
| Back to top |
|
|
| AkankshA |
| Posted: Thu Jul 10, 2008 1:44 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Please do not connect the error sub flow directly with compute nodes...
if you do so i.e. attach failure or catch nodes wired on any nodes other than the input node, then broker assumes you are dealing with the exception yourself and treats the flow as successful.
and hence At that point all transactions will be commited....
attach the error sub flow only with Input node terminals...
_________________
Cheers |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 1:55 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| AkankshA wrote: |
Please do not connect the error sub flow directly with compute nodes...
if you do so i.e. attach failure or catch nodes wired on any nodes other than the input node, then broker assumes you are dealing with the exception yourself and treats the flow as successful.
and hence At that point all transactions will be commited....
attach the error sub flow only with Input node terminals... |
So in my case if i disconnect the failure terminals of both the compute nodes and connect the failure/catch terminal of the input node to the error sub flow..will the flow works perfectly i.e it shuld not commit in either of the database whenever an exception occurs. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Jul 10, 2008 2:00 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
| sachinramesh wrote: |
| So in my case if i disconnect the failure terminals of both the compute nodes and connect the failure/catch terminal of the input node to the error sub flow..will the flow works perfectly i.e it shuld not commit in either of the database whenever an exception occurs. |
Yes.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 2:20 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
| sachinramesh wrote: |
| So in my case if i disconnect the failure terminals of both the compute nodes and connect the failure/catch terminal of the input node to the error sub flow..will the flow works perfectly i.e it shuld not commit in either of the database whenever an exception occurs. |
Yes.
Regards. |
But the flow still goes to ReprocQ and inserting in the first database.
Is this bcos of the code i am writing in the error flow
CREATE FILTER MODULE CUP_ERROR_SOL_TMM_Filter
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE CNT INTEGER 0;
SET Environment.Variables.DBErrorCode[] =(SELECT E.Text FROM ExceptionList.RecoverableException.RecoverableException.RecoverableException.DatabaseException.DatabaseException.Insert[] AS E where E.Text IN ('23505', '08001', '40001','40003','-30081'));
SET CNT = CARDINALITY(Environment.Variables.DBErrorCode[]);
IF (CNT>0) THEN
RETURN TRUE;
ELSE
SET Environment.Variables.DBErrorCode[]=(SELECT E.Text FROM ExceptionList.RecoverableException.DatabaseException.DatabaseException.Insert[] AS E where E.Text IN ('23505', '40001', '08001','40003','-30081'));
SET CNT = CARDINALITY(Environment.Variables.DBErrorCode[]);
IF (CNT>0) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IF;
END;
END MODULE;
IF Returns TRUE ---> ReprocQ
else---> ERRORQ
But still my doubt is why is the data not rollbacked when it is going to the ReprocQ. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Jul 10, 2008 3:22 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi sachinramesh,
So now you have your error handler ONLY linked with your MQInput node right ?
Where is this ReprocQ and ErrorQ ?
Are you doing any inserts into database in your error path ?
This code which you keep pasting where does it lead to ?
If you have not connected the compute node failure terminals and the transaction in the nodes was Automatic, then the inserts you made should surely have been rolled back...now the question remains as to what you are doing in the failure path.
Regards. |
|
| Back to top |
|
|
| sachinramesh |
| Posted: Thu Jul 10, 2008 3:53 am Post subject: |
|
|
Disciple
Joined: 20 Feb 2007
Posts: 170
|
| elvis_gn wrote: |
Hi sachinramesh,
So now you have your error handler ONLY linked with your MQInput node right ?
Where is this ReprocQ and ErrorQ ?
Are you doing any inserts into database in your error path ?
This code which you keep pasting where does it lead to ?
If you have not connected the compute node failure terminals and the transaction in the nodes was Automatic, then the inserts you made should surely have been rolled back...now the question remains as to what you are doing in the failure path.
yes i have the error handler linked ONLY to the MQi/p node.
The ReprocQ and ErrorQ are in the ERROR FLOW
Iam NOT doing any inserts inthe Database in the error path.
The code i had pasted is in the error flow.
I had found the problem in my case, when ever the message goes to the ReprocQ a Scheduler is supposed to run which picks up the message from the ReprocQ and puts it into the MQ i/p node again for processing.
In my case the Scheduler is missing and the message is staying in the reprocQ.
AnyWays Elvis & Akanksha Thanks for the Help
Regards. |
|
|
| Back to top |
|
|
|
|
