THY02K
March 10th, 2005, 07:44 PM
Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:
DECLARE @ValidationCode INTEGER
BEGIN TRAN
SET @ValidationCode = 0
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))
... do other stuff...
COMMIT TRAN
CREATE PROCEDURE sp_CreateSupplier(
@supplier_no INTEGER,
@type VARCHAR(8),
@group_no INTEGER = NULL,
@short_name VARCHAR(50) = NULL,
@full_name VARCHAR(100) = NULL,
@address VARCHAR(100) = NULL,
@country_code VARCHAR(10) = NULL,
@status VARCHAR(8) = NULL,
@createBy INTEGER,
@ownerGroup INTEGER,
@ValidationCode INTEGER OUTPUT
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- STEP 1: Validation
SET @ValidationCode = 0
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)
BEGIN
SET @ValidationCode = -10
ROLLBACK TRANSACTION
RETURN
END
...
COMMIT TRAN
END
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier
Dim sql As String = "sp_CreateSupplier"
Dim cmd As IDbCommand
Dim rdr As IDataReader
Dim param As IDataParameter
Dim factory As IDbProviderFactory
Dim validationCode As Int32 = 0
Try
If _conn.State <> ConnectionState.Open Then
_conn.Open()
End If
factory = apputil.GlobalVariables.ProviderFactory
cmd = factory.CreateCommand(sql, Conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Transaction = tran
...
rdr = cmd.ExecuteReader()
...
Catch
...
Finally
...
End Try
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
vconn = vFactory.CreateConnection(...)
tran = vconn.BeginTransaction(...serializable...)
SupplierBean.Create(..., tran)
AnotherBean.Update(..., tran)
tran.Commit()
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!
DECLARE @ValidationCode INTEGER
BEGIN TRAN
SET @ValidationCode = 0
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))
... do other stuff...
COMMIT TRAN
CREATE PROCEDURE sp_CreateSupplier(
@supplier_no INTEGER,
@type VARCHAR(8),
@group_no INTEGER = NULL,
@short_name VARCHAR(50) = NULL,
@full_name VARCHAR(100) = NULL,
@address VARCHAR(100) = NULL,
@country_code VARCHAR(10) = NULL,
@status VARCHAR(8) = NULL,
@createBy INTEGER,
@ownerGroup INTEGER,
@ValidationCode INTEGER OUTPUT
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- STEP 1: Validation
SET @ValidationCode = 0
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)
BEGIN
SET @ValidationCode = -10
ROLLBACK TRANSACTION
RETURN
END
...
COMMIT TRAN
END
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier
Dim sql As String = "sp_CreateSupplier"
Dim cmd As IDbCommand
Dim rdr As IDataReader
Dim param As IDataParameter
Dim factory As IDbProviderFactory
Dim validationCode As Int32 = 0
Try
If _conn.State <> ConnectionState.Open Then
_conn.Open()
End If
factory = apputil.GlobalVariables.ProviderFactory
cmd = factory.CreateCommand(sql, Conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Transaction = tran
...
rdr = cmd.ExecuteReader()
...
Catch
...
Finally
...
End Try
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
vconn = vFactory.CreateConnection(...)
tran = vconn.BeginTransaction(...serializable...)
SupplierBean.Create(..., tran)
AnotherBean.Update(..., tran)
tran.Commit()
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!