SQLite Error: no such column:

Question

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.

Solution

You are using a string to select from the database. The string is not in quotes so it is expected to be a column. That is why the error says that column doesn't exist, it is not saying _id doesn't exist.

Wrap the value in single quotes and it will work. And make sure it's not a user specified value or you'll have SQL injection possibility. Use parameters.

Comments

[Richard]

Thank you. I am not at my pc now. But will check it out later.

[Richard]

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

[Nailson Landim]

Thanks! In my case i was using double quotes. The crazy thing is that i tested the query with double quotes on my PC using dBeaver. (my target device is an ESP 32 microcontroller)