Caused by: android.database.sqlite.SQLiteException: no such table: BOOK (code 1 SQLITE_ERROR)

Question

We have created the xyz.sqlite file(in which we have table called BOOK) and saved it into "raw" folder and then copying the file and creating the database at run time, which is working fine in all the versions of android up to Android 8. However when I'm running it on Android 9(Pie) on emulator, it's giving me below mentioned error...

Searched for the solution but nothing worked... Any help would be appreciated...

Thanks.

09-13 00:55:49.536 5685-5685/? E/AndroidRuntime: FATAL EXCEPTION: main
                                             Process: com.kosmos.zentrale, PID: 5685
                                             java.lang.RuntimeException: Unable to start activity ComponentInfo{com.kosmos.zentrale/com.kosmos.zentrale.CatalogNew}: android.database.sqlite.SQLiteException: no such table: BOOK (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM BOOK ORDER BY _erscheinungsjahr DESC  , _uniqueID ASC
                                                 at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
                                                 at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
                                                 at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
                                                 at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
                                                 at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
                                                 at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
                                                 at android.os.Handler.dispatchMessage(Handler.java:106)
                                                 at android.os.Looper.loop(Looper.java:193)
                                                 at android.app.ActivityThread.main(ActivityThread.java:6669)
                                                 at java.lang.reflect.Method.invoke(Native Method)
                                                 at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
                                                 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
                                              Caused by: android.database.sqlite.SQLiteException: no such table: BOOK (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM BOOK ORDER BY _erscheinungsjahr DESC  , _uniqueID ASC
                                                 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                 at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
                                                 at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
                                                 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:46)
                                                 at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
                                                 at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
                                                 at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
                                                 at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1294)
                                                 at com.mobisys.android.database.BookDatabase.query(BookDatabase.java:68)
                                                 at com.mobisys.android.database.HelperDatabase.getBooks(HelperDatabase.java:57)
                                                 at com.kosmos.zentrale.CatalogNew.setCursorAdapter(CatalogNew.java:617)
                                                 at com.kosmos.zentrale.CatalogNew.onCreate(CatalogNew.java:120)
                                                 at android.app.Activity.performCreate(Activity.java:7136)
                                                 at android.app.Activity.performCreate(Activity.java:7127)
                                                 at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
                                                 at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
                                                 at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048) 
                                                 at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78) 
                                                 at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108) 
                                                 at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68) 
                                                 at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808) 
                                                 at android.os.Handler.dispatchMessage(Handler.java:106) 
                                                 at android.os.Looper.loop(Looper.java:193) 
                                                 at android.app.ActivityThread.main(ActivityThread.java:6669) 
                                                 at java.lang.reflect.Method.invoke(Native Method) 
                                                 at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493) 
                                                 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858) 

Below is our database creation code

public class BookDatabase {
public static final String DATABASE_NAME = "xyz.sqlite";
public static final int DATABASE_VERSION = 2;
private static String DB_PATH = "/data/data/"+"/databases/";
private OpenHelper gameHelper;
private SQLiteDatabase database;

public BookDatabase(Context context){
    try {
        DB_PATH = "/data/data/"+context.getPackageName()+"/databases/";
        gameHelper=new OpenHelper(context);
        gameHelper.createDataBase();
        openDataBase();
    } catch (IOException e) {
        gameHelper=null;
        e.printStackTrace();
    }
    catch (SQLException e) {
        gameHelper=null;
        e.printStackTrace();
    }
}

public void openDataBase() throws SQLException{
    //Open the database
    String myPath = DB_PATH + DATABASE_NAME;
    database = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}


public long insert(String table, ContentValues values){
    return database.insert(table, null, values);
}

public long delete(String table, String where, String[] whereArgs){
    return database.delete(table, where, whereArgs);
}

public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
    return database.update(table, values, whereClause, whereArgs);
}

public long countRows(String query){
    return DatabaseUtils.longForQuery(database, query, null);
}

public Cursor query(String table,String[] columns, String selection,String[] selectionArgs,String groupBy,String having,String orderBy){
    return database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
}

public void close(){
    database.close();
    gameHelper.close();
}

public Cursor rawQuery(String sql,String[] args){
    return database.rawQuery(sql, args);
}

private class OpenHelper extends SQLiteOpenHelper {
    Context context;

    OpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context=context;
    }

    /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{

        boolean dbExist = checkDataBase();

        if(dbExist){

            //do nothing - database already exist
        }else{

            //By calling this method and empty database will be created into the default system path
            //of your application so we are gonna be able to overwrite that database with our database.

            this.getReadableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }


        }



    }

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){

        try{
            File databasePath = context.getDatabasePath(DATABASE_NAME);
            return databasePath.exists();
        }catch(Exception e){
            return false;
        }

    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{

        //Open your local db as the input stream
        InputStream myInput = context.getResources().openRawResource(R.raw.xyz);

        // Path to the just created empty db
        String outFileName = DB_PATH + DATABASE_NAME;

        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0){
            myOutput.write(buffer, 0, length);
        }

        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
    }

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

}

}

Answer 1

What I'm doing in my app is exactly the same thing, creating a "my_db.sql" -> saving it into the "raw" folder and then copying it and creating my DB at runtime.

I had the same issue. The app seemed to work fine on all lower versions of Android except the Pie. After much deliberation and analysis, what worked for me is adding one single line "db.disableWriteAheadLogging();" in the "onOpen()" method.

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

Answer 2

Here's what worked in my case... Made changes to the overall class structure... Below is the edited new class...

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


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

public class BookDatabase extends SQLiteOpenHelper {
private static String DB_NAME = "xyz.db";
private static String DB_PATH = "";
private static final int DB_VERSION = 1;

private SQLiteDatabase mDataBase;
private final Context mContext;
private boolean mNeedUpdate = false;

public BookDatabase(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    if (android.os.Build.VERSION.SDK_INT >= 17)
        DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
    else
        DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    this.mContext = context;

    copyDataBase();

    this.getReadableDatabase();
}

public void updateDataBase() throws IOException {
    if (mNeedUpdate) {
        File dbFile = new File(DB_PATH + DB_NAME);
        if (dbFile.exists())
            dbFile.delete();

        copyDataBase();

        mNeedUpdate = false;
    }
}


public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
    SQLiteDatabase mDataBase = this.getWritableDatabase();
    return mDataBase.update(table, values, whereClause, whereArgs);
}

public long countRows(String query){
    SQLiteDatabase db = this.getWritableDatabase();

    return DatabaseUtils.longForQuery(db, query, null);
}

public long insert(String table, ContentValues values){

    SQLiteDatabase db = this.getWritableDatabase();

    return db.insert(table, null, values);
}

public long delete(String table, String where, String[] whereArgs){
    SQLiteDatabase mDataBase = this.getWritableDatabase();
    return mDataBase.delete(table, where, whereArgs);
}

public Cursor rawQuery(String sql,String[] args){
    SQLiteDatabase mDataBase = this.getWritableDatabase();
    return mDataBase.rawQuery(sql, args);
}


public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){

    SQLiteDatabase db = this.getWritableDatabase();

    return db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
}

private boolean checkDataBase() {
    File dbFile = new File(DB_PATH + DB_NAME);
    return dbFile.exists();
}

private void copyDataBase() {
    if (!checkDataBase()) {
        this.getReadableDatabase();
        this.close();
        try {
            copyDBFile();
        } catch (IOException mIOException) {
            throw new Error("ErrorCopyingDataBase");
        }
    }
}

private void copyDBFile() throws IOException {
    //InputStream mInput = mContext.getAssets().open(DB_NAME);
    InputStream mInput = mContext.getResources().openRawResource(R.raw.xyz);
    OutputStream mOutput = new FileOutputStream(DB_PATH + DB_NAME);
    byte[] mBuffer = new byte[1024];
    int mLength;
    while ((mLength = mInput.read(mBuffer)) > 0)
        mOutput.write(mBuffer, 0, mLength);
    mOutput.flush();
    mOutput.close();
    mInput.close();
}

public boolean openDataBase() throws SQLException {
    mDataBase = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.CREATE_IF_NECESSARY);
    return mDataBase != null;
}

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

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion > oldVersion)
        mNeedUpdate = true;
}
}

Answer 3

Use following method on SQLiteOpenHelper class

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
        db.disableWriteAheadLogging();
    }
}

Answer 4

If you are sure about the integrity of the database and the correctness of your code, then the most probable cause of the error could be because your asset file was compressed when the APK was built.

To disable automatic compressing of your database files, include the following code snippet in your app build.gradle file within the android block.

aaptOptions {
   noCompress 'db'
}

Hope this solved your problem...