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

Question

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")
}

The first block of code (the changeSwitchValue function) is where the error is getting called. It says "near VARCHAR" — Binyamin Robbins, Aug 03 '21 at 12:35

forpas · Accepted Answer · 2021-08-03T13:30:23.893

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()}")
}

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

1 Answers1