SQLite: No Such Table Error Android P problem

Question

Until Android P everything worked fine with my code but now in Android P I'm having a lot of problems working with databases.

I have .db SQLite database files stored on assets folder. I was importing them into the database space of Android and it worked fine until Android P. In android P I'm getting this exception: SQLite: No Such Table Error

I searched here and found this, and I tried to apply the accepted answer: Android P - 'SQLite: No Such Table Error' after copying database from assets

The problem is that now I'm having another problem and It still doesn't works. Now I'm having the problem that every time I try to check if the database already exists, this code always returns true, so I never create the database. Even with the database not created, even with the app recently installed:

private boolean checkIfDBExists() {
    File dbFile = new File(DB_COMPLETE_PATH);
    return dbFile.exists();
}

My source code is the source code of the accepted answer in the previously quoted Stack Overflow question.

The new Android P required way of getting the DB path:

public static String getDatabasePath(String fileNname){
    MySQLiteOpenHelper helper = new MySQLiteOpenHelper(ApplicationContextProvider.getContext(), fileNname);
    SQLiteDatabase database = helper.getReadableDatabase();
    String path = database.getPath();
    database.close();
    return path;
}

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
    public MySQLiteOpenHelper(Context context, String databaseName) {
        super(context, databaseName, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.disableWriteAheadLogging();
    }
}

My DBHelper class:

public class DbHelper extends SQLiteOpenHelper{ 
    private  String DB_NAME;
    private  String DB_COMPLETE_PATH;
    private SQLiteDatabase mDataBase;
    private static final int DATABASE_VERSION = 1;
    
    public DbHelper(String name) throws IOException {
        super(ApplicationContextProvider.getContext(), name+".db", null, DATABASE_VERSION);
        this.DB_NAME = name+".db";
        this.DB_COMPLETE_PATH = Util.getDatabasePath(DB_NAME);

        boolean mustOverWriteDB; 
        
        if (checkIfDBExists()==false) {
            createDataBase();
        }
        
        openDataBase();
    }

    private void createDataBase() throws IOException {
        this.getReadableDatabase();
        try {           
            copyDataBase();            
        } catch (IOException e) {           
            throw new RuntimeException(e);
        }
    }

    public void copyDataBase() throws IOException {
        InputStream myInput = App.getInstance().getAssetsFile(DB_NAME);
        String outFileName = DB_COMPLETE_PATH;
        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();
    }
}

Northernmost

Answer 1

Arg finally solved it adding this.close(); after this.getReadableDatabase();

That openned connection was generating the original no such table exception

So I used Util.getDatabasePath(DB_NAME); instead of the complex propossed solution in Android P - 'SQLite: No Such Table Error' after copying database from assets and now the code is much more simpler

Thanks a lot to @LifeStyle who found the real problem.

Now the code is much more simpler:

public static String getDatabasePath(String fileNname){
    return ApplicationContextProvider.getContext().getDatabasePath(fileNname).getAbsolutePath();
}

public class DbHelper extends SQLiteOpenHelper{
    private String DB_NAME;
    private String DB_COMPLETE_PATH;
    private SQLiteDatabase mDataBase;
    private static final int DATABASE_VERSION = 1;

    public DbHelper(String name) throws IOException {
        super(ApplicationContextProvider.getContext(), name+".db", null, DATABASE_VERSION);
        this.DB_NAME = name+".db";
        this.DB_COMPLETE_PATH = Util.getDatabasePath(DB_NAME);

        if (checkIfDBExists()==false){
            createDataBase();
        }

        openDataBase();
    }

    private void createDataBase() throws IOException {
        this.getReadableDatabase();
        this.close();
        try {           
            copyDataBase();            
        } catch (IOException e) {           
            throw new RuntimeException(e);
        }
    }

    private boolean checkIfDBExists() {
        File dbFile = new File(DB_COMPLETE_PATH);
        return dbFile.exists();
    }
}

Northernmost

It doesn't work properly in all Android Pie devices. Can you give any other suggestions? – Pairs

Even after adding this, I am still the same issue. Can you suggest anything else – Pliant

Answer 2

In your DBHelper class, you are creating the DB before you testing the if DB exists in method Util.getDatabasePath(DB_NAME). This line creating the DB

this.DB_COMPLETE_PATH = Util.getDatabasePath(DB_NAME);

By calling this method You sql open helper creates the databse(in your case empty because in OnCreate() method notthing happens).

If you are storing your DB in default app's db path then you can check if DB exist by next snippet:

File f = context.getDatabasePath("my_database.db");
if(f.exists()) {
   //Your code if DB exist
} else {
   //Your code if DB doesn't exist
}

And the better place to disable WAL is onConfigure() method in SQLiteOpenHelper

Lenoir

But in Android P it's mandatory to use that code to get the path of the DB, so it's a vicious circle. How can I get the path of the DB in Android P without creating the DB? – Northernmost

context.getDatabasePath("my_database.db); – Lenoir

as you can see on my linked question "getDatabasePath" is a problem generator in Android P because it generates open connections which make the app to crash – Northernmost

if i use getDatabasePath I get "SQLite: No Such Table Error" exception, I told in the question – Northernmost

but then It's a vicious circle. How can I get the path of the DB in Android P without creating the DB and without using getDatabasePath? – Northernmost

This is a snippet, tested on Android P with targetAPi=28 . File f = getApplicationContext().getDatabasePath("my_db.db"); if(f.exists()) { Log.i("DB_TEST", "Exist"); } else { Log.i("DB_TEST", "Doesn't exist"); } . You can create test app and put this code into onCreate() of the defaut acticity and will see that there is no crashes it is valid functionality, Your issue is your implementation of Util.getDatabasePAth() method – Lenoir

you don't understand me. If i use getDatabasePath, later when I try to access the database to read content, I get a crash. It's docummented here: #50477282 read the accepted answer – Northernmost

Post your exception here, please. According to link you provided, the issue is in WAL mode(Write Ahead Logging) . to disable it you may call db.disableWriteAheadLogging(); by overriding method onConfigure(SQLiteDatabase db) in your SQLiteOpenHelper class – Lenoir

it crashes, you are not reading content from your database, because of that is not crashing for you. Please read the question and the answer i told you – Northernmost

PLease post your exception and a snippet how you are copying the database from assets. – Lenoir

The exception is "no such table" and it's described on the linked question I posted in my question text. I added to my question the createDataBase() method for you, check it please. Also I readed your link but I don't know how to create the android_metadata table that guy is talking about. And that guy doesn't show how to create it – Northernmost

I meant exceptions staCktrace. Create as you created other tables in your DATABASE – Lenoir

finally solved it adding a simple line.. but thanks a lot – Northernmost

Answer 3

add this path your database

String dbPath = "/data/data/" + mContext.getPackageName() + "/cache/" + mDataBaseName;

Oubre

Answer 4

Actually i have taken object of SQLiteDatabase as static so i was getting this issue.

Pliant

Answer 5

My issues with Android P got solved by adding this.close(); after this.getReadableDatabase();

`private void createDataBase() throws IOException {
    this.getReadableDatabase();
    this.close(); 
    try {           
        copyDataBase();            
    } catch (IOException e) {           
        throw new RuntimeException(e);
    }
}`

[https://mcmap.net/q/212527/-android-p-39-sqlite-no-such-table-error-39-after-copying-database-from-assets][1]

Staceestacey