A transaction issue

[NeoLSN]

I just found I can't do a transaction.

LOG: 'BEGIN', []
LOG: 'INSERT OR IGNORE INTO device_model (device_sn) VALUES (?);', ['abcdefg']
LOG: 'COMMIT', []
LOG: 'executeSql error -> ', 'cannot commit - no transaction is active'

My table.

CREATE TABLE IF NOT EXISTS device_model(device_sn TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL, status TEXT, properties TEXT);

SqliteObject class

export class SQLiteObject {
  private _objectInstance;

  constructor() {
    this._objectInstance = new SQL.Database();
  }

  public executeSql(statement: string, params: any): Promise<any> {

    return new Promise((resolve, reject) => {
      try {
        console.log(statement, params);
        const st = this._objectInstance.prepare(statement, params);
        const rows: Array<any> = [];

        while (st.step()) {
          let row = st.getAsObject();
          rows.push(row);
        }
        st.free();
        const payload = {
          rows: {
            item: function (i) {
              return rows[i];
            },
            length: rows.length
          },
          rowsAffected: this._objectInstance.getRowsModified() || 0,
          insertId: this._objectInstance.insertId || void 0
        };

        resolve(payload);
      } catch (e) {
        console.log('executeSql error -> ', e.message);
        reject(e);
      }
    });
  }

}

Did I do something wrong?

[j005u]

I'm having the same issue. It appeared after I applied a setTimeout(function() { callback(myres); }, 0); to my callbacks to work around a control flow issue in some existing code.

It looks to me that if I don't do them as blocking operations in the same Javascript "runloop" transactions break.

The app I'm working in is quite extensive and I can't rule out other causes but I'm pretty sure I'm not reopening the database or anything like that. Logs show the two queries (BEGIN and COMMIT in the minimal case) running subsequently with no funny business in between.

EDIT: In my case the answer appears to be not to db.export() in the middle of the transaction. Fair enough.

[NeoLSN]

Right now, I modify my code like below,

export const READ_ONLY_REGEX = /^(\s|;)*(?:alter|create|delete|drop|insert|reindex|replace|update)/i;
export const TRANSACTION_REGEX = /^(\s|;)*(?:begin|end|commit|rollback)/i;

export class SQLiteObject {
  private _objectInstance;

  constructor() {
    this._objectInstance = new SQL.Database();
  }

  public executeSql(statement: string, params: any): Promise<any> {
    console.log(statement, params);
    return new Promise((resolve, reject) => {
      try {
        if (TRANSACTION_REGEX.test(statement)) {
          this._objectInstance.exec(statement);
          resolve();
        } else if (READ_ONLY_REGEX.test(statement)) {
          this._objectInstance.run(statement, params);
          resolve();
        } else {
          const st = this._objectInstance.prepare(statement, params);
          const rows: Array<any> = [];

          while (st.step()) {
            let row = st.getAsObject();
            rows.push(row);
          }
          st.free();
          const payload = {
            rows: {
              item: function (i) {
                return rows[i];
              },
              length: rows.length
            },
            rowsAffected: this._objectInstance.getRowsModified() || 0,
            insertId: this._objectInstance.insertId || void 0
          };

          resolve(payload);
        }
      } catch (e) {
        console.log('query error -> ', e.message);
        reject(e);
      }
    });
  }
}

It worked. But I still wondering why old way not work.