DB.Commit

  1. 6 days ago

    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 ?

    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.

  2. Kem T

    Apr 13 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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. Tim H

    Apr 13 Pre-Release Testers Answer Portland, OR USA

    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. Bob K

    Apr 13 Pre-Release Testers, Xojo Pro Kansas City

    @Kem T prepared statements to insert and do not rely on DatabaseRecord, etc.

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

  5. Kem T

    Apr 13 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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. Kem T

    Apr 13 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

    I'm sure it does, but we take advantage of "RETURNING" to get the id in PSQL. I also like the control. :)

or Sign Up to reply!