I am trying to send the entire (50K row) mysql database server data of GPS Coordinates and its information to Android phone using json ( 13.8 MB ) for first time run my app, i got so many major problems!,
the code i used to send json data from php yii2 controller is:
public function actionGetGPS(){
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
Yii::$app->response->data["success"] = true;
Yii::$app->response->data["gps"] = \app\models\gps::findAll();
}
and in the android part, AsyncTask to get the json file, i used this code:
public static class getGpsDataForFirstTimeRun extends AsyncTask<String, String, JSONObject> {
@Override
protected void onPreExecute() {}
@Override
protected JSONObject doInBackground(String... args) {
try {
HashMap<String, String> params = new HashMap<>();
// App.URL_GET_GPS = "http://localhost/myproject/web/gps/get-gps"
JSONObject jsonObject = JSONParser.MakeHttpRequest(App.URL_GET_Gps, "POST", params);
if (jsonObject != null) {
try {
if (jsonObject.getBoolean(App.TAG_SUCCESS)) {
App.Log("Saving Json Data");
JSONArray jsonArray = jsonObject.getJSONArray("Gps");
for (int i = 0; i < (jsonArray.length()); i++) {
JSONObject jObject = (JSONObject)jsonArray.get(i);
// App.db = new DBHelper();
App.db.addGps(jObject.getInt("id"), jObject.getInt("city_id"), jObject.getDouble("latitude"), jObject.getDouble("longitude"));
App.Log("Inserted: "+jObject.toString());
}
App.Log("Json Data Saved");
}
}
catch (JSONException e) {
e.printStackTrace();
}
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@Override
protected void onPostExecute(JSONObject jsonObject) {}
}
and in the dbhelper i have this function for adding gps:
public void addGps(int Gps_id, int city, double latitude, double longitude) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(GPS_COLUMN_ID, Gps_id);
values.put(GPS_COLUMN_CITY_ID, city);
values.put(GPS_COLUMN_LATITUDE, latitude);
values.put(GPS_COLUMN_LONGITUDE, longitude);
db.insert(GPS_TABLE_NAME, null, values);
//db.close(); // Closing database connection // Updated Question
App.Log("New Gps inserted into SQLite: " + Gps_id);
}
Problem 1: PHP server memory: Allowed memory size of 134217728 bytes exhausted
Solution 1: i made it unlimited using this line code:
ini_set('memory_limit', '-1');
Problem 2: However i found out it is not practical at all, if you have +1000 user , server might choke..
Solution 2: so the only way i found is to fetch my data once every 24 hours, and put it in a json file on server, if it is changed in the Database, i will run a query to update it.. and so on, so users when download the app newly, will get the latest data, and old users can download the new data from database since it is small changes..
MY QUESTION HERE: IS THIS APPROACH IS GOOD ? OR THERE IS BETTER ?
Problem 3: The android part :( 50K data is not being inserted to sqlite, i got this error:
10-03 17:47:39.381 702-702/com.myproject.gps E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.myproject.gps, PID: 702
java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:1027)
at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:742)
at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:397)
at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:905)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:147)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:136)
at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197)
at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237)
at com.myproject.gps.Tools.Instance.DBHelper.getGps(DBHelper.java:337)
at com.myproject.gps.Dashboard.GPSMapFragment.onCameraIdle(GPSMapFragment.java:119)
at com.google.android.gms.maps.GoogleMap$21.onCameraIdle(Unknown Source)
at com.google.android.gms.maps.internal.zzf$zza.onTransact(Unknown Source)
at android.os.Binder.transact(Binder.java:380)
at uv.a(:com.google.android.gms.DynamiteModulesB:79)
at maps.ad.j.b(Unknown Source)
at maps.ad.j.a(Unknown Source)
at maps.ad.j$2.run(Unknown Source)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:145)
at android.app.ActivityThread.main(ActivityThread.java:5951)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1400)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1195)
Solution 3: i tried to put the json file: as if i downloaded it, in Asset folder, and tried to insert json data from there: it worked BUT!
Problem 4: it took like 15 Minutes to insert 50,000 json data into sqlite..
still didn't find a solution for this!, any suggestions or comments would be appreciated..
