Altering a table through stored procedure : Transaction not

, ,
1

Hi,
I m facing the issue in altering a table through stored procedure. I m invoking a stored procedure through the connection having Transaction Type as LOCAL_TRANSACTION.
It is returning correct parameters what I m expecting, but the records which should get created in the table on execution of this proc…are not in the table. We dont have any Commit statement in stored proc & we cant do it either.
I tried to use explicit commit services(startTransaction & commitTransaction) also but no luck.

Thanks,
Manish

2

Manish,

If you are using implicit transactions on a LOCAL_TRANSACTION connection, then the database commit actually takes place when the top-level service completes successfully. If the top-level service exits with an exception (possibly propagated from a lower level service), then a rollback takes place.
So, things to check are:

  • Does the top-level service complete successfully?
  • Are you trying to verify whether your stored proc has run before the top-level service has completed?

If you don’t like the implicit transaction behaviour, then use explicit transactions. You can find a good explanation in the Appendix of the WmJDBC Adapter user guide.

Also be aware that you should avoid using implicit transactions within process models because you lose control over the transaction boundaries due to the intervening PRT layer.

regards,

Jonathan Heywood
webMethods Professional Services

3

Jonathan,
My problem is very simple…
I am calling a Stored Proc (SP) from wM. That SP is altering one table in the database (it doesn’t contain any Commit statement in its end). This Proc is also returning two fields one is status Message & other is status code. And I am getting the values for these fields in return what I am expectiong.

But the problem is when I checks the table , I dont see any alteration.

And this is my guess , that the root cause of this problem is the absence of Commit in the proc because when I tried to execute this SP from SQL+, I have to give explicit Commit command to see the changes in the table.

I have already tried all possible options like…All Transaction Types, Explicit transaction Service like startTransaction, commitTransaction etc. but no luck.

Now I want to ask what can I do to see the changes in the table when I execute the SP from wM?

Thanks,