SQlite syntax error in android

Question

public class ListsSQLiteOpenHelper extends SQLiteOpenHelper {

public static final int VERSION = 1;
public static final String DB_NAME  = "lightsettings_db.sqlite";
public static final String ITEMS_TABLE  = "light_setting_items";
public static final String ITEM_ID = "id";
public static final String ITEM_TYPE = "type";
public static final String ITEM_VALUE = "values";
public static final String ITEM_BUSTYPE = "bustype";
public static final String ITEM_LIGHTTYPE = "lighttype";
public static final String ITEM_SELECT  = "select";

public ListsSQLiteOpenHelper(Context context) {
    super(context, DB_NAME, null, VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    createTable(db);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}


protected void createTable(SQLiteDatabase db) {
    db.execSQL(
            "create table " + ITEMS_TABLE +" (" +
            ITEM_ID + " integer primary key autoincrement not null," +
            ITEM_TYPE + " text" +
            ITEM_VALUE + " text" +
            ITEM_BUSTYPE + " text" +
            ITEM_LIGHTTYPE + " text" +
            ITEM_SELECT + " text" +
            ");"
        );
}
}
private ArrayList<Entry> currentEntries;

private long lineNumber = 1;

private SQLiteDatabase database;

@Override
public void onCreate() {
    super.onCreate();
    ListsSQLiteOpenHelper helper = new ListsSQLiteOpenHelper(this);
    database = helper.getWritableDatabase();
    if(currentEntries == null){
        loadItems();
    }
}

private void loadItems() {
    currentEntries = new ArrayList<Entry>();

    Cursor itemsCursor = database.query(
            ITEMS_TABLE,
            new String[] {ITEM_ID, ITEM_TYPE, ITEM_VALUE, ITEM_BUSTYPE, ITEM_LIGHTTYPE, ITEM_SELECT},
            null, null, null, null, String.format("%s,%s", ITEM_SELECT, ITEM_TYPE));

    itemsCursor.moveToFirst();
    Entry e;
    if(!itemsCursor.isAfterLast()){
        do{
            long id = itemsCursor.getLong(0);
            String type = itemsCursor.getString(1);
            String value = itemsCursor.getString(2);
            String bustype = itemsCursor.getString(3);
            String lighttype = itemsCursor.getString(4);
            String boolvalue = itemsCursor.getString(5);
            boolean select = Boolean.parseBoolean(boolvalue);
            e = new Entry(id, type, value, bustype, lighttype);
            e.setRowId(id);
            e.setSelected(select);
            currentEntries.add(e);
        } while(itemsCursor.moveToNext());
    }

    itemsCursor.close();
}

I get syntax error on

database.query(ITEMS_TABLE,
                new String[] {ITEM_ID, ITEM_TYPE, ITEM_VALUE, ITEM_BUSTYPE, ITEM_LIGHTTYPE, ITEM_SELECT},
                null, null, null, null, String.format("%s,%s", ITEM_SELECT, ITEM_TYPE));

I don't understand why did I get this error:

10-19 17:01:37.925: E/AndroidRuntime(23164): java.lang.RuntimeException: Unable to create application com.example.abc.EntryManagerApplication: android.database.sqlite.SQLiteException: near "values": syntax error: , while compiling: SELECT id, type, values, bustype, lighttype, selected FROM light_setting_items ORDER BY selected,type

kdehairy · Accepted Answer · 2012-10-21 07:18:52Z

2

’values’ is a SQL keyword. If you insist on using it as a field name, put it between double quotes.

answered Oct 21, 2012 at 7:18

kdehairy

2,7301 gold badge22 silver badges29 bronze badges

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

3 Comments

mystery Over a year ago

thanks, i have changed values to itemValues but now i get a new error: android.database.sqlite.SQLiteException: no such column: itemId: , while compiling: SELECT itemId, itemType, itemValues, itemBustype, itemLighttype, itemSelected FROM light_setting_items ORDER BY itemSelected,itemType any idea on this error

kdehairy Over a year ago

please, open a new question with the updated code after modifications. I can't guess what is the new state of the code :). refer here to your new question.

mystery Over a year ago

i opened a new question for this and with someone's help i solved it. thanks

Collectives™ on Stack Overflow

SQlite syntax error in android

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related