I am not sure what is the best approach to make commits to the DB.
For example, I have a method that is InsertNewRecordInDB(RecArgs() as string)
So If I commit inside that method, then I will not be able to use it to batch insert records, because every insert will be commited and I want to commit after the whole transaction (all inserts) are done.
But If I don’t commit inside the method, that I must always remember to commit after calling the method, which is not very safe (Considering that method might be called from another method, and so on…)
It’s up to the caller to handle the transaction because a helper method like that never knows if it’s part of a broader transaction. FYI, we use prepared statements to insert and do not rely on DatabaseRecord, etc.
You could always issue an optional parameter to force a commit. You can also test to see if you’re already in a transaction and only issue a commit if you’re not.
Public Function IsInTransaction(Extends db As Database) as Boolean
//
// Since Xojo doesn't yet return notices,
// we will determine if were are in a transaction by
// attempting to start a savepoint
//
// Note: SQLite and MySQL will always return true
//
const kSavepointLabel = "ThisLabelWillNeverBeUsedElsewhere"
db.SQLExecute "SAVEPOINT " + kSavepointLabel
if db.Error then
if db isa PostgreSQLDatabase and db.ErrorCode = 3 then
return false
else
//
// Have to test with other databases
//
dim err as new RuntimeException
err.Message = "Unknown database error code " + str(db.ErrorCode) + ": " + db.ErrorMessage
raise err
end if
else
db.SQLExecute "ROLLBACK TO SAVEPOINT " + kSavepointLabel
return not db.Error
end if
End Function