Ionic 3 Sqllite connection setup

It is too simple thing to set up the local db connection for local storage in ionic.
But why am i writing blog for simple thing?
Because we make complex with the simple thing only! i myself made complex with this simple thing.

To setup the environment for sqllite please fallow here. Document which will be updated as and when the new version of the Ionic get published. 

Document showed the following steps when i referred it.

Install the Cordova and Ionic Native plugins:

$ ionic cordova plugin add cordova-sqlite-storage
$ npm install --save @ionic-native/sqlite

Usage

import { SQLite, SQLiteObject } from '@ionic-native/sqlite';

constructor(private sqlite: SQLite) { }

...

this.sqlite.create({
  name: 'data.db',
  location: 'default'
})
  .then((db: SQLiteObject) => {


    db.executeSql('create table danceMoves(name VARCHAR(32))', {})
      .then(() => console.log('Executed SQL'))
      .catch(e => console.log(e));


  })
  .catch(e => console.log(e));

 Which is clear from the document that first create the DB and after that write the sql executeSql command.

I have fallowed the same steps as as described in the document and i was able to created local db and table in it.

Now the point is how to access created table to insert the data into it and get the data from the table.

I have spent more than the one day to understand how ionic works with the sqllite. Because as i understand to access the table in the db we need to open the db connection. So i went through lot references which are not clear for me to understand what is required.

Few links are listed below.
1] https://www.thepolyglotdeveloper.com/2015/12/use-sqlite-in-ionic-2-instead-of-local-storage/.

Which gives complete information how to create the db and table in it and also access db.
 Link says the following code

For creating db and table:-

import {Component} from '@angular/core';
import {Platform, ionicBootstrap} from 'ionic-angular';
import {StatusBar, SQLite} from 'ionic-native';
import {HomePage} from './pages/home/home';

@Component({
    template: '<ion-nav [root]="rootPage"></ion-nav>'
})
export class MyApp {
    rootPage: any = HomePage;

    constructor(platform: Platform) {
        platform.ready().then(() => {
            StatusBar.styleDefault();
            let db = new SQLite();
            db.openDatabase({
                name: "data.db",
                location: "default"
            }).then(() => {
                db.executeSql("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)", {}).then((data) => {
                    console.log("TABLE CREATED: ", data);
                }, (error) => {
                    console.error("Unable to execute sql", error);
                })
            }, (error) => {
                console.error("Unable to open database", error);
            });
        });
    }
}

ionicBootstrap(MyApp);

 Accessing the db for table operation is as fallows

import {Component} from '@angular/core';
import {NavController, Platform} from 'ionic-angular';
import {SQLite} from "ionic-native";

@Component({
    templateUrl: 'build/pages/home/home.html'
})
export class HomePage {

    public database: SQLite;
    public people: Array<Object>;

    constructor(private navController: NavController, private platform: Platform) {
        this.platform.ready().then(() => {
            this.database = new SQLite();
            this.database.openDatabase({name: "data.db", location: "default"}).then(() => {
                this.refresh();
            }, (error) => {
                console.log("ERROR: ", error);
            });
        });
    }

    public add() {
        this.database.executeSql("INSERT INTO people (firstname, lastname) VALUES ('Nic', 'Raboy')", []).then((data) => {
            console.log("INSERTED: " + JSON.stringify(data));
        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error.err));
        });
    }

    public refresh() {
        this.database.executeSql("SELECT * FROM people", []).then((data) => {
            this.people = [];
            if(data.rows.length > 0) {
                for(var i = 0; i < data.rows.length; i++) {
                    this.people.push({firstname: data.rows.item(i).firstname, lastname: data.rows.item(i).lastname});
                }
            }
        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error));
        });
    }

}


Which clear that to create the db and table command is  openDatabase.
But when it come to Ionic 3 i was not able to find any command linke openDatabase.

Ionic 2 Sqllite was accessing the ref from ionic-native
But when it comes from Ionic 3 Sqllite was accessing the ref from @ionic-native/sqllite.

So some commands are changed ionic 3, sqllite removed the the command   openDatabase.
The command openDatabase will create the database if not exits.

When i did some R&D with the sqllite in my project found that the command create in ionic 3 have internal command openDatabase.








      
So it clear from the sqllite index.js that ionic 3 create command  do the same operation as the ionic 2 openDatabase.

Finally the conclusion is that don't get confused with the naming both create command ionic 3 and openDatabase in ionic 2 both are same.

Now let's see the code to create the the local db and accessing them.

import { Component } from '@angular/core';
import { Platform } from 'ionic-angular';
import { StatusBar } from '@ionic-native/status-bar';
import { SplashScreen } from '@ionic-native/splash-screen';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';


import { HomePage } from '../pages/home/home';
@Component({
  templateUrl: 'app.html'
})

export class MyApp {
  rootPage: any = HomePage;
  private db: SQLiteObject = null; //storage the SQLiteObject return by create method


