I have used following drivers to handle Commit and Rollback but so far could get any acheivement,
“com.wm.dd.jdbcx.sqlserver.SQLServerDataSource” Built in driver in WM
“com.microsoft.sqlserver.jdbc.SQLServerDataSource” download from Microsoft for SQL2005
Kindly also find below JDBC Connection parameters
Transaction Type LOCAL_TRANSACTION
DataSource Class com.microsoft.sqlserver.jdbc.SQLServerDataSource serverName localhost user Administrator password ****** databaseName Fin
portNumber 1433
I also have tried using,
Other Properties selectMethod=cursor
Also used WMArt Package to controll commit and Rollback as per documentation. Whenever there is any error while inserting or updating database, execution goes to catch block and passes through Rollback without giving any error. I have used get last error after Rollback and get following message “[ART.114.306] Adapter Runtime (Transaction): Unable to rollback transaction.
null”
I tried same WM7.1 and ART package to control the transactions exclusively but getting same message,
“[ART.114.306] Adapter Runtime (Transaction): Unable to rollback transaction.
null”
I am using getLastError to get the latest error and getting “Unable to Rollback Transaction” even after updating WM6.5 service pack to SP3 and also applied latest ART fix.
Looks like you are rolling back the transaction explicitly using start, commit and rollback services. Is your startTransaction service in Try block or initialized before Main. Because whenever your process falls in Catch block, everything initialezed in Try block drops out so if you initialize startTransaction before Main block, transactionName stay in pipeline to be used for rollbackTransaction.
I am doing same as you have explained previously. I have initialized start transaction as first step and then have try catch block.
I will really appreciate if you can send me small package only with explicit working example of insert or update transaction.
it’s been more than a month, I am trying to resolve this issue.
How about you send us the screen shots with pipeline variables and mappings of your flow service maybe we can find something in there or just send a package with sample flow service indentical to your original one.
I attached MyTest.zip package which contains only one folder called adapter. THis adapter folder has connection and two services, one batch insert and other try catch service uses explicit transactions.
You can create table by using below sql script, CREATE TABLE [dbo].[ORDER_DETAILS](
[ORDER_ID] char COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TRANSACTION_ID] char COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SKU] char COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QUANTITY] char COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] GO
When you run Try Catch service, purposely do not assign any value to Order_id in a second row and check first row will be inserted into DB which could not be the case. You will also get error trace "Unable to rollback " in the pipeline.
Sorry couldn’t get back earlier, the only thing I could find in your package was the mapping part. Try mapping the transactionName field from startTransactionOutput to transactionName of rollbackTransactionInput instead of the whole document. And do the same thing for commitTransaction too.
I have done trying mapping transactionName field from startTransactionOutput to transactionName of rollbackTransactionInput instead of the whole document but the result is same “Unable to Rollback”.
Check the “insertCatch_1” srevice that from the attached package … It is working fine …
Points to remember ::
You cannot debug (trace) the code when using startTransaction , commitTransaction and rollbackTransaction … If you do , its throws same error as you mentioned …
I have commented in one of the new flowsteps i added in the service that…
The flow should be as follows when you are testing rollback …
try
{
–Insert to Database
–Some Error before Commiting to Database
–COMMIT
}
catch
{
– getLastError
– rollback
}
So that the values inserted will be rolledback if ther is any error before commiting …After commit, there is no way of rolling them back … and this is the scenario you would want in real time…
Hope its clear… and shoot me back a message if you have any doubts …
Faisal sorry I can’t replcate the problem on my side since I don’t have a database on my machine and can’t ask my DBA’s to create this sample table in our office environment but I have used the same scenario before with the same way and it worked with me. Did you try opening the ticket with webMethods ?