SQLite no such table:

Ionic Framework ionic-v3
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:

2 Likes
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));
            });
        }    
    }
5

Hi,
I m using cordova-sqlite-plugin and iam getting error nosuch table exist, since from week,anyone please help me
I’m working on an ionic1 app…
I’m currently having trouble with the iOS version.
and iam using cordova sqlite plugin,and here db is getting opened but cordovasqlite.execute function(), not working instead iam getting error code:5,message:"no such table:table-name"
for opening database iam using,

if(ionic.Platform.isIOS())
{
console.log(‘database is iso’);
var db = $cordovaSQLite.openDB({name: ‘olarcniapp.db’, iosDatabaseLocation: ‘Library’}); //ios device

	}
	else
	{
			console.log('database is android');
		var	db = $cordovaSQLite.openDB({name:'olarcniapp.db',location:'default'});  // android device

	}
7

Thank you. This worked for me!