  constructor(private platform: Platform, statusBar: StatusBar, splashScreen: SplashScreen, private sqlite: SQLite) {
    platform.ready().then(() => {         
        //Create/Open the DB
      this.openDB().then(() => {
        //call createTable method
        this.createTable();
      });
    });
  }



  public openDB(): Promise<void> {
    return this.sqlite.create({
      name: 'sandallocaldb.db',
      location: 'default'
    })
      .then((db: SQLiteObject) => {
        //storage object to property
        this.db = db;
      });
  }

  //creamos tabla
  public createTable(): Promise<void> {
    //you can access to method executeSql now

    var SeatMatrix = '{"SeatMatrix":[{"rowNumber":1,"SeatCount":4,"colmatrix":[{"SeatNo":"11","isSeat":true},{"SeatNo":"12","isSeat":true},{"SeatNo":null,"isSeat":false},{"SeatNo":"13","isSeat":true},{"SeatNo":"14","isSeat":true}]},{"rowNumber":2,"SeatCount":4,"colmatrix":[{"SeatNo":"21","isSeat":true},{"SeatNo":"22","isSeat":true},{"SeatNo":null,"isSeat":false},{"SeatNo":"23","isSeat":true},{"SeatNo":"24","isSeat":true}]},{"rowNumber":3,"SeatCount":4,"colmatrix":[{"SeatNo":"31","isSeat":true},{"SeatNo":"32","isSeat":true},{"SeatNo":null,"isSeat":false},{"SeatNo":"33","isSeat":true},{"SeatNo":"34","isSeat":true}]},{"rowNumber":4,"SeatCount":4,"colmatrix":[{"SeatNo":"41","isSeat":true},{"SeatNo":"42","isSeat":true},{"SeatNo":null,"isSeat":false},{"SeatNo":"43","isSeat":true},{"SeatNo":"44","isSeat":true}]},{"rowNumber":5,"SeatCount":4,"colmatrix":[{"SeatNo":"51","isSeat":true},{"SeatNo":"52","isSeat":true},{"SeatNo":null,"isSeat":false},{"SeatNo":"53","isSeat":true},{"SeatNo":"54","isSeat":true}]}],"SeatCount":20}';
   
    var query = 'create table IF NOT EXISTS t_class' +
      '(ClassID integer PRIMARY KEY, ClassName text NOT NULL,' +
      ' SeatCount integer NOT Null,Price integer NOT Null, SeatMatrix text NOT NULL)';
    this.db.executeSql(query, {})
      .then(() => {
       
      })
      .catch(e => {
        console.log(e);
        alert(e);
      });
    return this.db.executeSql('Insert into t_class VALUES(1,"Balcony",20,90,?),(2,"I-Class",20,70,?),(3,"II-Class",20,50,?),(4,"III-Class",20,30,?)', [SeatMatrix, SeatMatrix, SeatMatrix, SeatMatrix]).then((data) => {
    })
      .catch(e => {
        alert(JSON.stringify(e))
      });
  }
}

The code above will create the DB and Table in it

import { Injectable } from '@angular/core';
import { Http } from '@angular/http';
import { Platform } from 'ionic-angular';
import 'rxjs/add/operator/map';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import {classparam} from '../../providers/business_objects/classparam'


@Injectable()
export class DbproviderProvider {
  private db: SQLiteObject = null; //storage the SQLiteObject return by create method
  public classesList: classparam[]=[];
  constructor(platform: Platform, public http: Http, private sqlite: SQLite) {
   
  }

  public getclass():Promise<void>{
    return this.openDB().then(()=>{
      this.getTableData();
    })
  }

  //Open the DB connection
  public openDB(): Promise<void> {
    return this.sqlite.create({
      name: 'sandallocaldb.db',
      location: 'default'
    })
      .then((db: SQLiteObject) => {
        //storage object to property
        this.db = db;
      })
      .catch(e => {
        alert(JSON.stringify(e));
      });
  }

  //Get data from DB
  public getTableData(): Promise<void> {
    //you can access to method executeSql now
    return this.db.executeSql("select ClassID,ClassName,SeatCount,Price,SeatMatrix from t_class", []).then((data) => {
     
      this.classesList=[];
      for(let i=0;i<data.rows.length;i++){
       
        let objclass=new classparam();
        objclass.classID=data.rows.item(i).ClassID;
        objclass.className=data.rows.item(i).ClassName;
        objclass.classSeatCount=data.rows.item(i).SeatCount;
        objclass.classMatrix=data.rows.item(i).SeatMatrix;
        objclass.classPrice=data.rows.item(i).Price;
        if(data.rows.item(i).SeatCount<=0){
          objclass.classDisable=true;
          objclass.classColor="danger";
        }
        else{
          objclass.classDisable=false;
          objclass.classColor="darkcoral";
        }
        this.classesList.push(objclass);
       
      }
     
    })
      .catch(e => {
        console.log(e);
        alert(e);
      });
      }
}

The code above access the db.

First Create the connection to db and put the connection information in some property and use that property inside the class where ever you need to access the DB.

Thanks and Regards
Keerthi RB
Application Programmer
Mindmaps Technologies