Where to place CommitTransaction in API 2.0

Databases
1

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?

[code]Try
db.ExecuteSQL(tempSQL)
db.CommitTransaction 'put commit here?

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

Finally
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]

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

not always - finally is not ALWAYS run :slight_smile:

3

[quote=483881:@Norman Palardy]try
update database
commit
catch
// 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.

4

Thank you

5

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

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

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

theDB.CommitTransaction

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

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

6

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