I m using a method to get device details and its location in every 1 min and store it in local database. but the problem is while retrieving the data i m getting so many duplicate records.
// Function to store location details to local SQLite
public void storeLocationToLocal(String imei, String positionDate, String positionTime, String signalStrength, String batteryStrength, String latitude, String longitude, String altitude, String speed, String chargingStatus, String status)
{
SQLiteDatabase db=getWritableDatabase();
String LOCATION_RECORD=imei+"|"+positionDate+"|" +positionTime+ "|"+signalStrength+"|"+batteryStrength+"|"+latitude+"|"+longitude+"|"+altitude+"|"+speed+"|"+chargingStatus+"|"+status;
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + KEY_LOCATION_RECORD + " = " + LOCATION_RECORD, null);
int recordCount = cursor.getCount();
cursor.close();
System.out.println("SQLiteHelper storeLocationToLocal() QUEUE_QUERY : " +"SELECT * FROM " + TABLE_NAME + " WHERE " + KEY_LOCATION_RECORD + " = " + LOCATION_RECORD);
System.out.println("SQLiteHelper storeLocationToLocal() QueueRecordCount:"+recordCount);
if (recordCount>0)
{
System.out.println("SQLiteHelper storeLocationToLocal() QueueRecordCount:"+recordCount + "Is already Exist...");
}
else
{
ContentValues values = new ContentValues();
values.put(KEY_LOCATION_RECORD,LOCATION_RECORD);
long result=db.insert(TABLE_NAME, null, values);
System.out.println("SQLiteHelper storeLocationToLocal():"+result);
System.out.println("SQLiteHelper storeLocationToLocal() LOCATION_RECORD:"+LOCATION_RECORD);
ArrayList<LocationRecordGetterSetter> DatabaseRecordsSendToServer=retrieveLocationFromLocal();
showRetrieveLocationFromLocal(DatabaseRecordsSendToServer);
}
}
// Function to print and check values, that will be uploaded to server
private void showRetrieveLocationFromLocal(ArrayList<LocationRecordGetterSetter> recordsSendToServer)
{
System.out.println("UpdateLocationService showRetrieveLocationFromLocal():");
for (int i=0;i<recordsSendToServer.size();i++)
{
System.out.println("ID: "+recordsSendToServer.get(i).getId()+" LOCATION_RECORD: "+recordsSendToServer.get(i).getLatitude()+","+recordsSendToServer.get(i).getLongitude()+" | "+recordsSendToServer.get(i).getAltitude()+" | "+recordsSendToServer.get(i).getBatteryStrength()+" | "+recordsSendToServer.get(i).getChargingStatus()+" | "+recordsSendToServer.get(i).getImei()+" | "+recordsSendToServer.get(i).getPositionDate()+" | "+recordsSendToServer.get(i).getPositionTime()+" | "+recordsSendToServer.get(i).getSignalStrength()+" | "+recordsSendToServer.get(i).getSpeed()+" | "+recordsSendToServer.get(i).getStatus());
}
}
Error
12:00.341 27260-27260/in.ileaf.ptrackerapp E/AndroidRuntime: FATAL EXCEPTION: main
Process: in.ileaf.ptrackerapp, PID: 27260
android.database.sqlite.SQLiteException: near ":12": syntax error (code 1): , while compiling: SELECT * FROM Location WHERE location_details = 864238037598142|2019-01-22|11:12|0|46|18.477268|73.894804|539.9|0|1|fused
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
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:1318)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
at in.ileaf.ptrackerapp.Utils.SQLiteHelper.storeLocationToLocal(SQLiteHelper.java:78)
at in.ileaf.ptrackerapp.Utils.LocationServices.onLocationChanged(LocationServices.java:210)
at com.google.android.gms.internal.zzcdi.zzq(Unknown Source)
at com.google.android.gms.internal.zzbdw.zzb(Unknown Source)
at com.google.android.gms.internal.zzbdx.handleMessage(Unknown Source)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:165)
at android.app.ActivityThread.main(ActivityThread.java:6375)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:883)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:773)
Enclose the argument to be searched for in single quotes e.g.
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + KEY_LOCATION_RECORD + " = '" + LOCATION_RECORD + "'", null);
Better still use the query convenience method and let it do that and write the SQL for you.
e.g.
SQLiteDatabase db=getWritableDatabase();
String whereclause = KEY_LOCATION_RECORD + "=?";
String[] whereargs = new String[]{LOCATION_RECORD};
Cursor cursor = db.query(TABLE_NAME, null, whereclause, whereargs,null, null, null);
int recordCount = cursor.getCount();
......... rest of your code ......