pub.art.transaction.commitTransaction : Unable to commit transaction null

, ,
1

Hi,
I am sure this kind of problem was delivery a thousand of time but I have little probleme about this.
I have a adapter connection with LOCAL_TRANSACTION as Transaction Type and I want work with an explicit transaction.

I have this STORED PROCEDURE in SQL (SQL Server with a Managed Service Account as credential with Windows Authentification).


ALTER PROCEDURE [dbo].[proc1]
 @isOpen bit
  AS
    DECLARE @ErrorMessage NVARCHAR(4000)
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT 
	BEGIN TRY

                -- I Let the user choose which kind of transaction he want to manage
		IF @isOpen = 0 BEGIN
			BEGIN TRANSACTION 
		END
		
		
		INSERT INTO dbo.TableTest(TestField) VALUES ('Test')

		IF @isOpen = 0 BEGIN
			COMMIT TRANSACTION
		END
		return 0

	END TRY
	BEGIN CATCH


		SELECT @ErrorMessage = ERROR_MESSAGE(),  
                               @ErrorSeverity = ERROR_SEVERITY(),  
                               @ErrorState = ERROR_STATE() ;

 		IF @isOpen = 0 BEGIN
			ROLLBACK TRANSACTION
		END;
		THROW @ErrorMessage, @ErrorSeverity, @ErrorState
 
	END CATCH

I created this king of code

  • pub.art.transaction:startTransaction (transactioName = test)
  • SEQUENCE (Exit On SUCESS) Try/Catch
    — SEQUENCE (Exit on FAILURE) Try
    ----- myStoredProcedureAdapter (ProcedureName = proc1, return value : Type = VARCHAR, Name = ReturnValue, parameter Input : Type = IsOpen, Type = VARCHAR). (isOpen = 1)
    ----- pub.art.transaction:commitTransaction transactioName = test)
    — SEQUENCE (Exit On DONE) Catch
    ----- pub.flow:getLastError
    ----- pub.art.transaction:rollbackTransaction (transactioName = test)

That seems correct to me but I noticed two problems :

    1. In debug mode, just after the step for stored procedure, when I check database, my record is filled (I expected to be locked in the table when I make a selection in SQL)
    1. After the commit step, I go to the Catch SEQUENCE with the error saying : Unable to commit a transaction null.

So :

  • Is it possible to manage explicit transaction with stored procedures ?
  • If yes what is wrong with my code ?
  • Is it a way that Managed Service Account and Windows Authentification as credentials could be a problem. (I don’t expect that, I use the WmDB package for the same issue, I have other problems but explicit transaction seems to work.

wM 9.10 for me.

Thanks for your answer.

PS : I am surprised that in startTransaction step, we don’t give the name of the Adapter Connection.

2

OK thanks to this discussion :
[url]http://tech.forums.softwareag.com/techjforum/posts/list/47494.page[/url]

I find the problem : it seems that in debug mode, transaction doesn’t work.
I try to do call my proc two times (Violation on primary key in database).

If Transaction does"nt work I should have only one row inserted. If work, I should be no row inserted.
I try it in runtime and the result is I have no row in database.
It seems to work.

I understand why mechanism in debug mode deosn’t work. Probably to not locked the database. But I wonder if it is possible to force the explicit transaction anyway as someone knowing what he does.

3

Hi Vital,

this is a known issue for quite a long time now.

Services with explicit transaction handling cannot be debugged in Developer/Designer.

startTransaction does not require a connection name, as it can span over several different connections (even for different adapter types), i.e. when using XA-Transactions.
You only need to make sure that the transaction name is handled correctly by startTransaction and commitTransaction/rollbackTransaction.

Regards,
Holger