cordova sqllite plugin with ionic 2 giving : "new transaction is waiting for open operation"

Question

I am trying to create a DB service. The initializeDb function is being called from app.ts. Snippet of provider/data/data.ts is like

import {Injectable} from 'angular2/core';
import {Platform, Storage, SqlStorage} from 'ionic-angular';

@Injectable()
export class Data {
  dbHandle;
  platform;

  constructor(platform: Platform) {
    this.platform = platform;
  }
  public initializeDataService() {
    this.platform.ready().then(() => {
      console.log('initializing db');
      this.dbHandle = new Storage(SqlStorage);
      this.dbHandle.query('CREATE TABLE IF NOT EXISTS APP_DATA (KEY_NAME TEXT PRIMARY KEY, KEY_VALUE TEXT)').
        then((data) => { console.log('Table created succesfully' + JSON.stringify(data)); return; },
        (error) => { console.log('Error while creating table: ' + JSON.stringify(error)); return; });
    });
  }
  getRecord(key_name) {
    let result = {};
    console.log('trying to get record for ' + key_name);
    this.dbHandle.query("SELECT * FROM APP_DATA WHERE KEY_NAME = '" + key_name + "'").
      then((data) => {
        console.log('Data obtained from db is ' + JSON.stringify(data));
        if (data.res.rows.length > 0) {
          return data.res.rows.item(0);
        }
        else {
          return result;
        }
      },
      (error) => {
        console.log('Error while selecting record for ' + key_name + ' error is: ' + JSON.stringify(error));
        return result;
      });
    console.log('This should have never been reached');
  }
}

Another page, viz. login looks like:

import {Platform, Page, NavController} from 'ionic-angular';
import {Http, Headers} from 'angular2/http';
import {Data} from '../../providers/data/data';
@Page({
  templateUrl: 'build/pages/login/login.html',
})
export class LoginPage {
  platform;
  constructor(platform: Platform, private data: Data, private http: Http) {
    this.platform = platform;
    this.platform.ready().
      then(() => {
        this.checkLogin();
      });  
  }
  checkLogin() {
    this.platform.ready().
      then(() => {
        this.loginRec = this.data.getRecord('LOGIN_REC');
        console.log('Login Rec from DB is '+JSON.stringify(this.loginRec));
        if ({} == this.loginRec) {
          // take to sign-up
          console.log('No local login rec present');
        }
        else {
          // try to do specific login
          console.log('Somethign present');
        }
      },
      (error) => {
        console.log('LoginPage: Platform not yet ready');
      });
  };
}

Now here is the snapshot of the log:

1     414909   log      Angular 2 is running in the development mode. Call enableProdMode() to enable the production mode.
2     415686   log      LoginPage
3     416134   log      Inside app.ts
4     416136   log      initializing db
5     416144   log      OPEN database: __ionicstorage
6     416156   log      new transaction is waiting for open operation
7     416158   log      new transaction is waiting for open operation
12    416175   log      Login Rec from DB is undefined
8     416160   log      Returning db handle
9     416166   log      trying to get record for LOGIN_REC
10    416169   log      new transaction is waiting for open operation
11    416173   log      This should have never been reached
14    416195   log      DB opened: __ionicstorage
13    416179   log      Somethign present
15    416229   log      Table created succesfully{"tx":{"db":{"openargs":{"name":"__ionicstorage","location":2,"createFromLocation":0,"backupFlag":2,"existingDatabase":false,"dblocation":"nosync"},"dbname":"__ionicstorage"},"txlock":true,"readOnly":false,"executes":[],"finalized":true},"res":{"rows":{"length":0},"rowsAffected":0}}
16    416250   log      Data obtained from db is {"tx":{"db":{"openargs":{"name":"__ionicstorage","location":2,"createFromLocation":0,"backupFlag":2,"existingDatabase":false,"dblocation":"nosync"},"dbname":"__ionicstorage"},"txlock":true,"readOnly":false,"executes":[],"finalized":true},"res":{"rows":{"length":0},"rowsAffected":0}}

From the log it seems that opening of DB is being done in back-ground and execution proceeds. All the DB operations seems to be queued till the time DB is opened

What i want is DB to be opened and only after it is successfully opened do all other operations. For this I always though promise/then helps. Logs suggests otherwise.

Any pointers/suggestion on how to achieve this appreciated

Thanks in advance

~Dhaval

Answer 1

It might not be an apt solution but the following did work for me.

1. I put Data as a provider in my app.ts

2. Made home page as an intermediate root page (instead of loginPage)

@App({
  template: '<ion-nav [root]="rootPage"></ion-nav>',
  providers: [Data],
  config: {} // http://ionicframework.com/docs/v2/api/config/Config/
})
export class MyApp {
  platform;
  rootPage;
  constructor(platform: Platform, private data : Data) {
    console.log('app.ts constructor');
    this.platform = platform;
    this.initializeApp(data);
    this.rootPage = HomePage;
  }
  initializeApp(data) {
    this.platform.ready().then( () => {
    console.log('Inside app.ts');
    });
  }
}

3. In HomePage only navigated to LoginPage on deviceRead

this.platform.ready().
then(() => {
console.log('HomePage: Platform ready');
this.nav.push(LoginPage);
});

4. Refactored the getRecord function in data.ts to return a promise

getRecord(key_name) {
  let result = {};
  return new Promise( (resolve, reject) => {
    this.dbHandle.query("SELECT * FROM APP_DATA WHERE KEY_NAME = '" + key_name + "'")
    .then((data) => {
      console.log('Data obtained from db is ' + JSON.stringify(data));
      if (data.res.rows.length > 0) {
        console.log('Something is present');
        resolve(data.res.rows.item(0));
      }
      else{
        console.log('Sending back empty record');
        resolve(result);
      }
    },
    (error) => {
      console.log('Error while selecting record for ' + key_name + ' error is: ' + JSON.stringify(error));
      reject(error);
    });
  });
}

5. Finally checking the promise in login.ts as:

this.data.getRecord('LOGIN_REC').
  then((data) => {
    console.log('Login Rec from DB is ' + JSON.stringify(data));
    this.loginRec = data;
    if ({} == this.loginRec) {
      // take to sign-up
      console.log('No local login rec present');
    } else {
      // try to do specific login
      console.log('Somethign present');
    }
  }, (error) => {
    console.log('couldnt fetch record from db' + JSON.stringify(error))
});

Answer 2

You can use observable instead of promise with Angular 2.. You have to create an observable like this:

 doSomeWork(){
   return Observable.create(observer => {
     //do some rest call or Database queries.
     observer.next(someResult);
     observer.complete();
   });
 }

and subscribe on it like this:

 this.doSomeWork().subscribe(data=>{
     // do something with the data
 });

here is a code snippet that might work with your code (I have not checked formatting)

    getRecord(key_name) {
        let result = {};
        console.log('trying to get record for ' + key_name);
    return Observable.create(observer => {
        this.dbHandle.query("SELECT * FROM APP_DATA WHERE KEY_NAME = '" + key_name + "'").then((data) => {
            console.log('Data obtained from db is ' + JSON.stringify(data));
            if (data.res.rows.length > 0) {
                observer.next(data.res.rows.item(0));
            }
            else {
               observer.next(result);
            }
            observer.complete();
         },(error) => {
            console.log('Error while selecting record for ' + key_name + ' error is: ' + JSON.stringify(error));
            observer.next(result);
            observer.complete();
         });
        console.log('This will never be reached');
   });
}

and when you want to get the record, you have to subscribe on the observable you created.

this.getRecord('LOGIN_REC').subscribe(data => {
        console.log("data",data);
});