I’m using an adapter service (jdbc in autocommit mode) and I would have a question :
I’m calling a first stored proc adapter which calls an “Insert returning” clause coded in Oracle PL/SQL.
When I test my service my new ID is well returned.
This adapter is called from a main service flow SF1.
Now I have to make an update of the last inserted row in my table (clause “where” using the later Id returned).
So I’ve created a 2nd service adapter based on UpdateSql.
Does this update adapter need to be executed from another service flow, or it can be called from SF1?
I suspect the 2 adapters can’t be called from the same service flow because of the implicit transaction.
So my question is :
When an auto commit transaction is applied ? at the end of a main service flow or just after the calling of the adapter ?
Testing it in a same main service flow, I can observe the update does not work but does not throw any exception : I suspect the new ID is not found in the table as if the transaction had never been applied after the insert treatment.
My JDBC Adapter is set to NO_TRANSACTION.
So my question was oriented to use adapters without using transaction (but I’m very aware that use transaction is better for such a case).
For the NO_TRANSACTION mode, the only information I’ve found in the Adapter guide is :
“NO_TRANSACTION The connection provides no transaction control
over the operations being performed. That is, the connection automatically commits (Auto Commit)
all operations.”
I didn’t find in the documentation, if the autocommit is applied immediately out of the Adapter (when the adapter returns), so the table can be in serted or updated immediately after, or if the autocommit applied at the end of the main service flow.
1/
call Adapter Insert TABLE_A (ID returned = 15)
call Adapter Update TABLE_A where ID = 15
=> The INSERT is OK but the UPDATE does not work : the row just inserted is probably locked or unvailable for the update
(no error or exception throwed)
In debug mode we can see the ID is well returned by the Insert Adapter.
2/
call Adapter Insert TABLE_A (ID returned = 16)
call Adapter Update TABLE_A where is = 15
=> The INSERT is OK and the update works : the row previously inserted (in 1/) is available and can be used for the update
I was using an INSERT…RETURNING clause.
In this case the EXECUTE IMMEDIATE is apparently mandatory to make webMethods abble to see the just inserted row.
So now all is OK.
(I’ve tried a simple INSERT clause, and it works fine too, but in my case I needed RETURNING clause)