SQLite issues - No Output - Need Help

Hi All,

I’m new to SQLite integration. I have created a provider (DatabaseProvider) to serve db related services. Then I’m calling those methods from test.ts file for inserting data and fetching data.

I could not see any output from provider methods, but I noticed all the method throws exception (catch block). Please find the code for DatabaseProvider.ts, test.ts and test.html. Please help.

DatabaseProvider.ts

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

/*
  Generated class for the DatabaseProvider provider.

  See https://angular.io/guide/dependency-injection for more info on providers
  and Angular DI.
*/
@Injectable()
export class DatabaseProvider {

  private db : SQLiteObject;
  private isOpen : boolean;

  constructor(public http: HttpClient, public storage: SQLite ) {
    if(!this.isOpen){
      this.storage = new SQLite();
      this.storage.create({name: "kidacad.db", location: "default"}).then((db:SQLiteObject) => {
        this.db = db;
        db.executeSql("CREATE TABLE IF NOT EXIST studverifystatus (RowId INTEGER PRIMARY KEY AUTOINCREMENT, StudID TEXT, VerifyStatus TEXT)",[]);
        this.isOpen = true;
      }).catch((error) => {
        console.log(error);
      })
    }
  }

  insertStudVerifyStatus(studid: string, vstatus: string){
    return new Promise ((resolve, reject) => {
      console.log("*****1.....");
      let sql = "INSERT INTO studverifystatus (StudID,VerifyStatus) values (?, ?)";
      this.db.executeSql(sql,[studid,vstatus]).then(data => {
        console.log("******2.....");
        resolve(data);
      }, (error) => {
        console.log("*******3.....");
        reject(error);
      })
    })
  }

  getStudVerifyStatus(sid: string){
    return new Promise ((resolve, reject) => {
      this.db.executeSql("SELECT VerifyStatus from studverifystatus where StudID="+sid).then(data => {
        let vstatus = "";
        if(data.rows.length > 0){
          vstatus = data.rows.item(0).VerifyStatus
        }
        resolve(vstatus);
      }, (error) => {
        let vstatus = "";
        if(error.rows.length > 0){
          vstatus = error.rows.item(0).VerifyStatus
        }
        reject(vstatus);
      });
    });
  }

  getAllStudVerifyStatus(){
    console.log("......6.....");
    return new Promise ((resolve, reject) => {
      console.log("......7.....");
      this.db.executeSql("SELECT * from studverifystatus").then(data => {
        console.log("......1.....");
        let svstatus = [];
        if(data.rows.length > 0){
          console.log("......2.....");
          for(var i=0; i<data.rows.length;i++){
            svstatus.push({
              Rid : data.rows.item(i).RowId,
              Sid : data.rows.item(i).StudID,
              vstatus : data.rows.item(i).VerifyStatus
            });
          }
        }
        console.log("......3.....");
        resolve(svstatus);
        console.log("......4.....");
      }, (error) => {
        console.log("......5.....");
        let svstatus = [];
        if(error.rows.length > 0){
          console.log("......2.....");
          for(var i=0; i<error.rows.length;i++){
            svstatus.push({
              Rid : error.rows.item(i).RowId,
              Sid : error.rows.item(i).StudID,
              vstatus : error.rows.item(i).VerifyStatus
            });
          }
        }
        reject(svstatus);
      });
    });
  }







}

test.ts

import { Component } from '@angular/core';
import { IonicPage, NavController, NavParams } from 'ionic-angular';
import { AlertController } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import { Toast } from '@ionic-native/toast';
import { DatabaseProvider } from '../../providers/database/database';
/**
 * Generated class for the TestPage page.
 *
 * See https://ionicframework.com/docs/components/#navigation for more info on
 * Ionic pages and navigation.
 */

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

private studid: string;
private sts: string;
private stud: string;
public studs : string[];

  constructor(public navCtrl: NavController, public navParams: NavParams, public alertCtrl: AlertController, public sqlite: SQLite, public toast: Toast, private database: DatabaseProvider) {

    
  }

  ionViewDidLoad() {
    console.log('ionViewDidLoad TestPage');
  }

  insertStudVerifyStatus(){
    this.database.insertStudVerifyStatus(this.studid,this.sts).then(data => {
      console.log("========"+data);
    }, (error) => {
      console.log(error);
    })
  }

  getAllStudVerifyStatus(){
    alert("Hi..");
    this.database.getAllStudVerifyStatus().then(data => {
      console.log("====1==="+data);
      this.stud = JSON.stringify(data);
      this.studs = JSON.parse(this.stud);
    }, (error) => {
      console.log("===2==="+error);
    })
  }


}

