androidsqlitekotlinandroid-sqlitesqliteopenhelper

Android Sqlite Database db.exec() is returning syntax error in the SQL text


I am using SQLiteOpenHelper to create an SQLite DB for my Android app. When I run a function to update data in the database ( db.exec(....) ), I am getting a syntax error with the following message:

E/SQLiteLog: (1) near "VARCHAR": syntax error in "INSERT INTO Switch_Database (Switch_Name VARCHAR, Switch_Status INTEGER) VALUES ('SpotifySwitch', 1)

I'm sure it's just a small syntax error but I've spent a lot of time and have not been able to figure out the answer. I'd appreciate some help.

Here's the function:

fun changeSwitchValue(switchName: String, status: Int) {
        val db: SQLiteDatabase = this.writableDatabase
        db.execSQL(
            "INSERT INTO $TABLE_NAME ($SWITCH_TABLE_NAME_COLUMN VARCHAR, $SWITCH_TABLE_ISCHECKED_COLUMN INTEGER) VALUES ('$switchName', $status)"
        )
        Log.i(TAG, "Active Switches array now is: ${queryActiveSwitches()}")
    }

and here are my local values above:

private val TABLE_NAME = "Switch_Database"
    private val SWITCH_TABLE_NAME_COLUMN = "Switch_Name"
    private val SWITCH_TABLE_ISCHECKED_COLUMN = "Switch_Status"
    private val TAG = "DatabaseHelper"

and my create function:

override fun onCreate(db: SQLiteDatabase?) {
    if(db != null) {
        //called when db created for the first time
        val sql =
            "CREATE TABLE $TABLE_NAME (switch_id INTEGER PRIMARY KEY AUTOINCREMENT, $SWITCH_TABLE_NAME_COLUMN VARCHAR(20) NOT NULL, $SWITCH_TABLE_ISCHECKED_COLUMN INTEGER)"
        db.execSQL(sql)
        addBasicData(db)
        return
    }
    Log.i(TAG,"DB is null in OnCreate() function")
}

Solution

  • Remove the data types from the INSERT statement:

    fun changeSwitchValue(switchName: String, status: Int) {
        val db: SQLiteDatabase = this.writableDatabase
        db.execSQL(
            "INSERT INTO $TABLE_NAME ($SWITCH_TABLE_NAME_COLUMN, $SWITCH_TABLE_ISCHECKED_COLUMN) VALUES ('$switchName', $status)"
        )
        Log.i(TAG, "Active Switches array now is: ${queryActiveSwitches()}")
    }
    

    But, the recommended and safe method to insert a new row to the table is insert() with ContentValues:

    fun changeSwitchValue(switchName: String, status: Int) {
        val db: SQLiteDatabase = this.writableDatabase
        val values = ContentValues().apply {
            put(SWITCH_TABLE_NAME_COLUMN, switchName)
            put(SWITCH_TABLE_ISCHECKED_COLUMN, status)
        }
        db.insert(TABLE_NAME, null, values)
        Log.i(TAG, "Active Switches array now is: ${queryActiveSwitches()}")
    }