Search code examples
androidsqliteandroid-room

Error SqlLite Query with Android 5.0 API 21

enter image description here

This error occurs with android 5.0 APi 21 but this code works perfectly with Android API 33

I'm using SimpleSQLiteQuery to create my select query[![enter image description here

override fun getPropertyBySearch(
    type: String,
    city: String,
    minSurface: Int,
    maxSurface: Int,
    minPrice: Int,
    maxPrice: Int,
    onTheMarketLessALastWeek: Boolean,
    soldOn3LastMonth: Boolean,
    min3photos: Boolean,
    schools: Boolean,
    shops: Boolean
): LiveData<List<RealEstateDatabase>> {

    val iso = ISOChronology.getInstance()
    val today = LocalDate(iso)

    Log.e("today",today.toString())

    val dateMinusThreeMonth = today.minusMonths(3)
    val dateMinus1Week = today.minusDays(7)

    Log.e("dateMinusThreeMonth",dateMinusThreeMonth.toString())
    Log.e("dateMinus1Week",dateMinus1Week.toString())

    val query = """SELECT * FROM RealEstateDatabase WHERE 
                    ('$type' ='' OR type LIKE '%$type%' ) AND 
                    ('$city' ='' OR city LIKE '%$city%' ) AND
                    ($schools = false OR schoolsNear = $schools ) AND 
                    ($shops = false OR shopsNear = $shops ) AND 
                    ($min3photos = false OR count_photo >= 3 ) AND
                    ($minSurface =0 AND $maxSurface = 0  OR  area BETWEEN $minSurface AND $maxSurface  ) AND 
                    ($minPrice =0 AND $maxPrice = 0  OR  price BETWEEN $minPrice AND $maxPrice ) AND 
                    ($onTheMarketLessALastWeek = false  OR  dateOfEntry BETWEEN '$dateMinus1Week' AND '$today' ) AND 
                    ($soldOn3LastMonth = false  OR dateOfSale BETWEEN '$dateMinusThreeMonth' AND '$today') """

    Log.e("query", query)


    return realEstateDao.getPropertyBySearch(SimpleSQLiteQuery(query))
}

Here is the table in question

@Entity
@Parcelize
data class RealEstateDatabase(
@PrimaryKey
var id: String,
var type: String? = null,
var price: Int? = null,
var area: Int? = null,
var numberRoom: String? = null,
var description: String? = null,
var numberAndStreet: String? = null,
var numberApartment: String? = null,
var city: String? = null,
var region: String? = null,
var postalCode: String? = null,
var country: String? = null,
var status: String? = null,
var dateOfEntry: String? = null,
var dateOfSale: String? = null,
var realEstateAgent: String? = null,
var lat: Double ?=null,
var lng: Double ?=null,
var hospitalsNear : Boolean = false,
var schoolsNear : Boolean = false,
var shopsNear : Boolean = false,
var parksNear : Boolean = false,
@ColumnInfo(name = "listPhotoWithText")
var listPhotoWithText : List<PhotoWithTextFirebase> ?=null,
var count_photo : Int? = listPhotoWithText?.size,
)

I don't explain that this code works on one api rather than another, I'm leaning towards a non-compatibility of my sql lite query but I'm not sure or how to fix it

Solution

  • The constant false (as well as true) was introduced in version 3.23.0 of SQLite; as per :-

    • 2018-04-02 (3.23.0)

        1. Add the sqlite3_serialize() and sqlite3_deserialize() interfaces when the SQLITE_ENABLE_DESERIALIZE compile-time option is used.
        1. Recognize TRUE and FALSE as constants. (For compatibility, if there exist columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)

      • Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.

      • https://www.sqlite.org/changes.html

    As such only android API's of 30+ support the use of true and false, as per

    Instead you could use 0 instead of false (non zero, typically 1 for true). e.g.

    val query = """SELECT * FROM RealEstateDatabase WHERE 
                    ('$type' ='' OR type LIKE '%$type%' ) AND 
                    ('$city' ='' OR city LIKE '%$city%' ) AND
                    ($schools = 0 OR schoolsNear = $schools ) AND 
                    ($shops = 0 OR shopsNear = $shops ) AND 
                    ($min3photos = 0 OR count_photo >= 3 ) AND
                    ($minSurface =0 AND $maxSurface = 0  OR  area BETWEEN $minSurface AND $maxSurface  ) AND 
                    ($minPrice =0 AND $maxPrice = 0  OR  price BETWEEN $minPrice AND $maxPrice ) AND 
                    ($onTheMarketLessALastWeek = 0  OR  dateOfEntry BETWEEN '$dateMinus1Week' AND '$today' ) AND 
                    ($soldOn3LastMonth = 0  OR dateOfSale BETWEEN '$dateMinusThreeMonth' AND '$today') """