escaping sqlite column name in java ContentValues

Question

I have an SQLite table with a column named 'group'. I've tried delimiting with single-quotes and square brackets. Each time, it inserts a '0' with no error message. At least when I leave it alone, I get an error message:

2019-11-30 16:18:43.634 11008-11008/com.example.fitcardsworkouttracker E/SQLiteDatabase: Error inserting name=Scrambled egg calories=78 group=80 serving=1 egg

android.database.sqlite.SQLiteException: near "group": syntax error (code 1): , while compiling: INSERT INTO foods(name,calories,group,serving) VALUES (?,?,?,?)

                ContentValues contentValues = new ContentValues();
            String newGroup = (String)tvGroup.getSelectedItem();
            Cursor c = myDatabase.rawQuery("SELECT initial FROM groups WHERE name = ?", new String[] {newGroup});
            c.moveToFirst();
            Log.i("EditHelpingDialog", "initial " + c.getInt(0));
            contentValues.put("name", foodName);
            contentValues.put("serving", etServing.getText().toString());
            contentValues.put("calories", etCalories.getText().toString());
            contentValues.put("group", (byte) c.getInt(0));

Answer 1

GROUP is a KEYWORD and thus without escaping it you may have issues; such as the syntax error that you got.

It is best to not use keywords for entity names/idenntifiers (columns, tables, triggers etc). Otherwise, in may situations you will have to escape (enclose the name in one of [], ``, "", or '').

• see SQL As Understood By SQLite - SQLite Keywords