| Author |
Message
|
| chetu777 |
 Posted: Tue Jan 31, 2012 9:15 pm Post subject: Rollback required when calling store procedure |
 |
|
Acolyte
Joined: 07 Sep 2009
Posts: 59
|
Hi All,
In my message flow I am calling a stored procedure.
Now the reuqirement is that they want MB to roll back any operations done by Store procedure(SP) on DB, if SP fails in its operation. They expect this to be done based on the return code or exception thrown by SP back to MB.
The compute node trasaction property being set to "Automatic" can do a Rollback only if MB is excecuting SQL statements on DB directly(at the flow level). But in this case SP is executing the SQL queries on DB.
I can throw exception in flow based on the return code from SP.
But my question is, can we rollback the operations on DB done by SP on DB through MB by throwing any exceptions and not completing the flow???
I guess this is not possible in MB but just checking out if someone has some idea on how to achieve this in MB layer if possible?
Thanks in Advance |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue Jan 31, 2012 10:37 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Is the Stored Proc written to provide transactionality?
You can wrap a transactional wrapper around the meat of the SP operations and handle it properly and then return an appropriate error to Broker
There is more to transactionality than just configuring broker.
How you write the SP to give you this functionality will be different for the different DB types.
Speak to your DB Developers. They 'should' know how to write an SP properly.
For example, http://www.4guysfromrolla.com/webtech/080305-1.shtml shows how you can do this with SQL Server
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| jlaisbett |
| Posted: Tue Jan 31, 2012 11:09 pm Post subject: |
|
|
Apprentice
Joined: 27 Nov 2009
Posts: 39
|
In most cases message broker can roll back work done by a stored procedure when using Automatic transaction mode by throwing exceptions appropriately, that functionality is not restricted to direct SQL calls.
We do this and it works perfectly fine although I guess it might depend to a degree how the stored procedures actually work and any transaction management they do themselves. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Feb 01, 2012 6:20 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| But my question is, can we rollback the operations on DB done by SP on DB through MB by throwing any exceptions and not completing the flow??? |
Yes you can, as long as the SP does not try to handle the transaction itself (by commiting or rolling back). Essentially if a flow "ends" with an exception it will rollback any DB work.
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 |
|
|
| chetu777 |
| Posted: Wed Feb 01, 2012 8:14 pm Post subject: |
|
|
Acolyte
Joined: 07 Sep 2009
Posts: 59
|
Thank you all for the wonderfull feedback.
I will be able to test the scenarios tommorrow and also I will enquire DBA guys about the trsaction management if done by SP itself.
After looking into the replies above. I am confident of this testing is going to be successfull even if SP is not covering the transaction management also. Will keep you people updated. |
|
| Back to top |
|
|
|
|
