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));

Use double quotes to escape column and table names (though brackets work too out of an attempt to be compatible with SQL server, and backticks for MySQL, double quotes are the standard SQL way) — Shawn
– Shawn, Commented Nov 30, 2019 at 21:38

MikeT · Accepted Answer · 2019-12-01 00:03:22Z

0

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

edited Dec 1, 2019 at 0:03

answered Nov 30, 2019 at 21:53

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

escaping sqlite column name in java ContentValues

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related