SQLite transaction

  1. 5 years ago

    Marc

    13 Mar 2019 User since 2018
    Edited 5 years ago by Marc

    A method for performing a transaction on a SQLite database would be a nice to have addition.

    It could for example be a simple overload of the "execute" method that also accepts an array of SQL commands instead of a single SQL string.

    NB: If the error response could also return an index to the SQL command in the array, that caused the error, then that would also be of much help.

  2. marco

    13 Mar 2019 Administrator User since 2016

    Hi @Marc that would be an easy method to add to your database:

    func executeList(r) {
          var index = 0;
          for (var sql in r) {
          	if (self.execute(sql) == false) return index;
          	index += 1;
          }
          return true
    }
  3. Marc

    13 Mar 2019 User since 2018

    @marco Hi @Marc that would be an easy method to add to your database:

    func executeList(r) { var index = 0; for (var sql in r) { if (self.execute(sql) == false) return index; index += 1; } return true }

    But that doesn't do a rollback? What I'm looking for is an implementation of SQLite's transaction:

    https://www.sqlite.org/lang_transaction.html

  4. marco

    13 Mar 2019 Administrator User since 2016

    Here you go an updated version with transactions support:

    func executeList(r) {
          var index = 0;
          self.execute("BEGIN TRANSACTION;")
          for (var sql in r) {
          	if (self.execute(sql) == false) {
                 self.execute("ROLLBACK;")
                 return index;
            }
          	index += 1;
          }
          self.execute("COMMIT;")
          return true
    }
  5. Marc

    13 Mar 2019 User since 2018

    @marco Here you go an updated version with transactions support:

    func executeList(r) { var index = 0; self.execute("BEGIN TRANSACTION;") for (var sql in r) { if (self.execute(sql) == false) { self.execute("ROLLBACK;") return index; } index += 1; } self.execute("COMMIT;") return true }

    I wonder if this would work in an async way with multiple connections performing transactions to the same database. I'm going to test that. Thanks so far.

  6. marco

    13 Mar 2019 Administrator User since 2016

    This is all synchronous code (for the async execute you would need to pass the onSuccess/onError closures). Please note that TRANSACTION code is usually pretty fast so most of the time it is safe to execute it in a sync way.

or Sign Up to reply!