DB.Commit

Databases
1

Hey everybody, I got a question here…

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…)

What do you guys, think ?

2

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.

3

Pass in an optional flag

InsertNewRecordInDB(RecArgs() as String, Commit as Boolean = True)

Then you can use the method without thinking about the commit. And where you have batch code, pass in False and do your own commit at the end.

4

I was under the impression that DatabaseRecord made a PreparedStatement in the background.

5

FYI:

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

Note the comments about non-PSQL databases.

6

I’m sure it does, but we take advantage of “RETURNING” to get the id in PSQL. I also like the control. :slight_smile: