Hi all, I’m now to these forums. I’m trying to get the JDBC adapter to work in a transactional manner for specific use case. However, I’m having the following error:

[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service Transactions:adpsvcGetTransBuffer.
[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource JDBC_Connections.PMT:MyConn_Local_Transaction. The resource is already being used in a parent transaction.

I’ve used transactions with JDBC adapter before and they work very well for the same case:

art.startTransaction
sequence(success)
sequence(failure)
db_action_1
db_action_2
…
db_action_n
art.commitTransaction
sequence(done)
art.rollbackTransaction

In the case that is causing problems, I have the transactions in a loop (looping over a document). I don’t want all or nothing transactional operations for the entire document, only on a per document record. Example:

art.startTransaction
sequence(success)
sequence(failure)
db_action_get_docHeaderRecords(maps to document/records)
Loop over /document/records
art.startTransaction
sequence(success)
sequence(failure)
do_dbactionForThisRecord1
do_dbactionForThisRecord2
do_dbactionForThisRecord3
art.commitTransaction
sequence(done)
art.rollbackTransaction (only for this record)

art.commitTransaction

sequence(done)
art.rollbackTransaction

Can anyone help? Thanks in advance.

Hi hpcd,
Pls. search this forum and you will get many threads discussing these problems and the solutions.
If you still don’t find the solution, pls. let us know and we will help you further.

Bhawesh.

Hi Bhawesh. thanks for replying so quickly and your suggestion. I did check through the posts prior to creating my own, after I found that the are subtle differences between my case and some of what I’ve seen. I’ve used wmDB, with trandactions in the same type of use-cases, without incident. So, I was rather surprised with the problems I got with transactions using JDBC adapter with this use-case. I’ve also consulted tje JDBC user guide.

UPDATE:
I found that if I change db_action_get_docHeaderRecords(maps to document/records) to use NO_TRANSACTION, it works; but there are two a couple of incidents where it generated an error. I then tried setting the art.transactiontimeout, at the beginning, which does seem to improve things–but you get a server debug message:

10025]2006-06-09 12:57:07 PDT [ART.0114.1301W] Adapter Runtime (Transaction): Not setting transaction timeout to 2 seconds. Transaction is already in progress.

but no error.

I want to avoid a hit-and-miss approach to fixing the problem. Are there any best practises with regard to the issues I’ve been having? Anyone?

Hi hpcd,
See if this works:

sequence(success)
–>sequence(failure)
---->db_action_get_docHeaderRecords(Using NO_TRANSACTION Connection)
---->loop over /document/records
------>art.startTransaction
------>sequence(success)
--------->sequence(failure)
----------->do_dbaction1(Using LOCAL_TRANSACTION Connection)
----------->do_dbaction2(Using LOCAL_TRANSACTION Connection)
----------->do_dbaction3(Using LOCAL_TRANSACTION Connection)
----------->art.commitTransaction(only for this record)
--------->sequence(done)
----------->art.rollbackTransaction(only for this record)
–>sequence(done)
---->handleException.

• Bhawesh

hpcd,

WmART Fix 4 corrects a problem where a service’s connection state is not properly cleaned up upon completion of the invoking service. From looking at the error you cited, I believe this fix may help you. Here is the text as included in the readme for WmART Fix 5 (which includes WmART Fix 4):

1-10R16V (IS_ART_6-5_Fix4)
After upgrading from Integration Server 6.0.1 to 6.5, migrated
WmART adapter services terminate and the following error is
thrown:

“[ART.117.4011] Adapter Runtime (Adapter Service): Unable to
connect to resource <resource_name>. The resource is already
being used in a parent transaction”.

This error occurs because Integration Server 6.5 does not
properly clean up a service’s connection state (for the invoking
thread) when the root level service ends. If any service later
is assigned the same thread having reference to the old
ConnectionState (and the references of the cached connection of
the previous ConnectionState), it appears that the connection is
still in use by the previous service and the “resource already
in use” error is thrown.

This issue has been resolved.

Of course, fixes can be requested from the webMethods Advantage site. Be careful when installing fixes; they are not fully regression tested by webMethods.

Getting this exception when running this select adapter service.

[ART.117.4011] Adapter Runtime (Adapter Service): Unable to connect to resource
The resource is already being used in a
parent transaction.
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceE
xception.java:125)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceE
xception.java:116)
at com.wm.pkg.art.error.DetailedServiceException.create(DetailedServiceE
xception.java:103)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode
.java:388)
at com.wm.pkg.art.ns.ARTNSService.baseInvoke(ARTNSService.java:54)
at com.wm.app.b2b.server.invoke.InvokeManager.process(InvokeManager.java
:630)
at com.wm.app.b2b.server.util.tspace.ReservationProcessor.process(Reserv
ationProcessor.java:46)

Please search the forums for “resource is already being used in a
parent transaction.” You may find what you’re looking for.

Hi… Try using a wrapper svc around your adapter svc. It worked for me