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