When an autocommit transaction is really applied?

Hi,

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.

Regards

Hi Cedric,

what transaction mode is configured in the Connection. NO_TRANSACTION or LOCAL_TRANSACTION?

See JDBC Adapter Users Guide for further informations about Transaction Handling.

Regards,
Holger

Set JDBC connection to Local Tx

startTx
main
  try
      insert table
      update table
      commitTx
  catch
       getLastError
       rollbackTx

Question for you - Say if an update to the table failed, do you still commit the insert or rollback it?

Hi Holger,

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).

Thank you for your reply.

Regards

M@he$h, I don’t need transaction for my simple need (because no need to rollback anything) :


try
   insert tableA
   
   try 
      flagErrorB = false
      insert tableB
   catch
      flagErrorB = true

  
    if flagErrorB
       update tableA
    else
       // All is ok

catch
   // do nothing
   display error

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.”

Ok, let us know if any issues.

The issue is described in my first message.

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.

I’ve made a simple test :

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

is it normal 1/ fails ?

I did try a simple insert and update on the same table by id. I can see both insert and updates happening, I am on IS 9.12 with JDBC JDBC_9.10_Fix11

Thank you for your testing.
I’m on IS 9.8 - Adapter 9.0 For JDBC 9.0 Fix 17 (the lastest)

Are you testing with Oracle ? (I’m on 10g)

I think I am on 11g. Share your flow screenshots?

After many tests, I’ve just found my issue.

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)

Thank you for your help