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