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 CATCHI 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 :
-
- 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)
-
- 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.