Ionic 2 - SQLite - SqlStorage - sqlBatch

Ionic Framework ionic-v3
#1

Hi,
I’m using Ionic 2 with the cordova-sqlite-storage plugin.

In the costructor of my Provider I have the instance of the Storage:

this.storage = new Storage(SqlStorage, {name: 'my-sqlite-database'})

I’m able to read/write in the database with the method query:

 this.storage.query(`SELECT * FROM sqlite_master WHERE type = 'table' and name = '${tablename}'`).then(....)

But I am not able to use the method sqlBatch documented here:

 http://ionicframework.com/docs/v2/native/sqlite/

and here:

 https://github.com/litehelpers/Cordova-sqlite-storage

How can I use sqlBatch method?

Thanks in advance,
Daniele

1 Like
#2

Are you running in a device or browser? I don’t think it’s going to work in a browser.

#3

The app is for device, but I’m using the browser for the development.
Is there a way to do multiple queries for time (cross platform: browser + devices)?

Daniele

#4

I use this function:

 private _batch(stmts: any[]): Promise<any> {
    let rv: Promise<any> = new Promise((resolve, reject) => {
      let wincb = (dbrv: any) => {
        resolve(dbrv);
      };

      let losecb = (tx: any, err: Error) => {
        reject(err);
        return false;
      };

      this._db.transaction((tx: any) => {
        let nstmts = stmts.length;
        for (let ix = 0; ix < nstmts; ix++) {
          let sql:string;
          let phs:any[];
          if (Array.isArray(stmts[ix])) {
            sql = stmts[ix][0];
            phs = stmts[ix][1];
          } else {
            sql = stmts[ix];
            phs = [];
          }
          tx.executeSql(sql, phs, undefined, losecb);
        }
      }, losecb, wincb);
    });
    return rv;
  }

db is initialized like this:

   if (win.sqlitePlugin) {
      let openparms = {
        name: _SQLITE_DB_NAME,
        location: 2, // local backup
        createFromLocation: 0
      };
      this._db = win.sqlitePlugin.openDatabase(openparms);
    } else {
      console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
      this._db = win.openDatabase(_SQLITE_DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
    }

_batch is called with an array of statements. They can either be a single string of SQL or an array beginning with the SQL and then containing placeholders. Called like so:

this._batch([
  "CREATE TABLE version (version INT NOT NULL PRIMARY KEY)",
  ["INSERT INTO version (version) VALUES ($1)", [1]],
]);
1 Like
Error while executing Sqlite transaction
#5

Pardon my inexperience but how is this._db defined? Is it an SqlStorage like this?
_db: SqlStorage;

#6

No, it’s an any because it is initialized differently depending on whether we have SQLite or not. I’m not a big fan of the SQLStorage API, for reasons discussed here.

#7

Jaja i’m actually making a tutorial about SqlStorage instead of ionic-native due to SqlStorage managing the use of WebSql as fallback when in browser for development and web versions of the app.

Maybe when i’m finished it should solve most problems you guys have.

#8

I don’t think this is something that can be solved by a tutorial. query is insufficient to do proper transaction processing.

#9

Oh yes, i don’t rely on the query method at all, i call the database transaction directly, and redefine the query as a one line transaction.

#10

How do you access “the database transaction directly” from an SqlStorage object without violating encapsulation and access control? The _db member is private.

#11

Well i do violate that private property, say i have this in my DB class, in telling that db var is type any typescript doesn’t complain about the private var db._strategy._db which is the one that allows to acces the transaction method:

export class DB {
  public db: any = new Storage(SqlStorage, {name: 'sonoramobile'});

  getDB() {
    return this.db._strategy._db;
  }

  queryTx(tx: any, query: string, bindings: any[], raw: boolean) {
    return new Promise((resolve, reject) => {
      tx.executeSql(query, bindings,
        (tx, success) => {
          if (raw) {
            return resolve(success)
          }
          resolve(this.fetchAll(success));
        },
        (tx, error) => {
          reject(error);
          return true
        }
      );
    });
  }
  queryAll(SQL: any, raw: boolean = false) {
    return new Promise((resolve, reject) => {
      var promises = [];
      this.getDB().transaction((tx) => {
        for (var i = 0; i < SQL.query.length; i++) {
          promises.push(
            this.queryTx(tx, SQL.query[i], SQL.args[i], raw)
          );
        }
        return Promise.all(promises).then((success) => {
          resolve(success);
        }, (err) => {
          reject(err);
        })
      })
    })
  }
}
#12

Yeah, that’s not acceptable to me. I would rather roll my own solution based on what is publicly available than to circumvent type-checking and access control in order to poke around in internals where I’m not wanted. The entire point of creating access controls and concepts like interface/implementation firewalls is to prevent the bugs that are caused by approaches like this. I wish you well in your endeavors, but can’t get onboard that train.

#13

I guess that’s true, we’re not supposed to reach that property but there’s a few petitions to ionic to expose a transaction method or something with no response, so i’m currently using SqlStorage only to open the database, everything else is just using directly WebSql when in web, and SqLite plugin in mobile.

I’m basically telling SqlStorage “gth, i use you to open the database, then i don’t care if you even exist”.

#14

What about this way from https://github.com/driftyco/ionic/issues/6594 :

` private storage = new Storage(SqlStorage, { name: 'myDatabase, location: ‘default’ });

this.storage.query("SELECT 'one request for transaction'")
        .then((response) => {
            response.tx.executeSql("CREATE TABLE IF NOT EXISTS USER (id integer primary key, name text");
            response.tx.executeSql("CREATE TABLE IF NOT EXISTS PROJECT (id integer primary key, name text");
        });`
#15

I don’t like that i have to do a sql request just to get the transaction object, in most cases one would need to make an empty query, that would introduce a few overhead since each transaction would need to add an extra unwanted query.

Here’s the request for exposing transaction api made in ionic repo, if all of you are kind to give it a thumbs up to encourage ionic staff to expose the transaction api: #5245

#16

A major problem with that is that query should be able to use readTransactioninternally. It would not make sense to force an operation that is inherently read-only to use, let alone return, a writeable transaction object.