SQL no such table But table exists

Question

I made a database file in python using sqlite3. The database holds 2 tables ANSWERS,QUESTIONS. I want to use that file in my android app and i followed this post to copy it instead of creating it. I copied the file,implemented the methods as shown,but I get an SQLiteException: no such table: QUESTIONS (code 1 SQLITE_ERROR): , while compiling: select * from QUESTIONS;. I double and triplechecked my database in sqlite,the tables are there,the names are correct. What could be the issue at place? My helper class looks like this:

public class DatabaseHelper extends SQLiteOpenHelper {
    private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
    private static String DB_NAME ="quiz.db"; // Database name
    private static int DB_VERSION = 1; // Database version
    private final File DB_FILE;
    private SQLiteDatabase mDataBase;
    private final Context mContext;
    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        DB_FILE = context.getDatabasePath(DB_NAME);
        this.mContext = context;
    }
    public void createDataBase() throws IOException {
        // If the database does not exist, copy it from the assets.
        boolean mDataBaseExist = checkDataBase();
        if (!mDataBaseExist) {
            this.getReadableDatabase();
            this.close();
            try {
                // Copy the database from assests
                copyDataBase();
                Log.e(TAG, "createDatabase database created");
            } catch (IOException mIOException) {
                throw new Error("ErrorCopyingDataBase");
            }
        }
    }
    // Check that the database file exists in databases folder
    private boolean checkDataBase() {
        return DB_FILE.exists();
    }
    // Copy the database from assets
    private void copyDataBase() throws IOException {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        OutputStream mOutput = new FileOutputStream(DB_FILE);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }
    // Open the database, so we can query it
    public boolean openDataBase() throws SQLException {
        mDataBase = SQLiteDatabase.openDatabase(String.valueOf(DB_FILE), 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 sqLiteDatabase) {}
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}
    public void testQuery() {
        mDataBase.rawQuery("select * from QUESTIONS;",null);
    }
}

And I call it like this:

    helper = new DatabaseHelper(this);
    try {
        helper.createDataBase();
    } catch (IOException e) {
        e.printStackTrace();
    }
    try{
        helper.openDataBase();
    }catch (SQLException e){
        e.printStackTrace();
    }
    helper.testQuery();    

EDIT:

• app/src/main/assets/quiz.db The path from the .db file I physically copied to the project
• data/data/com.rocket.src/databases This path contains quiz.db quiz.db-journal and quiz.dbquiz.db

Answer 1

What could be the issue at place?

This works perfectly now,i honestly can't tell what happened.

The link you posted is an old link and has a flaw, it does not cater for later versions of Android. Later versions default to using WAL (Write-Ahead Logging) where changes are written to a file (database file suffixed with -wal).

If the database file is created via the SQLiteDatabase openDatabaseMethod with CREATE_IF_NECESSARY and then overwritten the WAL file is for the created database NOT the overwritten database (the use of this.getReadableDatabase(); does this in your case). SQLite detects this and considers the database as corrupt. The openDatabase catches this and, to provide a database, creates a new database and hence you end up with a database that only has the sqlite_master table.

All of the above was historically used to circumvent the databases folder/directory (data/data/databases) not existing, when the correct method should have been to create the folder if the database file doesn't exist (using getWritabledatabase or Readable will create the databases directory).

The correct and more efficient way, for any version of Android is to check if the database file exists. If it does then don't copy the asset and return to the usual process.

Otherwise (if the database file does not exist), then get the parent (the databases folder), check if that exists and if not then make the directory. The database file can then be copied from the asset.

Here's a snippet that does the above where DB_NAME is the name of the database file :-

   private void getDatabase(Context context) {
      File dbFile = new File(context.getDatabasePath((DB_NAME)).getPath());
      if (dbFile.exists()) return; // Database found so all done
      // Otherwise ensure that the database directory exists (does not by default until later versions)
      if (!dbFile.getParentFile().exists()) {
         dbFile.getParentFile().mkdirs();
      }
      if (!copyDataBase()) {
         throw new RuntimeException("Unable to copy database from the asset (check the stack-trace).");
      }
   }

This file only approach is more efficient as it does not incur the increased processing required to open and initialise a database, just for it to then be overwritten and re-opened. Instead it just checks the file system.