Cannot read data from table in SQLite and Android device

Hey there,

I need help on SQLite issue here.

I did everything as described on documentation page but when trying to list data on a table it just does not do anything, not even an error:

My code/provider where I try to create tables and insert one record in one table (user_table), it seems to insert because on the select data returned it says length=2 but I am not able to see those data rows at all.

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

import 'rxjs/add/operator/map';

const DB_NAME: string = '__my_app_db';
const USER_TABLE: string = 'user_table';
const LOGIN_TABLE: string = 'login_table';

@Injectable()
export class DataServiceProvider {

    // the database created and opened
    public database: SQLiteObject;

    createUserTable: string = `CREATE TABLE IF NOT EXISTS  ` + USER_TABLE + ` (
        id INTEGER PRIMARY KEY,
        email TEXT, 
        name TEXT, 
        logged INTEGER, 
        profile_id INTEGER
        );`;

    createLoginTable: string = `CREATE TABLE IF NOT EXISTS ` + LOGIN_TABLE + ` (
        id integer primary key AUTOINCREMENT,
        email TEXT, 
        lang TEXT
        );`;

    constructor(public sqlite: SQLite) {
        console.log('Hello DataServiceProvider Provider');

        this.sqlite.create({ name: DB_NAME, location: "default" }).then((db: SQLiteObject) => {
            this.database = db;
            this.createTables();
        }, (error) => {
            if (error === 'cordova_not_available') {
                console.log('You are not in a mobile device');
            } else {
                console.log("ERROR: ", error);
            }
        });
    }

    async createTables() {
        try {
            // try to create tables if they dont exist
            this.runSQL(this.createUserTable);
            this.runSQL(this.createLoginTable);

            this.listAll(USER_TABLE).then((data) => {
                console.log('OK trying to list user table');
                console.log(JSON.stringify(data)); // See block below this code to console output
                // Is this right to check if the table is empty?
                if (data.rows.length === 0) {
                    console.log('Trying to insert first record');

                    // Try to insert one record (this table will contain only one row ever)
                    let sqlText = "INSERT INTO " + USER_TABLE + " (email, name, logged, profile_id) VALUES (?, ?, ?, ?)";
                    let values = ['nomail@noserver.com', 'no-user', 0, 0];
                    this.execute(sqlText, values).then((data) => {
                        console.log('OK inserting user config first record');
                        console.log(JSON.stringify(data));
                    }).then((error) => {
                        console.error('Error inserting first user record');
                        console.error(JSON.stringify(error));
                    });
                }
            }).catch((error) => {
                console.error('Error listing user table');
                console.error(JSON.stringify(error));
            });
        } catch (e) {
            console.log('====== ERROR CREATING TABLES =======');
            console.log(JSON.stringify(e));
        }
    }

    // Used only to create tables
    async runSQL(sql: string) {
        await this.database.executeSql(sql, {}).
            then((data) => {
                console.log('runSQL = ' + sql);
                console.log(JSON.stringify(data));
            }).
            catch(error => {
                console.error('Error running SQL: ' + sql);
                console.error(JSON.stringify(error));
            });
    }

    // To execute any SQL with some params
    async execute(sql: string, params: any[] = []) {
        console.log('Executing: ' + sql);
        return this.database.executeSql(sql, params);
    }

    // To list all rows on the table passed
    listAll(tableName) {
        return this.execute(`select * from ${tableName} `);
    }
}

Console output for the listing code:

[16:54:59]  console.log: OK trying to list user table
[16:54:59]  console.log: {"rows":{"length":2},"rowsAffected":0}

It says length=2, doesn’t that mean that there are 2 rows on the table? If so, how can I see those rows data?

Any help pls?

Why aren’t you just using ionic-storage here?

I don’t know, why should I?

I need SQLite, is this ionic-storage better?

Should I remove SQLite plugin and install this one?

Primarily two reasons: the API is radically simpler, offering a much narrower window of opportunity for creating bugs than directly dealing with SQLite does, and it works both in browsers and on device. More here.

1 Like

Why do you need SQLite?

Actually I just said I needed because in previous Ionic (v1) I used it normally, I mean, I had no problem to use SQLite will try ionic-storage.