Where to place CommitTransaction in API 2.0

I am updating my apps for API 2.0 and previously I used:

'update database If db.Error then 'display error Else db.Commit End if

Now I have moved to API 2.0 and am not supposed to use db.Error, but instead an Exception, where should I place the db.CommitTransaction?

  1. After the Try…End Try means it is committed even if there is an error
  2. After a Finally means it is committed even if there is an error
  3. After db.Error = False means I am not fully API 2.0 compliant
  4. The Error.Message = “” or Error.ErrorNumber = 0 are not accessible outside the Try…End Try
  5. After the db.ExecuteSQL?

db.CommitTransaction 'put commit here?

Catch Error
doDisplayError(Error.Message, CurrentMethodName, Error.ErrorNumber)

db.CommitTransaction 'put commit here?
End Try

If Error.Message = “” or Error.ErrorNumber = 0 or Not db.Error Then 'Error is out of scope and db.Error is not API 2.0
db.CommitTransaction 'or put commit here?
End If[/code]

    update database
catch <whatever error>
  // handle error
end try

not always - finally is not ALWAYS run :slight_smile:

[quote=483881:@Norman Palardy]try
update database
// handle error
end try[/quote]
I would generally add to Norman’s response

try update database commit catch <whatever error> // handle error rollback end try
And remember that, unlike API 1.x, API 2.0 will complain if you try to Commit or Rollback without having started a Transaction.

Thank you

One other minor issue with API 1.x is that each hit on the database would generally be in its own If…Then block and it was easy to track the errors. In API 2.0, hits on the database can be packed into a single Try/Catch block. That means that the error being caught can be at any of them and it can be hard to distinguish exactly which of multiple calls of the same type actually had the error. So I generally use a local variable and adjust its value for each call to the database so I can report that value.

[code]Var ErrLoc As String
If theDB.Connect() then

ErrLoc = “Unable to alter Writers table”
theDB.ExecuteSQL (“ALTER TABLE Writers ADD WebAddr varchar”)

ErrLoc = "Unable to alter Preferences table"
theDB.ExecuteSQL ("UPDATE Preferences SET DBVersion = 5")


Catch err As DatabaseException
MessageBox (CurrentMethodName, Str(err.ErrorNumber), err.Message, ErrLoc)
End Try

End If[/code]
It just makes tracking where the error occurred easier.

as long as things do indeed raise the exceptions as expected
they dont always
there are some bug reports about these issues