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?