Hello Everyone.

I am trying to create a integration service with the following flow:

SELECT count() without limit
Gets the “SELECT count()” value and divide it by 1,000 to get the “RepeatCount” using “divideInts”
REPEAT with Count == “%RepeatCount%”
startTransaction
try
SELECT 1,000 values WHERE integrated = ‘Nope’
Create a doc with 1,000 documents in a document list
Publishes the doc
Update 1,000 values SET integrated = ‘Processing’
commitTransaction
catch
roolbackTransaction
REPEAT END

But when I try to run the service I’m getting the following errors:

“Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.”

“[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections…”

Error while closing transactions at service completion

I am using a JDBC adapter with “Transaction Type” value set to “LOCAL_TRANSACTION”.

I don’t know why this is not working. What should I do to solve this issue?

Thank you

Hi Renan,

This error indicates that you’re calling multiple Adapter services which are from multiple JDBC Adapter connections configuring with “LOCAL_TRANSACTION” which are not allowed/recommended.

If your flow contains adapter services that use more than one
LOCAL_TRANSACTION connection, you must use explicit transactions.

For implicit transaction:

"A single transaction context can contain any number of XA_TRANSACTION connections but no more than one LOCAL_TRANSACTION connection.

Solution:

If you want to call multiple adapter services from multiple JDBC Adapter connection, you’d need to do the following modification to address this issue:

Change the JDBC adapter connection to use with “XA_Transaction” and not “LOCAL_TRANSACTION”.

If you are using one DB connection then use “No_transaction”(If you don’t mind to implicit commit after every insert).

If you still face the issue ,share the logs.

Thanks,
Santhosh Sunkari.

Try this snippet:

startTransaction
Main
try
SELECT count() without limit
Gets the "SELECT count()" value and divide it by 1,000 to get the "RepeatCount" using "divideInts"
REPEAT with Count == "%RepeatCount%"
SELECT 1,000 values WHERE integrated = 'Nope'
Create a doc with 1,000 documents in a document list
Publishes the doc
Update 1,000 values SET integrated = 'Processing'
commitTransaction
catch
rollbackTransaction

Hi Mr. Mahesh.

I tried as you suggested, but now I’m having the following message:

“ART.114.302] Adapter Runtime (Transaction): Error while committing transaction 917715. The transaction in progress is 917716.”

Thank you

HI Renan,

as per your flow design, only one transaction ID suppose to create. while committing it should commit with that transaction id.

can you validate your flow again.

• please go with local transaction

  start transaction
  repeat -step
  – select operation
  – update operation
  commit transaction

HI Renan,

as per your flow design, only one transaction ID suppose to create. while committing it should commit with that transaction id.

can you validate your flow again.

• please go with local transaction
  try
  start transaction
  repeat -step
  – select operation
  – update operation
  repeat -end
  commit transaction
  rollback -catch

let me know if any other error

Hi,

please try if this works when using a second connection with NO_TRANSACTION type for the select and only use LOCAL_TRANSACTION for the Update.

You can update the connection in the service directly without having to delete and recreate it by using helper service from WmART package. Check for the setAdapterServiceNodeConnection service.
Reload the package afterwards to get the service updated in server memory

Regards,
Holger

I second Holger , please try with the above mentioned approach.

Hi,

I want to know what you are publishing and how, is it JMS publish ? if so check the transaction type in JMS connection alias. Ensure all your connections have common transaction types. Else use XA transaction time.

Regards,
Jacob

Hi,

can you provide some more details how the update is implemented?

Is it looping over 1,000 entries or is it just a batch update on all 1,000 entries at once?
This might make a difference on how the update is dealing with transaction handling.

Regards,
Holger