Sqlite database is not functioning properly in Samsung Android Oreo 8.0 device

Question

I have create one application which performs some database read operations only, (fetching records from db and perform some formula's and show up results)

The app is working fine in all devices tested on Lenovo (5.1.1), Moto (5.1, 6.0, 7.1.1), OnePlus (8.1), Mi A1 (8.0.0), Micromax (5.0), Samsung (7.1)

But it is not working is Samsung device contains (8.0) Devices are Samsung galaxy J8 (SM-J810G), Samsung galaxy S7 (SM-G930W8), Samsung galaxy S9 (SM-G960U).

Below is the code I used.

DBQuery.java

package com.test.dbhelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.test.common.DBConstants;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class DBAdapter extends SQLiteOpenHelper implements DBConstants {
private static String DB_PATH = "";

private SQLiteDatabase mDB;

private static int DB_VERSION = 1;
private Context appContext;

public DBAdapter(Context context) {
    super(context.getApplicationContext(), DB_NAME, null, DB_VERSION);
    DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    appContext = context;
    createDataBase();
}

@Override
public void onCreate(SQLiteDatabase db) {
    System.out.println("DB Helper On Create....");
    this.mDB = db;
    createDataBase();
}

public void createDataBase() {
    SQLiteDatabase db_Read = null;
    try {
        boolean dbExist = checkDataBase();
        if (dbExist) {
        } else {
            db_Read = this.getReadableDatabase();
            db_Read.close();
            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private boolean checkDataBase() {

    try {
        String myPath = DB_PATH + DB_NAME;
        mDB = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e) {
    }
    if (mDB != null) {
        mDB.close();
    }
    return mDB != null ? true : false;
}

private void copyDataBase() throws IOException {

    InputStream myInput = appContext.getAssets().open(DB_NAME);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream myOutput = new FileOutputStream(outFileName);
    byte[] buffer = new byte[1024];
    int length;

    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }

    myOutput.flush();
    myOutput.close();
    myInput.close();
}

public void openDataBase() {
    try {
        String myPath = DB_PATH + DB_NAME;
        mDB = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (Exception e) {
        System.out.println("Open Database failed...");
        e.printStackTrace();
    }
}

public DBAdapter open() throws SQLException {
    mDB = getWritableDatabase();
    return this;
}

@Override
public synchronized void close() {
    if (mDB != null)
        mDB.close();
    super.close();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public int insertData(String table_name, ContentValues initialValues) {
    int record = (int) mDB.insert(table_name, null, initialValues);
    return record;
}

public int deleteData(String table_name, String whereClause) {
    return (int) mDB.delete(table_name, whereClause, null);
}

public int updateData(String table_name, ContentValues initialValues,
                      String whereClause) {
    return mDB.update(table_name, initialValues, whereClause, null);
}

public Cursor executeRawQuery(String query) {
    Cursor c = null;
    c = mDB.rawQuery(query, null);

    return c;
}

}

DBConstants.java

package com.test.common

interface DBConstants {
  companion object {
    const val DB_NAME = "test.db3"
    const val TAB_WELL_RITE = "well_rite"
    const val TAB_WELL_RITE_TANK_VOLUME_IN_GALLON = "tank_volume_in_gallon"
    const val TAB_WELL_RITE_MODEL_WELL_RITE = "model_well_rite"
    const val TAB_WELL_RITE_MODEL_CHALLANGER = "model_challanger"
    const val TAB_WELL_RITE_MODEL_FLEX_LITE = "model_flex_lite"
    const val TAB_WELL_RITE_TANK_VOLUME_IN_LITRE = "tank_volume_in_litre"
  }
}

DBQuery.java

package com.test.dbhelper;

import android.database.Cursor;

import com.test.activity.TestApp;
import com.test.common.Constants;
import com.test.common.DBConstants;
import com.test.common.LogUtils;

import java.util.HashMap;

public class DBQuery implements DBConstants, Constants {

public static String TAG = "DBQuery";

public static HashMap<String, String> getWellRiteModels(String tableName, String field, double value) {

    HashMap<String, String> dataMap = new HashMap<>();

    String query = "SELECT * " + " FROM " + tableName
            + " WHERE " + field + " >= '" + value + "' order by " + field + " LIMIT 1";

    Cursor cursor = TestApp.dbAdapter.executeRawQuery(query);

    if (cursor.getCount() > 0) {
        if (cursor.moveToFirst()) {
            dataMap.put(WELL_RITE, cursor.getString(1));
            dataMap.put(CHALLANGER, cursor.getString(2));
            dataMap.put(FLEX_LITE, cursor.getString(3));
        }
    }
    return dataMap;
  }
}

TestApp.kt

package com.test.activity

import android.app.Application
import com.crashlytics.android.Crashlytics
import com.test.dbhelper.DBAdapter
import io.fabric.sdk.android.Fabric

class TestApp : Application() {

  companion object {
    lateinit var dbAdapter: DBAdapter
  }

  override fun onCreate() {
    super.onCreate()
    Fabric.with(this, Crashlytics())
    dbAdapter = DBAdapter(applicationContext)
    dbAdapter.openDataBase()
  }
}

From the MainActivity.java I am calling below function to get the data using DBQuery method.

var dataMap: HashMap<kotlin.String, kotlin.String> = HashMap()
dataMap = DBQuery.getWellRiteModels(TAB_WELL_RITE, DBConstants.TAB_WELL_RITE_TANK_VOLUME_IN_GALLON, 32.50)

I have test.db3 database with records in assets folder. that will copy from assets to /data/data/... as you can see that code in DBAdapter.java class.

is any specific issue for samsung devices of Android 8.0 ?

Below is the Error Logs

Fatal Exception: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/com.test/databases/test.db3
   at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
   at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1742)
   at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1685)
   at com.test.dbhelper.DBAdapter.executeRawQuery(DBAdapter.java:147)
   at com.test.dbhelper.DBQuery.getWellRiteModels(DBQuery.java:26)     

Answer 1

Try to close and open database whenever you need, as of now just comment below code from checkDataBase() it will work like charm...

mDB.close();

Answer 2

So I solved a similar issue in Oreo which was coming from an optimization of Sqlite in a specific version which was working fine on other devices but not for Oreo. If you have a complex Query, try to use Cross joins to force the order of some tables for the optimizer and exec a "Pragma optimize" before the actual query.

Using EXPLAIN QUERY PLAN revealed that in Oreo, the optimizer was scanning a different table at some point than the other devices. This table was huge so the query took forever. I can't really explain why it did this but according to the SQLite documentation , there might be regressions regarding complex subqueries.

I don't know if it will help you but it worked for me.