test.html

<!--
  Generated template for the TestPage page.

  See http://ionicframework.com/docs/components/#navigation for more info on
  Ionic pages and navigation.
-->
<ion-header>

  <ion-navbar>
    <ion-title>SQLite Database Test Lab</ion-title>
  </ion-navbar>

</ion-header>


<ion-content padding>


  <ion-item>
    <ion-label floating>Enter Student ID</ion-label>
    <ion-input type="text" [(ngModel)]="studid"></ion-input>
  </ion-item>
  <ion-item>
    <ion-label floating>Enter Verification Status</ion-label>
    <ion-input type="text" [(ngModel)]="sts" id="inpt"></ion-input>
  </ion-item>

  
  <div text-right>
    <button block style="text-transform: none;" ion-button round color="danger" (click)="insertStudVerifyStatus()">Insert Stud Data</button>
    <button block style="text-transform: none;" ion-button round color="danger" (click)="getAllStudVerifyStatus()">Display</button>
  </div>

  <table border="1" width="80%">
      <tr>
        <td align="center"><b>Row ID</b></td>
        <td align="center"><b>Student ID</b></td>
        <td align="center"><b>Verification Status</b></td>
      </tr>
      <tr *ngFor="let x of studs">
         
        <td align="center" >{{x.Rid}}</td>
        <td align="center" >{{x.Sid}}</td>
        <td align="center" >{{x.vstatus}}</td>
  
      </tr>
  </table>


</ion-content>

Hey there, what did the exception say?

OK, great, we’ve injected storage.

wat

But the word “storage” is just an alias name for SQLite, I think we can name it with other different name as well. is “storage” is a keyword? please suggest

Hi, I see something like

console.log: {“message”:“sqlite3_prepare_v2 failure: no such table: studverifystatus”,“code”:5}

But while loading the emulater the provider constructor is being called, which displays as below

[08:49:49] console.log: OPEN database: kidacad.db
[08:49:49] console.log: ionViewDidLoad TestPage
[08:49:50] console.log: OPEN database: kidacad.db - OK

I also noticed an error as below, but not sure for what it is thrown

[08:49:50] console.error: Unhandled Promise rejection: [object Object] ; Zone: ; Task: Promise.then ; Value:
[object Object]

I managed to resolve part of the issue,thanks for the support.

Which means I’m able to create table and Insert data into table, but while retrieving data, the output comes in the (error) block something like below, please anyone suggest as why the output is coming in the (error) block (method : getAllStudVerifyStatus() please refer the code which I have posted in this thread)

[09:37:12] console.log: ===2===[{“Rid”:1,“Sid”:“122”,“vstatus”:“Verified”},{“Rid”:2,“Sid”:“100”,“vstatus”:“Verified”}]

Steps taken to resolve Previous Issues

  1. Systax error in create table query wrongly mentioned “EXIST” instead of “EXISTS”

CREATE TABLE IF NOT EXISTS studverifystatus

  1. Unhandled Promise rejection error : I missed to call “then” and “error” block after Create table query

Wrong Syntax

db.executeSql(“CREATE TABLE IF NOT EXIST studverifystatus (RowId INTEGER PRIMARY KEY AUTOINCREMENT, StudID TEXT, VerifyStatus TEXT)”,[]);

Correct Syntax

db.executeSql("CREATE TABLE IF NOT EXISTS studverifystatus (RowId INTEGER PRIMARY KEY AUTOINCREMENT, StudID TEXT, VerifyStatus TEXT).then(res => {
console.log("Table successfully created "+JSON.stringify(res));
}, (error) => {
console.log(JSON.stringify(error));
});

Issue Resolved. The method executeSql accepts string and object as below. now the output is captured in then block not in error block.

executeSql("SELECT * from tablename where name='name'",[])
1 Like

I get a code 5 because the data binding for executeSql is not working.

let sql = "INSERT INTO studverifystatus (StudID,VerifyStatus) values (?, ?)";
      this.db.executeSql(sql,[studid,vstatus]).then(data => {