I have an Sqlite db where I'm limiting it to 10 max rows of latest entries. Logic is when inserting any new entry I check the number of rows. If there are already 10 rows, it will delete the oldest entered row and add a new one. But I'm getting error SQLiteException: near "BYid": syntax error (code 1): , while compiling: DELETE FROM profORDER BYid LIMIT 1. Is this because of any space mismatch or query error?
public class SQLiteHandler extends SQLiteOpenHelper {
private static final String TAG = SQLiteHandler.class.getSimpleName();
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "android_api";
// Profile Settings table name
private static final String TABLE_PROF = "prof";
// Profile Settings information names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_MOBILE = "mobile";
public SQLiteHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_PROF_TABLE = "CREATE TABLE " + TABLE_PROF + "("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_NAME+" TEXT, "+KEY_MOBILE+" TEXT" + ")";
db.execSQL(CREATE_PROF_TABLE);
Log.d(TAG, "Database tables created");
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROF);
// Create tables again
onCreate(db);
}
/**
* Storing Prof_settings details in database
* */
public void addProfile(String name, String mobile){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, name);
values.put(KEY_MOBILE, mobile);
int count = getProfilesCount();
if(count == 10) {
String DELETE_ROW = "DELETE FROM " +TABLE_PROF+ "ORDER BY" +KEY_ID+" LIMIT 1";
db.execSQL(DELETE_ROW); // will delete oldest record
}
long id = db.insert(TABLE_PROF, null, values); // insert to 1st row
db.close(); // Closing database connection
Log.d(TAG, "New profile settings inserted into sqlite: " + id);
}
/**
* Getting Profile Settings data from database
* */
public ArrayList<HashMap<String, String>> getProfDetails()
{
ArrayList<HashMap<String, String>> array_list = new ArrayList<HashMap<String, String>>();
SQLiteDatabase db = this.getReadableDatabase();
//Cursor res = db.rawQuery("SELECT * " + " FROM " + TABLE_PROF + " GROUP BY " + KEY_NAME + " ORDER BY " + KEY_MOBILE + " COLLATE NOCASE;", null);
String selectQuery = "SELECT * FROM " + TABLE_PROF;
Cursor res = db.rawQuery(selectQuery, null);
res.moveToFirst();
while (res.isAfterLast() == false)
{
HashMap<String, String> hashmap= new HashMap<String, String>();
hashmap.put("id", res.getString(res.getColumnIndex(KEY_ID)));
hashmap.put("name", res.getString(res.getColumnIndex(KEY_NAME)));
hashmap.put("mobile", res.getString(res.getColumnIndex(KEY_MOBILE)));
array_list.add(hashmap);
res.moveToNext();
}
return array_list;
}
public int getProfilesCount() {
String countQuery = "SELECT * FROM " + TABLE_PROF;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int cnt = cursor.getCount();
cursor.close();
return cnt;
}
/**
* Re crate database Delete all tables and create them again
* */
public void deleteUsers() {
SQLiteDatabase db = this.getWritableDatabase();
// Delete All Rows
db.delete(TABLE_PROF, null, null);
db.close();
Log.d(TAG, "Deleted all profile info from sqlite");
}
}
