MSSQL Transaction loop (commit/rollback) with subroutines

[ddprlewis]

I am currently building an application and for simplicity there are several updates that have lent themselves to being called from within other functions so that I don't have to duplicate the code.

However, I am now in need of doing a rollback/commit scenario. I thought if I passed the connection to the sub-routines that I would then be able to control the transaction loop within the primary function. So, it is something like this:

cn.Open(<some connection>)

cn.Execute("select statements")

if cn.BeginTransaction()
    lcResponse = transactionprocessing(cn, parameters)
    if len(lcResponse) <> 0
        cn.RollBackTransaction()
    else
       'write some more stuff with inserts and updates

       cn.CommitTransaction()
    end if
end if

It all appears to work fine, but even if there is a problem in the "transactionprocessing" sub routine (and that routine does not create a new connection, but uses the existing one that is passed), the records are still committed and the rollback is just ignored. So, I am wondering if:
1. There is something else I am missing setting
2. If it is not possible to have a transaction processing loop cross functions
3. There is a better way to handle this.

It took quite a bit of finagling to get all of the queries above the write backs so that I could do a transaction loop, but it's not working :(.

Any ideas a appreciated.

[ddprlewis]

Re: MSSQL Transaction loop (commit/rollback) with subroutines

For anyone who is searching, the only solution I could come up with was to use SQL Server commands. So, I do not use the connection commands. It ends up looking like this.

cn.Execute("BEGIN TRANSACTION")
llCont = cn.Execute(<update statements>)
if llCont
  llCont = transactioncall(cn, parameters)
end if

if llCont
  cn.Execute("COMMIT")
else
  cn.Execute("ROLLBACK")
end if

This functioned properly in a complex commit/rollback scenario and also worked with passing the connection object to the sub-routine. I don't know why the internal cn.BeginTransaction doesn't work. My only thought is that was designed for use with DBF files only and is not functionally used when connecting to other back ends. Anyway, I will continue to use the SQL Server calls moving forward.