SQLite no such table:


#1

Hi, I’m using the cordova-sqlite-storage. And each time I want to insert data in my database, I have this error: no such table: record.

I have created a provider:

    import { Injectable } from '@angular/core';
    import { Http } from '@angular/http';
    import {SQLite} from 'ionic-native';
    import {Platform} from 'ionic-angular';
    import 'rxjs/add/operator/map';
     
    /*
      Generated class for the SqliteProvider provider.
     
      See https://angular.io/docs/ts/latest/guide/dependency-injection.html
      for more info on providers and Angular 2 DI.
    */
    @Injectable()
    export class SqliteProvider {
      data: any;
      public storage: any;
     
      constructor(private http: Http, public platform: Platform) {
        this.data = null;
        this.storage = new SQLite({name: 'db.storage', iosDatabaseLocation: 'Library'});
      }
     
      createDatabase()
      {
        if(this.platform.is('ios'))
        {
            this.storage.executeSql('CREATE TABLE IF NOT EXISTS record (id_record INTEGER PRIMARY KEY AUTOINCREMENT, longitude DOUBLE, latitude DOUBLE, dateStart TEXT, dateEnd TEXT, url_maps TEXT)',{}).then(()=> {
              this.storage.executeSql('CREATE TABLE IF NOT EXISTS measure (id_measure INTEGER PRIMARY KEY AUTOINCREMENT,range TEXT, turbidity FLOAT, temperature FlOAT, comments TEXT, id_record INTEGER)',{}).then(()=> {
                this.storage.executeSql('CREATE TABLE IF NOT EXISTS picture (id_picture INTEGER PRIMARY KEY AUTOINCREMENT, data_picture TEXT, id_record INTEGER)',{}).then(()=> {
                  console.log("creation ok");
                });
              });
            });
        }
}
        insertRecord(longitude,latitude, dateStart, dateEnd, url_maps)
          {
                 console.log("insertion record");
                 if(this.platform.is('ios'))
                 {
                       this.storage.executeSql('INSERT OR REPLACE INTO record(longitude, latitude, dateStart, dateEnd, url_maps) VALUES (?,?,?,?,?)', [longitude,latitude,dateStart,dateEnd,url_maps],function (resultSet) {
                console.log('resultSet.insertId: ' + resultSet.insertId);
                console.log('resultSet.rowsAffected: ' + resultSet.rowsAffected);
              }, function(error) {
                  console.log('SELECT error: ' + error.message);
              });
          }
          } 

          insertMeasure(range,turbidity,temperature)
          {
               console.log("insertion measure");
               if(this.platform.is('ios'))
               {
                   return new Promise(resolve => {
                      this.selectLastIDRecord().then((data)=> {
                          this.storage.executeSql('INSERT OR REPLACE INTO measure(range, turbidity, temperature, id_record) VALUES (?,?,?,?) ',[range,turbidity,temperature,data], function (data) {
                  console.log("insert good measure");
                },function(error) {
                  console.log('Insert error: ' + error.message);
                });
              });
            });   
        }
          }

          selectLastIDRecord()
          {
              console.log("select last ID record");
              if(this.platform.is('ios'))
              {
                     return new Promise(resolve => {
                          this.storage.executeSql('SELECT MAX(id_record) FROM record',[], function (data) {
                               resolve(data.rows.item(0)['MAX(id_record)']);
                     },function(error) {
                             console.log('SELECT error: ' + error.message);
                      });
                   });  
            }
        }

          selectAllRecord()
          {
                console.log("select all record");
                if(this.platform.is('ios'))
                {
                     return new Promise(resolve => {
                            this.storage.executeSql('SELECT * FROM record', [], function (data) {
                                   var records = [];
                                   if(data.rows.length > 0)
                                    {
                                          for(var i = 0; i < data.rows.length; i++)
                                           {
                                                records.push({
                                                      id_record: data.rows.item(i).id_record ,
                                                      longitude: data.rows.item(i).longitude,
                                                      latitude: data.rows.item(i).latitude,
                                                      dateStart: data.rows.item(i).dateStart,
                                                      dateEnd: data.rows.item(i).dateEnd,
                                                       url_maps: data.rows.item(i).url_maps
                                                 });
                                             }
                                        }
                                        resolve(records);
                                        }, function(error) {
                                               console.log('SELECT error: ' + error.message);
                                        });
                         });
                  }  
          }  

          selectMeasure(id_record)
          {
                console.log("select one measure");
                if(this.platform.is('ios'))
                {
                     return new Promise(resolve => {
                            this.storage.executeSql('SELECT * FROM measure WHERE id_record=?', [id_record], function (data) {
                          var measures = [];
                          if(data.rows.length > 0)
                           {
                                for(var i = 0; i < data.rows.length; i++)
                                  {
                                      measures.push({
                                     id_measure: data.rows.item(i).id_measure,
                                    range: data.rows.item(i).range ,
                                     temperature: data.rows.item(i).temperature,
                                     turbidity: data.rows.item(i).turbidity,
                                     comments: data.rows.item(i).comments,
                                    id_record: data.rows.item(i).id_record
                                    })
                                   }
                          }
                          resolve(measures);
                          }, function(error) {
                                 console.log('SELECT error: ' + error.message);
                           });
                     }); 
                }  
        }

I’m calling createDatabase() in App.ts and the others functions in two differents pages.

I don’t know how to insert and select in my database.

Thanks for your help.
Regards


#2

Solved, thanks for your help.


#3

Care to share the solution? You might not be te last person to encounter this error :wink:


#4

Firstly, you have to create the database: this.storage = new SQLite({name: 'db.storage', iosDatabaseLocation: 'Library'});

Secondly, create the tables:

  createDatabase()
        {
            if(this.platform.is('ios'))
            {
                var db = (<any>window).sqlitePlugin.openDatabase({ name: 'db.storage', iosDatabaseLocation: 'Library'}, function (db) {
                    db.executeSql('CREATE TABLE IF NOT EXISTS record (id_record INTEGER PRIMARY KEY AUTOINCREMENT, longitude DOUBLE, latitude DOUBLE, dateStart TEXT, dateEnd TEXT, url_maps TEXT)');
                        db.executeSql('CREATE TABLE IF NOT EXISTS measure (id_measure INTEGER PRIMARY KEY AUTOINCREMENT,range TEXT, turbidity FLOAT, temperature FlOAT, comments TEXT, id_record INTEGER)');
                            db.executeSql('CREATE TABLE IF NOT EXISTS picture (id_picture INTEGER PRIMARY KEY AUTOINCREMENT, data_picture TEXT, id_record INTEGER)');
                                console.log("creation ok");
                }, function (error) {
                    console.log('Open database ERROR: ' + JSON.stringify(error));
                });
            }
            else if(this.platform.is('android'))
            {
                var db = (<any>window).sqlitePlugin.openDatabase({ name: 'db.storage', location: 'default'}, function (db) {
                    db.executeSql('CREATE TABLE IF NOT EXISTS record (id_record INTEGER PRIMARY KEY AUTOINCREMENT, longitude DOUBLE, latitude DOUBLE, dateStart TEXT, dateEnd TEXT, url_maps TEXT)');
                        db.executeSql('CREATE TABLE IF NOT EXISTS measure (id_measure INTEGER PRIMARY KEY AUTOINCREMENT,range TEXT, turbidity FLOAT, temperature FlOAT, comments TEXT, id_record INTEGER)');
                            db.executeSql('CREATE TABLE IF NOT EXISTS picture (id_picture INTEGER PRIMARY KEY AUTOINCREMENT, data_picture TEXT, id_record INTEGER)');
                                console.log("creation ok");
                }, function (error) {
                    console.log('Open database ERROR: ' + JSON.stringify(error));
                });
            }    
        }

Then, insert:

insertRecord(longitude,latitude, dateStart, dateEnd, url_maps)
    {
        console.log("insertion record");
        if(this.platform.is('ios'))
        {
            var db = (<any>window).sqlitePlugin.openDatabase({ name: 'db.storage', iosDatabaseLocation: 'Library'}, function (db) {
                db.executeSql('INSERT OR REPLACE INTO record(longitude, latitude, dateStart, dateEnd, url_maps) VALUES (?,?,?,?,?)', [longitude,latitude,dateStart,dateEnd,url_maps],function (resultSet) {
                    console.log('resultSet.insertId: ' + resultSet.insertId);
                    console.log('resultSet.rowsAffected: ' + resultSet.rowsAffected);
                }, function(error) {
                    console.log('INSERT RECORD: ' + error.message);
                });
            }, function (error) {
                console.log('INSERT RECORD ERROR: ' + JSON.stringify(error));
            });
        }
        else if(this.platform.is('android'))
        {
            var db = (<any>window).sqlitePlugin.openDatabase({ name: 'db.storage', location: 'default'}, function (db) {
                db.executeSql('INSERT OR REPLACE INTO record(longitude, latitude, dateStart, dateEnd, url_maps) VALUES (?,?,?,?,?)', [longitude,latitude,dateStart,dateEnd,url_maps],function (resultSet) {
                    console.log('resultSet.insertId: ' + resultSet.insertId);
                    console.log('resultSet.rowsAffected: ' + resultSet.rowsAffected);
                }, function(error) {
                    console.log('INSERT RECORD: ' + error.message);
                });
            }, function (error) {
                console.log('INSERT RECORD ERROR: ' + JSON.stringify(error));
            });
        }    
    }