CODEWITHSUNDEEP
androidsqlkotlinandroid-room
Jaimin Modi
Jaimin Modi

Reputation: 1667

Android Room - Issue with query with WHERE condition

@Query("delete from " + DatabaseConstant.mSyncAllDataTable + " WHERE id < :idLimit AND tableName = " + DatabaseConstant.mUserBatteryDetailsTable)
suspend fun deleteSyncedBatteryDetailsData(idLimit: Int)

DatabaseConstant.mUserBatteryDetailsTable is "batteryDetails"

Below is the Entiry for SyncAllData

@Entity(tableName = DatabaseConstant.mSyncAllDataTable)
data class SyncAllData(
        @PrimaryKey(autoGenerate = true)
        val id: Int = 0,
        val tableName: String,
        val jsonData: String
)

While Calling above method getting below error :

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: batteryDetails)
public abstract java.lang.Object deleteSyncedBatteryDetailsData(int idLimit, @org.jetbrains.annotations.NotNull()

What might be the issue? As you can see that Entity contains the column name "tableName".

Upvotes: 0

Views: 787

Answers (1)

Shivam Pokhriyal
Shivam Pokhriyal

Reputation: 1104

Use ' quotes otherwise it's treated as an identifier. Quoting the docs here for reference:

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

'keyword'       A keyword in single quotes is a string literal.
"keyword"       A keyword in double-quotes is an identifier.
[keyword]       A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword`       A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

So you need to modify your query to:

@Query("delete from " + DatabaseConstant.mSyncAllDataTable + " WHERE id < :idLimit AND tableName = '" + DatabaseConstant.mUserBatteryDetailsTable + "'")

Upvotes: 1

Related Questions