androidsqliteandroid-roomandroid-api-levels

Empty SQLiteException when using Room for certain api levels


I'm getting this error when loading my app:

2024-10-29 13:29:19.532  3647-3854  SQLiteLog               org.druidanet.druidnet               E  (1) 
2024-10-29 13:29:19.727  3647-3647  AndroidRuntime          org.druidanet.druidnet               E  FATAL EXCEPTION: main
                                                                                                    Process: org.druidanet.druidnet, PID: 3647
                                                                                                    android.database.sqlite.SQLiteException: no such column: TRUE (Sqlite code 1): , while compiling: SELECT common_name, plantId, image_path, latin_name,language FROM PlantView ORDER BY common_name, (OS error - 88:Socket operation on non-socket)
                                                                                                        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                                        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:925)
                                                                                                        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:536)
                                                                                                        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
                                                                                                        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
                                                                                                        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                                                                                                        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                                                                                                        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1397)
                                                                                                        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1367)
                                                                                                        at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.kt:156)
                                                                                                        at androidx.room.RoomDatabase.query(RoomDatabase.kt:484)
                                                                                                        at androidx.room.util.DBUtil.query(DBUtil.kt:75)
                                                                                                        at org.druidanet.druidnet.data.PlantDAO_Impl$1.call(PlantDAO_Impl.java:47)
                                                                                                        at org.druidanet.druidnet.data.PlantDAO_Impl$1.call(PlantDAO_Impl.java:43)
                                                                                                        at androidx.room.CoroutinesRoom$Companion$createFlow$1$1$1.invokeSuspend(CoroutinesRoom.kt:129)
                                                                                                        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
                                                                                                        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:108)
                                                                                                        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
                                                                                                        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
                                                                                                        at java.lang.Thread.run(Thread.java:784)

I tried with a physical tablet with Oreo (API level 26) and virtual machine with same API level and I get this crash. However, in many other devices, like Android 11 (API level 30) it works seamlessly.

I created the database using Room and an asset to initialize it:

@Database(entities = [PlantEntity::class, UsageEntity::class, NameEntity::class, ConfusionEntity::class],
          views =[PlantView::class],
          version = 1)
abstract class AppDatabase: RoomDatabase() {

    abstract fun plantDao(): PlantDAO

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null


        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                Room.databaseBuilder(
                    context,
                    AppDatabase::class.java,
                    "druid_database"
                )
                    // Wipes and rebuilds instead of migrating if no Migration object.
                    .fallbackToDestructiveMigration()
                    .createFromAsset("databases/druid_database.db")
                    .build()
                    .also {
                        INSTANCE = it
                    }
            }
        }
    }
}

Solution

  • I just discovered the bug!

    Older versions of SQLite (previous to API level 30) don't support TRUE / FALSE as synonyms to 1 / 0.

    When creating the SQL View to do the queries into, I was using the keyword TRUE instead of 1:

    @DatabaseView("SELECT p.plantId, p.latin_name, n.common_name, n.language, p.image_path" +
            " FROM Plant p JOIN Name n ON p.plantId = n.plantId WHERE isDisplayName = TRUE"
    )
    

    which works in modern versions of SQLite but it does not work in older versions.

    Replacing TRUE by 1 does the trick!

    PD: kudos to ChatGPT which helped me to locate the bug!