Android P - 'SQLite: No Such Table Error' after copying database from assets in android version 9

Question

This DBHelper code is working in all version but not working android version 9

public class DBHelper extends SQLiteOpenHelper {

    private static int db_version = 1;
    private static String db_name = "quote_db";
    private String db_path;
    private SQLiteDatabase db;
    private final Context con;

    public DBHelper(Context con) {
        super(con, db_name, null, db_version);
        // TODO Auto-generated constructor stub
        this.con = con;
        db_path=con.getDatabasePath(db_name).getPath();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {           
    }

    public void createDB() throws IOException {

        this.getReadableDatabase();
        copyDB();
        Log.d("Database", "copy databse");    
    }

    private boolean checkDB() {

        SQLiteDatabase cDB = null;
        try {
            cDB = SQLiteDatabase.openDatabase(db_path+db_name, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (SQLiteException e) {    
        }
        if (cDB != null) {
            cDB.close();
        }
        return cDB != null ? true : false;
    }

    private void copyDB() throws IOException {
        InputStream inputFile = con.getAssets().open(db_name);
        String outFileName = db_path + db_name;
        OutputStream outFile = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputFile.read(buffer)) > 0) {
            outFile.write(buffer, 0, length);
        }
        outFile.flush();
        outFile.close();
        inputFile.close();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS var_guj");
        db.execSQL("DROP TABLE IF EXISTS var_eng");
        onCreate(db);
        Log.d("DB Upgrade", "Yes Upgrade");
    }

    //get category list from database
    public Cursor get_categorydatabyid(String colum_name,int cateoryId) {

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("SELECT id,date,month,"+colum_name+",day FROM quote where category_id="+cateoryId+" ORDER BY id",null);
            if (cursor != null) {
                cursor.moveToFirst();
                db.close();
                return cursor;
            }
        } catch (Exception e) {
            db.close();
            Log.d("Error-", ""+e);
            Toast.makeText(con, "Compai-" + e, Toast.LENGTH_LONG).show();
        }
        cursor.close();
        db.close();
        return cursor;    
    }

    public int getmaxid(int todate,int tomonth) {
        int maxID = 0;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("SELECT id FROM quote WHERE date="+todate+" and month="+tomonth+"", null);
            if (cursor != null) {
                cursor.moveToFirst();
                maxID = cursor.getInt(0);
                db.close();
                return maxID;
            }
        } catch (Exception e) {
            db.close();
            Log.d("Error-", ""+e);
            Toast.makeText(con, "Compai-" + e, Toast.LENGTH_LONG).show();
        }
        cursor.close();
        db.close();
        return maxID;    
    }    
}

The error is

Compai-android.database.sqlite.SQLite Exception:no such table : quote(code 1 SQLITE_ERROR):,while compiling:SELECT id FROM quote WHERE date=14 and month=10

score 0 · Answer 1 · answered Oct 14 '19 at 07:55

0

Add this.close(); after this.getReadableDatabase(); in your createDB()

answered Oct 14 '19 at 07:55

Saurabh Thorat

18,131
5
53
70

MikeT · Accepted Answer · 2019-10-14T12:38:38.450

This is caused because of the use of this.getReadableDatabase() before the copy.

This creates a database and since Android 9 the database is opened, by default, in WAL mode. This results in two files -wal and -shm (each preceded with the database file name) that are owned by the database which is overwritten, not by database that overwrites the former. This anomaly is detected and results in an empty database being returned and hence the table not found.

There are a few get-arounds but the suggested and most efficient way is to not open the database but to instead check if the file exists.

e.g. :-

private boolean checkDB() {

    File cDB = new File(con.getDatabasePath(db_name).getPath());
    if (cDB.exists()) return true;
    if (!cDB.getParentFile().exists()) {
        cDB.getParentFile().mkdirs();
    }
    return false;
}

Note this additionally gets around the issue why opening the database was likely introduced instead of the the file check; that is checking the file, without creating the parent directory (the databases folder) would subsequently fail when attempting the copy of the database file with an ENOENT cannot open file or directory exception.
databases are stored, unless otherwise specified, at data/data/the_package_name/databases/. However, when an App is installed only data/data/package_name/ exists, there is no databases folder/directory.
Opening the database as an SQLiteDatabase is quite expensive resource wise, a fair bit has to be done, the header checked, the schema generated and written to disk or read from disk into memory, the creation of the android_metadata table, the writing to the -wal and -shm files in addition to actually getting a File.
The only flaw as such with this method is that if the asset weren't a valid database in which case a Corrupt Database exception would occur. If this is an issue then you can easily check the first 16 bytes of the header as per Database File Format.

In addition to the above you should also remove/comment out this.getReadableDatabase(); in the createDB method. As this will also result in the same issue.

A more in-depth answer

@AmitYadav irrespective, without the existence of a database, the net result is the same. getReadableDatabase (or Writable) before the copy will leave the -wal and -shm files which will then result, if not deleted, in a database that has two tables sqlite_master and android_metadata and none of the expected tables and the resultant table not found. The example is that, an example that you could tailor to resolve the issue. — MikeT, Oct 14 '19 at 12:34
@AmitYadav, just noticed, you will have issue with `cursor.close(); db.close(); return cursor;` in **get_categorydatabyid**. If you close the Cursor or the database, you will be unable to use the returned Cursor (Index -1 for 0 rows if I recall correctly). Just return the Cursor. It is also inefficient to close the database better to always leave it open. — MikeT, Oct 14 '19 at 13:03

score 0 · Answer 3 · answered Apr 28 '23 at 04:21

0

In Android studio , go to View --> Tool Windows --> Device File Explorer Find this path --> "/data/data/yourpackagename/databases/" then manually upload the updated db. It works.

answered Apr 28 '23 at 04:21

roo_agnes

1

Android P - 'SQLite: No Such Table Error' after copying database from assets in android version 9

3 Answers3