SQLite Error: no such column

Ionic Framework ionic-v3
1

Hi All,

I am using Ionic2 with SQLite, I have the following:

app.ts

  private createDatabase(): void {
    let db: SQLite = new SQLite();
    db.openDatabase({
      name: "data.db",
      location: "default"
    }).then(() => {
      db.executeSql("CREATE TABLE IF NOT EXISTS chats (_id TEXT PRIMARY KEY, memberIds TEXT, title TEXT, subTitle TEXT, picture TEXT, lastMessageId TEXT, lastMessageCreatedAt DATE)", {}).then((chatData) => {
        console.log("chats TABLE CREATED: ", chatData);
        db.executeSql("CREATE TABLE IF NOT EXISTS messages (_id TEXT PRIMARY KEY, chatId TEXT, senderId TEXT, ownership TEXT, content TEXT, createdAt DATE, changeDate BOOLEAN, readByReceiver BOOLEAN)", {}).then((messageData) => {
          console.log("messages TABLE CREATED: ", messageData);
        }, (error) => {
          console.error("Unable to execute messages sql", error);
        });

      }, (error) => {
        console.error("Unable to execute chats sql", error);
      });
    }, (error) => {
      console.error("Unable to open database", error);
    });
  }
}

storageServicce.ts

        console.log('addMessage: chat '+chat._id);
        this.database.executeSql("SELECT * FROM chats where _id = " + chat._id, []).then((data) => {
            let chats = [];
            if (data.rows.length > 0) {
                for (var i = 0; i < data.rows.length; i++) {
                    this.chats.push({
                        _id: data.rows.item(i)._id
                    });
                }
            }
            console.log('addMessage: chats.length = ' + chats.length);

Output

addMessage: chat rSkFGaLgQ554FCCYJ 
ERROR: {"message":"sqlite3_prepare_v2 failure: no such column: rSkFGaLgQ554FCCYJ","code":0}

Question

Do you know why I am getting the error? As far as I can see, the column the error is referring to is _id, but it does exist when I create the database.

2

Why not use parameters for selection? I’m not sure if it’ll fix your issue, but it’s better overall and worth a shot.

this.database.executeSql("SELECT * FROM chats where _id = ?", [chat._id]).then((data) => {
3

Thanks, that worked:

this.database.executeSql("SELECT * FROM messages where _id = ?", [data.rows.item(i).lastMessageId]).then((messageData) => {