Ionic SQLite Provider issue

Hey everybody,

Im developing an small app for some friends and im little bit in trouble. The app starts with and login, which connects through http to the backend after an succeeded login im connecting again to the backend and receive some data, which works quite fine. Now there is my problem. I want to store the data in sqlite but I can’t insert the data and receive it from the database with an select request from my provider.

Provider:

import { Injectable } from '@angular/core';
import { Http } from '@angular/http';
import 'rxjs/add/operator/map';
import { Platform } from 'ionic-angular';


import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
/*
  Generated class for the DatabaseProvider provider.

  See https://angular.io/docs/ts/latest/guide/dependency-injection.html
  for more info on providers and Angular DI.
*/


const DATABASE_FILE_NAME: string = 'data.db';

@Injectable()
export class DatabaseProvider {

  private db: SQLiteObject;
  m: string[] = [];

  constructor(public http: Http, public sqlite: SQLite) {
    console.log('Hello Database Provider');
    this.createDatabaseFile();
    
  }

  public createDatabaseFile(): void {
    this.sqlite.create({
      name: DATABASE_FILE_NAME,
      location: 'default'
    })
      .then((db: SQLiteObject) => {
        console.log('erstellt!');
        this.db = db;
        this.createTables();
      })
      .catch(e => console.log(e));
  }

 private createTables() {
      this.db.executeSql('CREATE TABLE IF NOT EXISTS `m` (  `NAME` varchar(31) DEFAULT NULL)', {})
      .then(() => {console.log('Table M created!!! !'); })
      .catch(e => console.log(e));
  }


   public createM(name: string) {
        return new Promise((resolve, reject) => {
            this.db.executeSql("INSERT INTO m (name) VALUES (?)", [name]).then((data) => {
                resolve(data);
            }, (error) => {
                reject(error);
            });
        });
    }
    


  public getM() {
        return new Promise((resolve, reject) => {
            this.db.executeSql("SELECT * FROM m", []).then((data) => {
                let m = [];
                if(data.rows.length > 0) {
                    for(let i = 0; i < data.rows.length; i++) {
                        m.push({
        
                            name: data.rows.item(i).name
                        });
                    }
                }
                resolve(m);
            }, (error) => {
                reject(error);
            });
        });
    }


}

On my HomePage (login) after an successful login I create the DB for the first time and go the map page

Home:

 saveM() {
        let url = ' http://localhost:8888/m.php ';
        console.log("checking connection to m php");
        this.http.get(url).map(res => res.json()).subscribe(
            data => { // here is my problem!
                    for(let i = 0; i < data.rows.length; i++) {
                      console.log("data has rows!!");
                      this.db.createMr(data.rows.item(i).name).then((result) => {
                         }, (error) => {
                       console.log("ERROR: ", error);
                       });
                    }

            });
    }


	public login() {
    this.showLoading()
    this.auth.login(this.registerCredentials).subscribe(allowed => {
      if (allowed) {     
        console.log("LOGIN OK AND SAVING DATA FROM HP");  
        this.saveM();
        this.navCtrl.setRoot('TabsPage');
      } else {
        this.showError("User or Password wrong!");
      }
    },
      error => {
        this.showError(error);
      });
  }


In the Map Page im trying to get the data name which I have stored in my created DB

import { Component } from '@angular/core';
import { IonicPage, NavController, NavParams } from 'ionic-angular';
import { DatabaseProvider } from '../../providers/database/database';
import { Http } from '@angular/http';

/**
 * Generated class for the MapPage page.
 *
 * See http://ionicframework.com/docs/components/#navigation for more info
 * on Ionic pages and navigation.
 */

@IonicPage()
@Component({
  selector: 'page-map',
  templateUrl: 'map.html',
})
export class MapPage {

  public mList: Array<Object>;

  constructor(public navCtrl: NavController, public navParams: NavParams, private db: DatabaseProvider, public http: Http,) {
  this.db = db;

  }

  ionViewDidLoad() {
    console.log('ionViewDidLoad MapPage');

  }



  public loadM() {
        this.db.getM().then((result) => {
            this.mList = <Array<Object>> result;
        }, (error) => {
            console.log("ERROR: ", error);
        });
    }

}

Here is an example of my output from the HP

[{"name":"Is","von":"","vorname":"srd","nummer":"","status":"AHasd","titel":"","Foto":"fa1778cb407c72334be5adf82d446b22"},{"name":"Khl","von":"","vorname":"Tch","nummer":"I","status":"AasdH","titel":"","Foto":"b27156dbbff96c5262bfd4cf9ac3dcf9"}]

Can someone people me with this problem?

Ionic Storage is not enough for your needs?

Im quite new to ionic so I though that, that sqlite is a better option. my backend has 2500 items and each item has 20 variable - is that not a problem for ionic storage?

No, that is not a problem.

The more interesting question is if you need SQL stuff that is offered by a relational database or if you are happy with a key value store (Ionic Storage).

1 Like

Ionic Storage would be nice but my understanding of ionic storage is that you only store a key/value pair and not an object with serval information.

In my app ill need that kind of informations for an address book, pins on a map and for an search option in the history members. So I thought that an sql db would be perfect but if you could show me how I can manage this with ionic storage that would be great :slight_smile:

The “value” can be as complex as you wish. The core issue, as @Sujan12 mentioned, is whether you can get away with queries that are just “give me object with primary key X” or “give me everything”. If you need “give me things where name starts with ‘foo’ and job description is anything but ‘bar’”, then you need direct SQLite. Otherwise, your life will be much easier sticking to Ionic Storage.

Ok thanks for your explanation!

I didn’t really know what the value can be complex as I wish but I think SQLite would fit better. Or what do you think?

The “app” is at the beginning not really complex and has 4 tabs.

  1. Map with geolocation where friends houses in your near will be shown
  2. address book with all members and later I want to add an search option
  3. news page with all the news about the club
  4. profile where you can see the profile

and the app could be used in offline mode after you downloaded the values from the backend

Start with your backend data structure. This will define what actual data there is.

But besides the “search” option this all sounds like direct accesses to lists. And the search will even be performant enough if there won’t be that many members (think: club with a few hundred, not facebook with a few million).

Then you just need a “adresses” key, a “members” key, a “news” key all with lists of things and a profile that is just one thing. Simple.