Ionic 2 SqlStorage transactions, how to use?

luchillo17 · December 4, 2015, 3:56pm

Hi, i need to be able to make queries inside transactions, i have inspected the source code for SqlStorage but as far as i see i have to use the tx returned from a query to make the next one, does it work like that?

If it does i want to avoid that as it will lead to a lot of nesting when more than a few queries with transaction is needed, i did one when i was using ionic 1 but idk if it will work in ionic 2, i need to input a tx variable containig the transaction to the function queryTx and then i can use it inside queryAll, will it work?, i’ve commented the try with Promise.all() and put a workaround:

UPDATE: Found the issue, i leave the code for posterity

import {Injectable} from 'angular2/angular2';

import {Http} from 'angular2/http';
import {Storage, SqlStorage, IonicPlatform} from 'ionic/ionic';

@Injectable()

export class DB {
  constructor(http: Http) {
    this.db = new Storage(SqlStorage, {name: 'thadatabase'});
    this.http = http;
    this.data = null;
    this.self_class = this;
    // this.query = this.db.query;
  }

getDB() {
    return this.db;
}
query(sql, args) {
  // return this.db.query;
  return new Promise((resolve, reject) => {
    var self_class = this;
    this.db.query(sql, args).then((success) => {
      resolve(self_class.fetchAll(success.res));
    }, (err) => {
      reject(err);
    });
  });
}

queryTx(tx, query, bindings) {

  return new Promise((resolve, reject) => {
    var self_class = this;
    tx.executeSql(query, bindings,
      (tx, success) => {
        resolve(self_class.fetchAll(success));
      },
      (tx, error) => {
        reject(error);
      }
    );
  });
}

queryAll(SQL) {

  return new Promise((resolve, reject) => {
    var promises = [];
    var self_class = this;
    this.getDB().transaction(function(tx) {
      for (var i = 0; i < SQL.query.length; i++) {
        promises.push(
          self_class.queryTx(tx, SQL.query[i], SQL.args[i])
        );
      }
      return Promise.all(promises).then(function(success) {
        resolve(success);
      }, function(err) {
        reject(err);
      });
    });
  });
};

fetch (result) {

  if (result.rows.length <= 0) return false;
  return result.rows.item(0);
};

fetchAll (result) {

  var output = [];
  for (var i = 0; i < result.rows.length; i++) {
    output.push(result.rows.item(i));
  }
  return output;
};

bengtler · December 4, 2015, 7:14pm

in your outcommented code --> your !this.getDB().transaction(function(tx) {" call returns a promise, but your queryAll function as no return value. you need to create a promise before, return it at the end of queryAll.

In your transaction you need to reject/resolve this promise.

luchillo17 · December 4, 2015, 7:30pm

Thanks, i actually found my error, besides that, there were a new keyword bad placed in the Promise.all function, i will edit to leave the correct code.

How do you like it now , no seriously i spent almost all day putting this to work.

luchillo17 · December 7, 2015, 8:41pm

Need a little help with the query function, for some reason i keep getting number of '?'s in statement string does not match argument count no matter how i put the sql string and args, have tried:

var sql = 'SELECT * FROM ad_user WHERE nameuser = ? AND password = ?';

this.db.query(sql, username, password)
.then((res) => {
    console.log(res);
}, (err) => {
    console.log('Error: ', err);
});

and

this.db.query(sql, [username, password]).then(...

I think i’m stuck in the ES6 rest params that ionic 2 query method uses under the hood: https://github.com/driftyco/ionic2/blob/master/ionic/platform/storage/sql.ts#L90-L115

bengtler · December 8, 2015, 6:54am

in your sql statement you are using 2 prepared parameters --> two ‘?’ for nameuser and password, but you pass the real values wrong.
The db.query-function takes 2 arguments:

the sql query
an array of parameters

so you have to call it this way:
this.db.query(sql, [username, password]).then....

luchillo17 · December 8, 2015, 7:02pm

Hi thanks for answering, i tried that too, i tried all 4 ways, like this:

query defined like: query(sql, ...args) {...}

query used like: 
this.db.query(sql, username, password).then(....)
this.db.query(sql, [username, password]).then(...)

Like this both of them wrong, also:

query defined like: query(sql, args) {...}

query used like: 
this.db.query(sql, username, password).then(....)
this.db.query(sql, [username, password]).then(...)

I literaly tried with all four combinations and no luck, i also tried with the raw query method and no luck:

this.db.db.query(sql, username, password).then(...)

this.db.db.query(sql, [username, password]).then(...)

luchillo17 · December 8, 2015, 9:44pm

It’s the raw method which is wrong, i tried with the raw method instead of my wrapper with both args as 2 args as specified by ES6 rest args, and also with [username,password] and still get error.

Is it a bug or am i doing something wrong?

rapropos · March 9, 2016, 9:53pm

I don’t understand why the query method returns a transaction at all, because as far as I can tell, they’re unusable. First off, in the error case, the failure handler inside query does not return false, which will cause the cordova sqlite plugin to throw an error and short-circuit everything:

  SQLitePluginTransaction.prototype.handleStatementFailure = function(handler, response) {
    if (!handler) {
      throw newSQLError("a statement with no error handler failed: " + response.message, response.code);
    }
    if (handler(this, response) !== false) {
      throw newSQLError("a statement error callback did not return false: " + response.message, response.code);
    }
  };

I tried making the failure handler return false after calling reject, which succeeded in getting to my promise rejection handler without getting an exception thrown, but the transaction itself is useless, as it has already been marked as finalized by the time the promise has been rejected (same story on resolution, I expect).

So I find it confusing that query bothers to expose the transaction, as there doesn’t seem to be anything that can productively be done with it (and the way things are now, I don’t see how the rejection handler will ever get called). I would propose getting rid of it entirely, and just resolving with the res result and rejecting with err.

I personally would find a separate transaction (and readTransaction, for that matter) method in SqlStorage to be extremely useful.

luchillo17 · March 9, 2016, 10:32pm

That’s exactly the approach i took, i don’t call the query method ever, i dig in the code and get the db directly and initiate the transaction from there, i bypassed the query method that ionic’s SqlStorage exposes and made the queries directly in the db object instead of the wrapper.

rapropos · March 9, 2016, 10:51pm

My project is in TypeScript, and both Storage._strategy and SqlStorage._db are private, so attempts to access them from outside their class are prohibited at transpile-time.

luchillo17 · March 10, 2016, 12:21am

Well you have to remember that the Typescript transpiler works like a linter, it still transpiles, you take it as a warning and ignore it, in my project i also ignore it and have been done so for the last month or so.

rapropos · March 10, 2016, 12:36am

I’m not excited about subverting the tools like that. I have noEmitOnError in my tsconfig.json and I’m not keen on removing it, so I’m going to look into replacing SqlStorage instead. The whole StorageEngine family tree smells strange to me in the first place. The parent interface promises the query method, but LocalStorage doesn’t deliver it.

I would have StorageEngine just promise get/set/remove, and if SqlStorage is going to extend it (which is debatable, as one would probably be able to just use localforage for this functionality), the methods I would want off the top of my head would be:

query(sql:string, placeholders?:string[]): Promise<SqlResult>;
exec(sql:string, placeholders?:string[]):Promise<SqlResult>;
batch(stmts:SqlStatement[]):Promise<SqlResult>;

query would use readTransaction locally, exec and batch would use transaction. batch would return the result of its final statement. SqlResult would have to be typed, and SqlStatement would need to be an alias for the “SQL plus optional placeholders” idiom used everywhere in the WebSql API.

In any event, I just wanted to share my findings regarding difficulty using the existing code to do transactional stuff, in case anybody else happened to be banging their head against this particular section of wall.

sphaso · March 10, 2016, 8:16am

Hey guys, in case you were wondering, I had already opened an issue about the SqlStorage problem with transactions. You can look at it here: https://github.com/driftyco/ionic/issues/5245

luchillo17 · March 10, 2016, 1:25pm

@sphaso There’s still no answer about exposing the transaction right? seems they ignored my last comment in that issue.

sphaso · March 10, 2016, 1:50pm

yeah still no answer, I don’t know if they mean “we won’t fix this” or Adam was just distracted when he closed the issue. Maybe we should ping him on Github?

luchillo17 · March 10, 2016, 5:09pm

If you would please, i want to stop using a custom query method.

varshil29 · July 13, 2016, 9:52am

hey luchillo17 , Your problem is solved or not because i also get same issue as you.

code that i used
import {Storage, SqlStorage} from ‘ionic-angular’;
storage: Storage = null;
this.db = new DatabaseHelper();
let options = {
name: ‘_Novity’,
backupFlag: SqlStorage.BACKUP_LOCAL,
existingDatabase: false
};
this.storage = new Storage(SqlStorage, options);
this.db.createMemberTable();
this.db.createLoginTable();

        var sql = "INSERT INTO ' + this.db.sqtable_UserInfo + ' ( '+this.db.COLUMN_userId +' , '+ this.db.COLUMN_password + ' ) VALUES ( ?, ?)";
        this.storage.query(sql, ['varshil','shah']).then((res) => {
              console.log(res);
          }, (err) => {
              console.log('Error: ', err.res);

});

luchillo17 · July 13, 2016, 1:22pm

Try to format your code, it’s hard to read, search for ‘markdown code block’ since this forum have support for GFM (Github Flavour Markdown).
This issue was about sql transactions, in your code i don’t see anything about transactions.
In your last sql query is that a dynamic insert based on params?

varshil29 · July 14, 2016, 5:21am

Whole code that i used

import {Component} from '@angular/core';
import {Platform, ionicBootstrap} from 'ionic-angular';
import {LoginTabPage} from './pages/login-tab/login-tab';
import {enableProdMode} from '@angular/core';
import {WebService} from './services/webservice';
import {StatusBar} from 'ionic-native';
import {DatabaseHelper} from './services/DatabaseHelper';
import {Storage, SqlStorage} from 'ionic-angular';

@Component({
  template: '<ion-nav [root]="rootPage"></ion-nav>'
})
export class MyApp {
  rootPage: any = LoginTabPage;
  //public db: SQLite;
  db : DatabaseHelper;
  storage: Storage = null;


  constructor(platform: Platform) {

    platform.ready().then(() => {
            StatusBar.styleDefault();
            this.db = new DatabaseHelper();
            let options = {
              name: '_Novity',              // the name of the database
              backupFlag: SqlStorage.BACKUP_LOCAL, // where to store the file
              existingDatabase: false              // load this as an existing database
            };
            this.storage = new Storage(SqlStorage, options);
   			    this.db.createMemberTable();
            this.db.createLoginTable();

            var sql = 'INSERT INTO ' + this.db.sqtable_UserInfo + ' ( '+this.db.COLUMN_userId +' , '+ this.db.COLUMN_password + ' ) VALUES ( ?, ?)';
            this.storage.query(sql, ['varshil','shah']).then((res) => {
                  console.log(res);
              }, (err) => {
                  console.log('Error: ', err.res);
              });
        });
  }
}
enableProdMode();
ionicBootstrap(MyApp,[WebService]);

yes in my query i dynamically insert based on params…but in above code i just try to store data for checking

i got below error

luchillo17 · July 14, 2016, 10:26am

Ok, the code formatting block is better, also can be given an extension for code coloring.

Next try the same but with the Network tab in dev tools, let’s see what kind of request you’re making to the server.