Using the JDBC 6.0.1 adapter to create a transaction I use
pub.art.startTransaction
then use
pub.art.commitTransaction or rollbackTransaction to complete the transaction.
Everything seems to work fine unless I try to step through the code. Stepping through the code causes it to fail on the startTransaction step with the following error:
An error occurred while tracing.
com.wm.app.b2b.client.ns.NSRuntimeException
I am connecting to Oracle 8i, using LOCAL_TRANSACTION.
I have seen that Exception, and if I recall, it may be been when I ran with a corrupted pipeline input file (from pub.flow:restorePipelineFromFile). But then when I sent another request through our driver and saved the pipeline, that seemed to have cleared up this Exception.
I do have a related question though. We are using the wM6.0.1 WmDB package against Oracle9i to handle transactional requests. The basic design looks like this:
SEQUENCE (parent of try/catch - exit on success)
SEQUENCE (try block - exit on failure)
pub.db:startTransaction
pub.db:execSQL
pub.db:commit
pub.db:close
SEQUENCE (catch block - exit on done)
pub.flow:getLastError
pub.db.rollback
pub.db.close
What I’m seeing is that execSQL in the try block uses a $dbAlias to open a database handle to Oracle. Then if there is an error, I jump down into the catch block. Here, the rollback service no longer has acdess to the same database handle. Therefore, it can’t rollback the SQL statement which caused the Exception.
Has anyone successfully done this type of logic? Thanks.
did you dropped $dbConnection object in flow some where else.
I noticed in your try/catch block you are not calling clearTransaction
service to clear transactions.We are using these services with out
dropping connection objects upto end of catch block.
Hi Srinik - I am not dropping $dbConnection anywhere in the flow. Also, I’m not calling pub.db:clearTransaction after the commit (in try block) and rollback (in catch block) because this is an internal wM service (i.e., it doesn’t talk to Oracle). pub.db:close is the one that closes my database connection. However, after reading more about clearTransaction, I agree with you and am going to add this after both the commit and rollback.
We have, however, discovered an interesting phenomenon after recently tuning some IS extended settings and database alias parameters for optimal performance purposes. We noticed that our service now opens one database connection in the try block and when it hits an exception, a new database connection is opened in the catch block (for pub.db:rollback).
And because a second connection object is opened in the catch block, the reference to the first database handle is lost. Therefore, the rollback fails because it’s rolling back the wrong transaction. Anyway, we have identified that this behavior is due to one of the settings we recently changed … we just don’t know yet which setting(s) it is
Rajesh,
Define your startTransaction before your try block. So you will have reference of your transaction name in catch block also. and you will be fine
Ashesh - That’s a good suggestion based on the code snippet I described above. However, the entire service does both WRITEs and READs from the database. Because of the latter case, I didn’t want to treat the whole service like a transaction.
You’re right though, it would be more elegant to call startTransaction and clearTransaction once each.
Monday 10.59 posting (I saw it today)Exact thing what you mentioned in your posting.We discovered several months ago in our
Company Integrations(Utility company in NC).This is a bug.
Because of this Database connection always opens
locking problem etc…To solvve this problem.Before main sequence
We called Connect service.This opens database connection.Because
of some reasons database transaction failed(Failurecondition) immeadiately we called clearTransaction,close database connection.
This seems to be catch block for connect service before we executed
main flow(Try/Catch).In main flow we are doing database manipulations
like insert,Dalete,update,SP etc…Using database connection object
we called before Our main flow(Try/catch block).
In main flow Catch block again we called
clearTransaction.close etc…Similarly in we called close in
main flow also.So we called close three times.Earlier we used
JDBC adapter.Some times it is difficult to us debugging exceptions
it thrown.That time we used webmethods6.0 fcs version(it is oct’02 issue).
Hi,
Using the JDBC 6.0.1 adapter to create a transaction I use
pub.art.startTransaction
then use
pub.art.commitTransaction or rollbackTransaction to complete the transaction.
SEQ (exit on failure)
pub.art.startTransaction
updateHeadertable
updatedetailtable
pub.art.commitTransaction
SEQ (exit on done)
pub.art.rollbackTransaction
I am connecting to Oracle using LOCAL_TRANSACTION.
when i try to run the service iam getting the following error
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to rollback transaction. Transaction state:Transaction is rolled back .
I am very interested to hear if you ever got to solve your problem. We are encountering the same error messages when connecting to a SQL Server database.
Dear Carl,
till now we were not able to solve that, mean time i was into some other assignment. infact today i came to check whether any respose for my request. if anyone encountered and solved this problem, please lets know. mean time iam also lly
Here is a response compiled by wM Technical Support. It did make sense in my case and I do not see the error anymore.
1.The most likely cause of this is that there is a flow call to
pub.art.transaction:startTransaction without a corresponding call to
pub.art.transaction:commitTransaction. As you know, there needs to be a
commit or rollback for each start. In fact, if you create a flow service
with just a call to startTransaction and run it, you will see this error. So
check your code to make sure your flow has all corresponding start/commit
calls.
Another cause could be that the transaction terminates without any call
to rollback Transaction. This can happen when an error occurs right before
the commit, but the rollback is missed from the error handler and the
transaction is left in an open state. So verify that rollbackTransaction is
always called in case of exceptions. The key is that the thread must not
terminate with an open transaction.
Another cause is use of the “trace” feature in developer to execute a
flow that calls an adapter service (regardless of the transactional setting
for the connection being used by that adapter service). The trace facility
should not be used when there is a transactional context on the thread
because it does not guarantee that the same thread is used for each flow
step.
