| Author |
Message
|
| shashivarungupta |
 Posted: Mon Jan 05, 2015 5:43 pm Post subject: Mapping Database Insert Prevention ? |
 |
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
Hi,
I am inserting rows into a database table based on supplied input (SOAP Message, over SOAPInput Node), database interaction is done via Mapping Node.
The question is, how can I prevent intert into database if database exception happens, at the middle of Input Message Assembly ?
For ex:
<Main>
<Parent>A</Parent>
<Child1>C1</Child1>
<Child2>C2</Child2>
<Child3>C3</Child3>
<Child4>C4</Child4>
<Child5>C5</Child5>
</Main>
Suppose, Insert has been susscessful for parent A in a table (table1) AND for all the children into different table (table2) C1, C2, C3 is successful but C4 is not valid as per Database Table's Column Value (say string limit or datatype mismatch).
How can I say Message Broker[v8] to roll back all the previous transactions which were inserted before C4 (from the different tables) ?
Mapping node has Transaction 'Automatic' by default. Whereas SOAPInput Transaction Mode is 'yes' but that's applicable for JMS transport.
Database Oracle, over JDBC Connection.
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Jan 06, 2015 6:17 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Doesn't matter what the SOAPInput Transaction mode is.
Can you set the mapping node's transaction mode to Yes?
Can you make the exception bubble down to the SOAP Input Node Failure Terminal? You are getting an exception in the mapping node yes?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Tue Jan 06, 2015 12:33 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| fjb_saper wrote: |
| Doesn't matter what the SOAPInput Transaction mode is. |
Yup..because HTTP protocol resource manager cann't participant in the coordinated transaction.
| fjb_saper wrote: |
| Can you set the mapping node's transaction mode to Yes? |
Nope, deafult is 'Automatic'.
That means it totally depends on the overall behaviour of the message flow. If successful then commit otherwise rollback.
But this function doesn't seem to work in this case because Message flow is not participanting in the coordinated transaction and there is no external transaction manager as MQ / Database.
| fjb_saper wrote: |
| Can you make the exception bubble down to the SOAP Input Node Failure Terminal? |
If I don't wire the Failure & Catch terminal of SOAPInput node to handle them, the whole transaction is rolled back (which seems fine) because "message flow transactions are managed by the broker, by default. and these transactions are known as broker-coordinated transactions or partially coordinated transactions."
But If I handle that exception (in mapping node, down the flow/subflow) by catching it at SOAPInput node then it prevents the Rollback, which I don't need, where some transaction are getting committed and some not.
Or have I misunderstood your question ?
| fjb_saper wrote: |
| You are getting an exception in the mapping node yes? |
Yes, when doing the insert for child fields, in the database table.
Additionally, as per InfoCenter "If you want the failure transform to cause the execution of the map to stop when the database transform receives an SQL exception, remove the Failure from the transform."
I don't think we can achieve the complete rollback of transaction (globally coordinated transaction) until we use external transaction manager like MQ and/or database, cause HTTP Protocol don't support this.
Or if you've another thought/suggestion, kindly share.
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jan 06, 2015 1:01 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Remember that if you are wiring the catch terminal without re-throwing at the end of the catch path, you are still committing...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Tue Jan 06, 2015 1:10 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| fjb_saper wrote: |
| Remember that if you are wiring the catch terminal without re-throwing at the end of the catch path, you are still committing... |
That means... "If I connect the Catch terminal of the input node, that indicates that the flow handles all the exceptions that are generated anywhere in the out flow. The broker performs no rollback, and takes no action, unless there is an exception on the catch flow. If I want any rollback action after an exception has been raised and caught, I must provide this in the catch flow."
Right ?
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jan 06, 2015 1:16 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Tue Jan 06, 2015 1:23 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
Okey.
As per Info Center v8, "If the resource does not have transactional behavior, all the work that it does is committed immediately. For example, files and HTTP connections do not support transactions."
In this case I've SOAPInput node and that's on HTTP based protocol.
Then "How" to deal with it when some of the work is committed already in the database, in the different tables ? [ While Handling the Exception in the Catch Terminal of SOAPInput Node and Rethrowing an exception in the Catch Flow to Rollback the Committed Work ]
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jan 06, 2015 1:47 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Perhaps you should consider not using a mapping node. |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Wed Jan 07, 2015 1:55 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| Quote: |
| Perhaps you should consider not using a mapping node. |
hmm.. Perhaps is a big word 
By the way, that Complete Rollback functionality has been tried & possible by Pushing the Catch Flow to the Throw Node at the end (as it was expected in this case), (By Propagating the flow to Throw Node, can be Conditional, as in this case).
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jan 08, 2015 4:56 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Then "How" to deal with it when some of the work is committed already in the database, in the different tables ? |
Maybe you could split the flow into two parts. The first one simply bridges from SOAP to MQ. The second flow starts with an MQInput and then does the processing transactionally.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 08, 2015 6:40 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| kimbert wrote: |
| Quote: |
| Then "How" to deal with it when some of the work is committed already in the database, in the different tables ? |
Maybe you could split the flow into two parts. The first one simply bridges from SOAP to MQ. The second flow starts with an MQInput and then does the processing transactionally. |
Well, apparently just the local transaction (rather than a non-existing global transaction) resolved the issue - as long as shashivarungupta made sure to roll back the transaction to the SOAPInput node.
 |
|
| Back to top |
|
|
| martinb |
| Posted: Thu Jan 15, 2015 12:20 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Agreed.
Mapping node automatic transaction mode means all database transforms that do not have a "Failure" transform (which is a internal to the map "catch"), will be rolled back if the exception is passed back for the flow's input node to process. |
|
| Back to top |
|
|
|
|
