SQLite - ExecuteSql of undefined

Ionic Framework ionic-v3
1

I’m having some issues inserting into an SQLite database if no rows exist. I’ve looked up the query and tried a couple of suggestions I found but none have worked.

The CREATE TABLE IF NOT EXISTS query works fine, but the INSERT OR IGNORE does not. I keep getting an error saying cannot read property ‘executeSql’ of undefined.

This is what I have:

let db = new SQLite();
db.openDatabase({
  name: "data.db",
  location: "default"
}).then(() => {
  db.executeSql("CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY AUTOINCREMENT, alarmPaused BOOLEAN, alarmVibrate BOOLEAN,
          alarmRing BOOLEAN, monAlarmOn TEXT, monAlarmOff TEXT, tueAlarmOn TEXT, tueAlarmOff TEXT, wedAlarmOn TEXT, wedAlarmOff TEXT,
          thuAlarmOn TEXT, thuAlarmOff TEXT, friAlarmOn TEXT, friAlarmOff TEXT, satAlarmOn TEXT, satAlarmOff TEXT, sunAlarmOn TEXT, sunAlarmOff TEXT)", {}).then((data) => {

  console.log("TABLE CREATED: ", data);
  }, (error) => {
    console.error("Unable to execute sql", error);
  })
  }, (error) => {
    console.error("Unable to open database", error);
  });

db.executeSql("INSERT OR IGNORE INTO settings (id, alarmPaused, alarmVibrate, alarmRing, monAlarmOn, monAlarmOff, tueAlarmOn, tueAlarmOff, wedAlarmOn,
          wedAlarmOff, thuAlarmOn, thuAlarmOff, friAlarmOn, friAlarmOff, satAlarmOn, satAlarmOff, sunAlarmOn, sunAlarmOff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [
  ...
  ...
  ...
  ])
  .then(() => console.log("inserted or ignored"))
  .catch(e => console.log("ERROR " + e));
});
2

Is there a reason you’re attached to using SQL here? It doesn’t seem like a very good match for your data structure. Storing a JSON object with ionic-storage would seem to be much easier and straightforward.

3

To be honest I didn’t know there was a better way of doing it. I planned to store them in firebase, but when getting the values and trying to update the values through date-time I couldn’t get anywhere due to not being able to use ngModel. I have planned to use this as a temporary fix and then come back to it later on.

I’ve got my issue fixed now.

I better take a look at the ionic-storage route to make my life a little easier. I assume a JSON object is the following?

obj = {
  property1: val,
  property2: val
};

I don’t have much experience with with web technologies and so I’ve just been figuring things out as I go along.

Thanks for pointing me in the right direction.

4

Exactly. Think of it as an associative array, which sounds perfect for something like your settings. Ionic-storage will let you save and retrieve it with just a single line of code and zero fooling around with the various pitfalls of directly touching SQLite.

1 Like
5

Hello,

Is that a start up sql script at the start of your application? If so why don’t you put the INSERT statement just after the CREATE TABLE statement. You can do something like that

this.db.executeSql("create table ..., {})
this.db.executeSql("INSERT INTO ..", [1, new Date()])
this.db.executeSql("INSERT INTO ..", [2, ""])
.then((data) => {
.....
})

It is not that it will wait for the create table statement to be finished first and it will run the insert statement. They are like asynchoronous calls.
Hope I have helped you.

Ashley

6

If you wind up going the ionic-storage route that’s great, but in case you don’t or for others benefit I believe I’ve spotted the issue.

It looks like the INSERT OR IGNORE query is outside of the openDatabase scope, which I’m guessing means it’s attempting to run the insert query before the database is actually opened. (Asynchronous calls 'n all)

So the solution, if my guess is correct anyway, is to re-arrange things a bit, something along the lines of this (albeit simplified to show the flow):

let db = new SQLite();
db.openDatabase({
    name: "data.db",
    location: "default"
})
.then(() => {
    return db.executeSql("CREATE TABLE QUERY", null);
})
.then(() => {
    console.log('table created');
    return db.executeSql("INSERT DATA QUERY", DATA)
})
.then(() => console.log("inserted or ignored"))
.catch(e => console.log("ERROR " + e));
1 Like
7

I don’t think trying to insert rows into a table that may not exist yet is a very good idea.

@SigmundFroyd: I don’t think that would cause the error message being reported. db’s constructor can’t have succeeded. I suspect it might be failure to wrap in platform.ready(), but still think in the long run OP is better off with ionic-storage.

8

I can’t remember exactly what I did but I moved some stuff around and realised I had the INSERT query outside of the scope somewhere along the line.

I am now going to look at going down the ionic-storage route because it makes more sense to do so, as @rapropos kindly pointed out.

9

I’ve made a bit of progress but I have a new problem which is as follows:

[13:20:20] error opening ws message: {“category”:“console”,“type”:“info”,“data”:[“Ionic Storage
driver:”,“cordovaSQLiteDriver”]}
[13:20:20] console.log: Storage ready
[13:20:20] console.log: OPEN database: _ionicstorage
[13:20:20] console.log: new transaction is waiting for open operation
[13:20:20] console.log: OPEN database: _ionicstorage - OK
[13:20:20] console.log: DB opened: _ionicstorage

Here’s my code

this.storage.ready().then(() => {
      
      console.log("Storage ready");
      this.storage.length().then(result => {
        
        if (result == 0) {
          
          this.storage.set("settings", this.settingsData.getDefaultSettings());
          console.log("Storage set");
        }
      });
    });

How do I stop a transaction from beginning before the the DB is opened?