I am trying to develop a local db with the help of Active android.In model class I have a field called updated_date which is a string.I have converted the string updated_date to Date format and trying to compare the field with today's date in where clause.But while doing this I have got a SQLiteException in where() of Active android.
Model Class
@Column(name = "Updated_date")
public String updated_date = "2016-11-16";
DBUtil Class
public static int getTotalPreVisitCount() throws ParseException {
int i = new Select()
.from(Lead.class)
.where(Utility.getDateFromString(new Lead().updated_date) + " >= ?",Utility.getDateFromString(Utility.todayDate()))
.where(new Lead().lead_status + " = ",LMSUtil.PRE_VISIT)
.execute().size();
return i;
}
Converting String field to Date
public static Date getDateFromString(String dbdate) throws ParseException {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date date = formatter.parse(dbdate);
return date;
}
**Logcat**
11-16 18:11:16.232 29852-29987/com.purplepropshop.lms E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.purplepropshop.lms, PID: 29852
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.purplepropshop.lms/com.purplepropshop.lms.ToDoListActivity}: android.database.sqlite.SQLiteException: near "Nov": syntax error (code 1): , while compiling: SELECT * FROM Leads WHERE Wed Nov 16 00:00:00 GMT+05:30 2016 >=
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2377)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2440)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1342)
at android.os.Handler.dispatchMessage(Handler.java:110)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:5344)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:676)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: near "Nov": syntax error (code 1): , while compiling: SELECT * FROM Leads WHERE Wed Nov 16 00:00:00 GMT+05:30 2016 >=
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1339)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1278)
at com.activeandroid.Model.rawQuery(Model.java:349)
at com.activeandroid.query.From.execute(From.java:155)
at com.purplepropshop.lms.Utility.DBUtil.getTotalPreVisitCount(DBUtil.java:50)
at com.purplepropshop.lms.ToDoListActivity.getLeadCount(ToDoListActivity.java:297)
at com.purplepropshop.lms.ToDoListActivity.onCreate(ToDoListActivity.java:78)
at android.app.Activity.performCreate(Activity.java:5361)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1088)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2331)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2440)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1342)
at android.os.Handler.dispatchMessage(Handler.java:110)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:5344)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:676)
at dalvik.system.NativeStart.main(Native Method)
Please help me to solve the issue.I don't understand where i have made mistake.
In this line :
where(Utility.getDateFromString(new Lead().updated_date) + " >= ?",Utility.getDateFromString(Utility.todayDate()))
You are trying to make SQLite compare two Date objects directly which is not possible. Try comparing their UNIX timestamps instead :
where(Utility.getDateFromString(new Lead().updated_date).getTime() + " >= ?",Utility.getDateFromString(Utility.todayDate()).getTime())