SQLITE.ALL Prepare 1

Question

In my NativeScript with Angular project, I'm trying to use SQLite to create my database. Whenever I try to run it though, I keep getting an error on my create table statements that says SQLITE.ALL - Prepare Error 1. I've tried looking the problem up but can't really find anything on it.

Here's my database.service.ts file:

var Sqlite = require("nativescript-sqlite");

@Injectable()
export class DatabaseService {

    private createDB() {
        return new Promise((resolve, reject) => {
            return (new Sqlite("imtracker.db")).then(db => {
            db.execSQL("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, password TEXT, firstName TEXT, lastName TEXT, gender TEXT, permissions TEXT, year INTEGER, basketball NUMERIC, dodgeball NUMERIC, football NUMERIC, soccer NUMERIC, softball NUMERIC, volleyball NUMERIC, teamID INTEGER)").then(id => {
                resolve(db);
            }, error => {
                console.log("CREATE USESRS TABLE ERROR: ", error);
                reject(error);
            });
            db.execSQL("CREATE TABLE IF NOT EXISTS leagues (id INTEGER PRIMARY KEY AUTO INCREMENT, teamSize INTEGER, sportID INTEGER)").then(id => {
                resolve(db);
            }, error => {
                console.log("CREATE LEAGUES TABLE ERROR: ", error);
                reject(error);
            });
        }, error => {
            reject(error);
        })
    })
}

public insertUser(user: User) {
    return new Promise((resolve, reject) => {
        this.createDB().then((res: any) => {
            res.execSQL("INSERT INTO users (id,password,firstName,lastName,gender,permissions,year,basketball,dodgeball,football,soccer,softball,volleyball,teamID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", [user.id, user.password, user.firstName, user.lastName, user.gender, user.permissions, user.year, user.basketball, user.dodgeball, user.football, user.soccer, user.softball, user.volleyball, user.teamID]).then(id => {
                console.log("INSERT RESULT: ", id);
                resolve(true);
            }, error => {
                console.log("INSERT USERS FAILED: ", error);
                reject(false);
            })
        })
    })
}

public insertLeague(league: Leagues) {
    return new Promise((resolve, reject) => {
        this.createDB().then((res: any) => {
            res.execSQL("INSERT INTO leagues (id,teamSize,sportID) VALUES (?,?,?)", [league.id, league.teamSize, league.sportID]).then(id => {
                console.log("INSERT RESULT: ", id);
                resolve(true);
            }, error => {
                console.log("INSERT LEAGUES FAILED: ", error);
                reject(false);
            })
        })
    })
}

public selectAllUsers() {
    return new Promise((resolve, reject) => {
        this.createDB().then((res: any) => {
            return res.all("SELECT * FROM user").then(rows => {
                let result: User[] = [];
                for (let row in rows) {
                    result.push({
                        "id": rows[row][1],
                        "password": rows[row][2],
                        "firstName": rows[row][3],
                        "lastName": rows[row][4],
                        "gender": rows[row][5],
                        "permissions": rows[row][6],
                        "year": rows[row][7],
                        "basketball": rows[row][8],
                        "dodgeball": rows[row][9],
                        "football": rows[row][10],
                        "soccer": rows[row][11],
                        "softball": rows[row][12],
                        "volleyball": rows[row][13],
                        "teamID": rows[row][14]
                    });
                }
                resolve(result);
            }, error => {
                console.log("SELECT USERS ERROR: ", error);
                reject(error);
            })
        })
    })
}

public selectAllLeagues() {
    return new Promise((resolve, reject) => {
        this.createDB().then((res: any) => {
            return res.all("SELECT * FROM leagues").then(rows => {
                let result: Leagues[] = [];
                for (let row in rows) {
                    result.push({
                        "id": rows[row][1],
                        "teamSize": rows[row][2],
                        "sportID": rows[row][3]
                    });
                }
                resolve(result);
            }, error => {
                console.log("SELECT LEAGUES ERROR: ", error);
                reject(error);
            })
        })
    })
  }
}

and then here are my models:

leagues.model.ts:

export interface Leagues {
    id: number,
    teamSize: number,
    sportID: number
}

user.model.ts:

export interface User {
    id: number,
    password: string,
    firstName: string,
    lastName: string,
    gender: string,
    permissions: string,
    year: number,
    basketball: boolean,
    dodgeball: boolean,
    football: boolean,
    soccer: boolean,
    softball: boolean,
    volleyball: boolean,
    teamID: number
}

and then here's where I call it in my component:

constructor(private router: Router, private databaseService: DatabaseService) {

    this.databaseService.selectAllUsers().then((res: any) => {
        this.usersList = res;
    })
}

Any help would be greatly appreciated as I'm pretty confused right now. Thanks!

Answer 1

I suspect you are accessing the tables in your select query too early, even before it's created. You will have to update your createDB method to something like below,

private createDB() {
        return (new Sqlite("imtracker.db")).then(db => {
            return Promise.all([
                db.execSQL("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, password TEXT, firstName TEXT, lastName TEXT, gender TEXT, permissions TEXT, year INTEGER, basketball NUMERIC, dodgeball NUMERIC, football NUMERIC, soccer NUMERIC, softball NUMERIC, volleyball NUMERIC, teamID INTEGER)")
                    .catch(error => {
                        console.log("CREATE USESRS TABLE ERROR: ", error);
                        throw error;
                    }),
                db.execSQL("CREATE TABLE IF NOT EXISTS leagues (id INTEGER PRIMARY KEY AUTO INCREMENT, teamSize INTEGER, sportID INTEGER)")
                    .catch(error => {
                        console.log("CREATE LEAGUES TABLE ERROR: ", error);
                        throw error;
                    })
            ]).then(() => db);
        });
    }