jeudi 27 août 2015

exception in android SQLite in where clause

I'm having the following exception during the execution of a method with a where clause:

android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): , while compiling: SELECT _id, city_name, city_state_id FROM city WHERE city_state_id= ORDER BY _id DESC

this is the code of the method:

public List<City> GetItemsByState(int state_id) throws Exception {
    List<City> cities = new ArrayList<City>();
    try{
        SQLiteDatabase db = mDbHelper.getReadableDatabase();
        String[] projection = {
                RealStateDiaryContract.City._ID,
                RealStateDiaryContract.City.COLUMN_NAME_CITY_NAME,
                RealStateDiaryContract.City.COLUMN_NAME_CITY_STATE_ID
        };
        String sortOrder =
                RealStateDiaryContract.City._ID + " DESC";
        String selection = RealStateDiaryContract.City.COLUMN_NAME_CITY_STATE_ID + "=";
        String[] selectionArgs = {String.valueOf(state_id)};
        Cursor c = db.query(
                RealStateDiaryContract.City.TABLE_NAME,  // The table to query
                projection,                                 // The columns to return
                selection,                                  // The columns for the WHERE clause
                selectionArgs,                              // The values for the WHERE clause
                null,                                       // don't group the rows
                null,                                       // don't filter by row groups
                sortOrder                                   // The sort order
        );
        if (c.moveToFirst()) {
            do {
                City myCity = new City(this.context);
                myCity.setState_id(c.getInt(c.getColumnIndexOrThrow(RealStateDiaryContract.City.COLUMN_NAME_CITY_STATE_ID)));
                myCity.setCity_id(c.getInt(c.getColumnIndexOrThrow(RealStateDiaryContract.City._ID)));
                myCity.setCity_name(c.getString(c.getColumnIndexOrThrow(RealStateDiaryContract.City.COLUMN_NAME_CITY_NAME)));
                cities.add(myCity);
            } while (c.moveToNext());
        }
        if (c != null && !c.isClosed()) {
            c.close();
        }
    }catch (Exception ex){
        throw new Exception("An error occurred - Get City List");
    }
    return cities;
}

the following code is the contract of the tables:

public static abstract class State implements BaseColumns{
    public static final String TABLE_NAME = "state";
    public static final String COLUMN_NAME_STATE_NAME = "state_name";
}

public static abstract class City implements BaseColumns{
    public static final String TABLE_NAME = "city";
    public static final String COLUMN_NAME_CITY_NAME = "city_name";
    //PK-with State
    public static final String COLUMN_NAME_CITY_STATE_ID = "city_state_id";
}

And the following is the code for the creation of the tables:

private static final String SQL_CREATE_STATE =
        "CREATE TABLE " + RealStateDiaryContract.State.TABLE_NAME + " (" +
                RealStateDiaryContract.State._ID + " INTEGER PRIMARY KEY," +
                RealStateDiaryContract.State.COLUMN_NAME_STATE_NAME + TEXT_TYPE  +
                " )";

private static final String SQL_DELETE_STATE =
        "DROP TABLE IF EXISTS " + RealStateDiaryContract.State.TABLE_NAME;

private static final String SQL_CREATE_CITY =
        "CREATE TABLE " + RealStateDiaryContract.City.TABLE_NAME + " (" +
                RealStateDiaryContract.City._ID + " INTEGER PRIMARY KEY," +
                RealStateDiaryContract.City.COLUMN_NAME_CITY_NAME + TEXT_TYPE + COMMA_SEP +
                RealStateDiaryContract.City.COLUMN_NAME_CITY_STATE_ID + INTEGER_TYPE + COMMA_SEP +
                " FOREIGN KEY ( " + RealStateDiaryContract.City.COLUMN_NAME_CITY_STATE_ID + " ) REFERENCES "+ RealStateDiaryContract.State.TABLE_NAME + "(" + RealStateDiaryContract.State._ID + ")" +
                " )";

Thanks in advanced

Aucun commentaire:

Enregistrer un commentaire