How to connect to a SQLite DB?

Ionic Native
1

Hello,
I’m a ionic beginner and I’m in trouble.
In my app.component.ts, I’ve wrote this :

export class MyApp {
  rootPage:any = LoginPage;
  private db: SQLiteObject;

  constructor(platform: Platform, statusBar: StatusBar, splashScreen: SplashScreen, private sqlite: SQLite) {
    platform.ready().then(() => {
      this.createDB();
      statusBar.styleDefault();
      splashScreen.hide();
    });
  }
 
  private createDB() {
    this.sqlite.create({
      name: 'myDB.db',
      location: 'default'
    })
    .then((db: SQLiteObject) => {
      alert('base de données créée')
      this.db = db;
      this.createTable();
    })
    .catch(e => alert(e));
  }

  private createTable() {
    this.db.executeSql('CREATE TABLE IF NOT EXISTS `SIGNALEMENTS` ( `... )', {})
      .then(() => alert('Table créée'))
      .catch(e => console.log(e));
  }
}

It works well, my DB was created (is the default location is android/data ?) but how to connect/open to this database local storage from another page to insert my sql queries ?
Could any one help me please ?
Thanks in advance…

P.S. : Another question, from this sqlite local storage database, I would like to export all theses datas to an online SQL database. May I use the Ionic Native SQLite Porter plugin to do this ?

2

use provider for your 1st query

3

Please edit your post and use the </> button above the post input field to format your code or error message or wrap it in ``` (“code fences”) manually. This will make sure your text is readable and if it recognizes the programming language it also automatically adds code syntax highlighting. Thanks.

4

Sorry and thanks for your advice Sujan12 :wink:

5

Hi to all again :wink:
I followed the vks_gautam1 suggestion by creating a provider which looks like

import { Injectable } from '@angular/core';
import { Http } from '@angular/http';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
 
const DATABASE_NAME: string = 'signalsDatabase.db';

@Injectable()
export class DatabaseProvider {
  public db : SQLiteObject;

  constructor(public http: Http, public sqlite: SQLite) {
  }

  public createDatabase() {
    this.sqlite.create({name: DATABASE_NAME, location: 'default'})
    .then((db: SQLiteObject) => {
      this.db = db;
      })
      .catch(e => alert(e));
  }

  //création de la table `SIGNALEMENTS`
  public createTable() {
    this.db.executeSql('CREATE TABLE IF NOT EXISTS `SIGNALEMENTS` ( `idSIGNALEMENTS` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `signalement` TEXT NOT NULL, `date` TEXT NOT NULL, `commentaires` TEXT NOT NULL, `longit` INTEGER NOT NULL, `latit` INTEGER NOT NULL, `image` BLOB )', {})
      .then(() => alert('SQL query executed'))
      .catch(e => alert(e));
  }

  public insertInto(signal_details){
    let insert_query = 'INSERT INTO `SIGNALEMENTS` (signalement, date, commentaires, longit, latit) VALUES (?, ?, ?, ?, ?)';
    this.db.executeSql(insert_query, [signal_details.signalement, 
                                       signal_details.date, 
                                       signal_details.commentaires,
                                       signal_details.longit,
                                       signal_details.latit
                                      ])
    .then((data) => alert("Data insertion executed"))
    .catch((e) => alert(e));
  }
 
  public getSignalements(){
    return this.db.executeSql('SELECT * FROM SIGNALEMENTS', []);
  }

  //méthode de suppression de la table
  public dropDatabase(){
    this.sqlite.deleteDatabase({name: DATABASE_NAME,      
                                location: 'default'
                              })
  }
  
}

then, I import this database provider in my ListePage like this

import { Component } from '@angular/core';
import { IonicPage, NavController, NavParams } from 'ionic-angular';
import { Http } from '@angular/http';
//importation du provider database caractérisant le DAO de la base de données
import { DatabaseProvider } from '../../providers/database/database';

@IonicPage()
@Component({
  selector: 'page-liste',
  templateUrl: 'liste.html',
})
export class ListePage {
  signal = {};
  signalsTab = [];
  
  constructor(public navCtrl: NavController, public navParams: NavParams, public dbService: DatabaseProvider) {

    let temp = navParams.get('signalement');
    if (temp!=undefined) {
      this.signal = ({signalement: navParams.get('signalement'),
                       date: navParams.get('date'),
                       commentaires: navParams.get('commentaires'),
                       longit: navParams.get('longit'),
                       latit: navParams.get('latit')
                      });
      this.dbService.insertInto(this.signal);
    }

    Promise.all([this.dbService.getSignalements()]).then((data) => {
    this.signalsTab.push(data);
     });
  }.......

The Database creation, InsertInto and getData working very well but I tried to display my Tab objects (signalsTab) in my without success. How could I do this ?

Thanks in advance and really sorry for my poor english level :wink:

6

If you’re not doing complex queries, and are always just fetching either everything or by the unique row id, I would switch to using ionic-storage instead of directly dealing with SQLite. Much simpler syntax and works in browsers as well.