CODEWITHSUNDEEP
androidandroid-sqliteandroid-contentprovider
SametSahin
SametSahin

Reputation: 661

How to fix SQLiteException thrown while trying to make a query?

I have a dictionary app works totally offline using a database I've provided. To be able to use the database, I've applied a solution where I've seen on a developer's blog. You can refer to it from this link. The solution is basically copying the database from the assets folder and using it. It works without a problem on most of the devices but some users experiencing crashes while they're trying to query the database. I've attached the stack trace I've received with the crash report sent from Asus ZenFone 5 (ZE620KL) (ASUS_X00QD). My question is: Is there a problem on the practice I'm applying (Using the existing database located in the assets folder)? What can I do to avoid this exception?

  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.tur_cirdictionary.turkish_circassiandictionary.data.WordProvider.query (WordProvider.java:52)
  at android.content.ContentProvider.query (ContentProvider.java:1064)
  at android.content.ContentProvider.query (ContentProvider.java:1156)
  at android.content.ContentProvider$Transport.query (ContentProvider.java:241)
  at android.content.ContentResolver.query (ContentResolver.java:809)
  at android.content.ContentResolver.query (ContentResolver.java:758)
  at com.tur_cirdictionary.turkish_circassiandictionary.MainActivity.showSuggestionsForQuery (MainActivity.java:245)
  at com.tur_cirdictionary.turkish_circassiandictionary.MainActivity.access$000 (MainActivity.java:37)
  at com.tur_cirdictionary.turkish_circassiandictionary.MainActivity$2.onQueryTextChange (MainActivity.java:178)
  at android.widget.SearchView.onTextChanged (SearchView.java:1250)
  at android.widget.SearchView.access$2100 (SearchView.java:98)
  at android.widget.SearchView$10.onTextChanged (SearchView.java:1776)
  at android.widget.TextView.sendOnTextChanged (TextView.java:9784)
  at android.widget.TextView.handleTextChanged (TextView.java:9881)
  at android.widget.TextView$ChangeWatcher.onTextChanged (TextView.java:12539)
  at android.text.SpannableStringBuilder.sendTextChanged (SpannableStringBuilder.java:1263)
  at android.text.SpannableStringBuilder.replace (SpannableStringBuilder.java:575)
  at android.text.SpannableStringBuilder.replace (SpannableStringBuilder.java:506)
  at android.text.SpannableStringBuilder.replace (SpannableStringBuilder.java:36)
  at android.view.inputmethod.BaseInputConnection.replaceText (BaseInputConnection.java:843)
  at android.view.inputmethod.BaseInputConnection.commitText (BaseInputConnection.java:197)
  at com.android.internal.widget.EditableInputConnection.commitText (EditableInputConnection.java:183)
  at com.android.internal.view.IInputConnectionWrapper.executeMessage (IInputConnectionWrapper.java:341)
  at com.android.internal.view.IInputConnectionWrapper$MyHandler.handleMessage (IInputConnectionWrapper.java:85)
  at android.os.Handler.dispatchMessage (Handler.java:106)
  at android.os.Looper.loop (Looper.java:198)
  at android.app.ActivityThread.main (ActivityThread.java:6732)
  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)

WordContract.java

package com.tur_cirdictionary.turkish_circassiandictionary.data;

import android.content.ContentResolver;
import android.net.Uri;
import android.provider.BaseColumns;

public final class WordContract {

    public static final String CONTENT_AUTHORITY =
            "com.tur_cirdictionary.turkish_circassiandictionary";

    public static final Uri BASE_CONTENT_URI = Uri.parse("content://" + CONTENT_AUTHORITY);

    public static final String PATH_WORDS = "words";

    private WordContract() {}

    public static class WordEntry implements BaseColumns {

        public static final Uri CONTENT_URI = Uri.withAppendedPath(BASE_CONTENT_URI, PATH_WORDS);

        public static final String CONTENT_LIST_TYPE = ContentResolver.CURSOR_DIR_BASE_TYPE + "/" +
                CONTENT_AUTHORITY + PATH_WORDS;

        public static final String CONTENT_ITEM_TYPE = ContentResolver.CURSOR_ITEM_BASE_TYPE + "/" +
                CONTENT_AUTHORITY + "/" + PATH_WORDS;

        public static final String TABLE_NAME = "words";
        public static final String _ID = BaseColumns._ID;
        public static final String COLUMN_NAME_CIRCASSIAN = "circassian";
        public static final String COLUMN_NAME_TURKISH = "turkish";
    }
}

WordDbHelper.java

package com.tur_cirdictionary.turkish_circassiandictionary.data;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.tur_cirdictionary.turkish_circassiandictionary.data.WordContract.WordEntry;

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

public class WordDbHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static String DATABASE_PATH
            = "/data/data/com.tur_cirdictionary.turkish_circassiandictionary/databases/";
    private static final String DATABASE_NAME = "Cir_Tur.sqlite";
    private SQLiteDatabase database;
    private final Context context;

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

    public void createDatabase() {
        boolean dbExist = checkDataBase();
        if (dbExist) {
            //Cool. Don't do anything.
        } else {
            try {
                this.getReadableDatabase();
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String path = DATABASE_PATH + DATABASE_NAME;
            checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        } catch (SQLException e) {
            //Database does not exist
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null;
    }

    private void copyDataBase() throws IOException {
        InputStream inputStream = context.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_NAME;
        OutputStream outputStream = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputStream.read(buffer)) > 0) {
            outputStream.write(buffer, 0, length);
        }
        outputStream.flush();
        outputStream.close();
        inputStream.close();
    }

    public SQLiteDatabase openDatabase() {
        String path = DATABASE_PATH + DATABASE_NAME;
        database = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        return database;
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

    private static final String SQL_CREATE_WORDS =
            "CREATE TABLE  " + WordEntry.TABLE_NAME + " ("
                    + WordEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + WordEntry.COLUMN_NAME_CIRCASSIAN + " TEXT, "
                    + WordEntry.COLUMN_NAME_TURKISH + " TEXT)";

    private static final String SQL_DELETE_WORDS =
            "DROP TABLE IF EXISTS " + WordContract.WordEntry.TABLE_NAME;
}

WordProvider.java

package com.tur_cirdictionary.turkish_circassiandictionary.data;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.tur_cirdictionary.turkish_circassiandictionary.data.WordContract.WordEntry;

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

public class WordDbHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static String DATABASE_PATH
            = "/data/data/com.tur_cirdictionary.turkish_circassiandictionary/databases/";
    private static final String DATABASE_NAME = "Cir_Tur.sqlite";
    private SQLiteDatabase database;
    private final Context context;

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

    public void createDatabase() {
        boolean dbExist = checkDataBase();
        if (dbExist) {
            //Cool. Don't do anything.
        } else {
            try {
                this.getReadableDatabase();
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String path = DATABASE_PATH + DATABASE_NAME;
            checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        } catch (SQLException e) {
            //Database does not exist
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null;
    }

    private void copyDataBase() throws IOException {
        InputStream inputStream = context.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_NAME;
        OutputStream outputStream = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputStream.read(buffer)) > 0) {
            outputStream.write(buffer, 0, length);
        }
        outputStream.flush();
        outputStream.close();
        inputStream.close();
    }

    public SQLiteDatabase openDatabase() {
        String path = DATABASE_PATH + DATABASE_NAME;
        database = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        return database;
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

    private static final String SQL_CREATE_WORDS =
            "CREATE TABLE  " + WordEntry.TABLE_NAME + " ("
                    + WordEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + WordEntry.COLUMN_NAME_CIRCASSIAN + " TEXT, "
                    + WordEntry.COLUMN_NAME_TURKISH + " TEXT)";

    private static final String SQL_DELETE_WORDS =
            "DROP TABLE IF EXISTS " + WordContract.WordEntry.TABLE_NAME;

}

MainActivity.java

package com.tur_cirdictionary.turkish_circassiandictionary.data;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.tur_cirdictionary.turkish_circassiandictionary.data.WordContract.WordEntry;

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

public class WordDbHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static String DATABASE_PATH
            = "/data/data/com.tur_cirdictionary.turkish_circassiandictionary/databases/";
    private static final String DATABASE_NAME = "Cir_Tur.sqlite";
    private SQLiteDatabase database;
    private final Context context;

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

    public void createDatabase() {
        boolean dbExist = checkDataBase();
        if (dbExist) {
            //Cool. Don't do anything.
        } else {
            try {
                this.getReadableDatabase();
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String path = DATABASE_PATH + DATABASE_NAME;
            checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        } catch (SQLException e) {
            //Database does not exist
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null;
    }

    private void copyDataBase() throws IOException {
        InputStream inputStream = context.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_NAME;
        OutputStream outputStream = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputStream.read(buffer)) > 0) {
            outputStream.write(buffer, 0, length);
        }
        outputStream.flush();
        outputStream.close();
        inputStream.close();
    }

    public SQLiteDatabase openDatabase() {
        String path = DATABASE_PATH + DATABASE_NAME;
        database = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        return database;
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

    private static final String SQL_CREATE_WORDS =
            "CREATE TABLE  " + WordEntry.TABLE_NAME + " ("
                    + WordEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + WordEntry.COLUMN_NAME_CIRCASSIAN + " TEXT, "
                    + WordEntry.COLUMN_NAME_TURKISH + " TEXT)";

    private static final String SQL_DELETE_WORDS =
            "DROP TABLE IF EXISTS " + WordContract.WordEntry.TABLE_NAME;
}

Her is the link to the whole project if necessary.

Upvotes: 0

Views: 136

Answers (2)

MikeT
MikeT

Reputation: 56938

Is there a problem on the practice I'm applying (Using the existing database located in the assets folder)?

Not really as this is a recommended and frequently used technique. However, if implemented wrongly there can be issues especially now.

According to a quick search the device's OS is Android 8.0 (Oreo), upgradable to Android 9.0 (Pie); ZenUI 5

There is certainly 1 issue that will come to light when using Android Pie, which could well be the issue you have encountered.

The cause is the use of this.getReadableDatabase(); prior to the call to the copyDatabase method.

As Android Pie turns on WAL (Write-ahead logging) by default two files the -wal and the -shm files are created. These remaining cause a conflict as they then do not match the database that has just been copied. I believe that the end result is that the copied database is deleted and a brand new one created so as to provide a usable database. As such no tables nor underlying data exists. This often results in a table not found error/exception as typically when an attempt is made to access the data the table doesn't exist.

What can I do to avoid this exception?

Fix 1

The simple fix, but not recommended fix, is to override the SQLiteOpenhelper's onConfigure method in WordDbHelper.java to call the disableWriteAheadLogging method.

However, applying this fix means that you forsake the advantages of Write-Ahead Logging.

Fix 2

The correct fix is to not use the getReadableDatabase before the copy. This appears to have been a historical fix of a simple issue. That is the data/data/the_package/ directory for a package, for a new installation of an App, does not have a databases directory. So getWritableDatabase (or getReabableDatabase which gets a writable database if it can) creates the directory and also the database which is then overwritten.

What an App should do is check to see if the directory exists and then create it, this could all be done in the checkDataBase method using something like :-

private boolean checkDataBase() {

    File db = new File(DATABASE_PATH + DATABASE_NAME);
    if(db.exists()) return true;
    File dir = new File(db.getParent());
    if (!dir.exists()) {
        dir.mkdirs();
    }
    return false;
}
  • note that you should additionally remove the line this.getReadableDatabase(); from the createDatabase method.

Although probably not an issue it is much wiser to NOT ever hard code the database path but to instead utilise the Context class' getDatabasePath method (if using the standard/recommended location for the database). e.g. 

private boolean checkDataBase() {

    File db = new File(context.getDatabasePath(DBNAME)); //<<<<<<<<<< CHANGED
    if(db.exists()) return true;
    File dir = new File(db.getParent());
    if (!dir.exists()) {
        dir.mkdirs();
    }
    return false;
}

Note

From the provided stack-trace and also that MainActivity.java, WordProvidr.java and WordDBHelper have what appears to be identical code, it's impossible to determine the exact cause. As such the above is a likely cause or a cause that would likely happen.

Upvotes: 2

Michael Dougan
Michael Dougan

Reputation: 1698

I don't know if this is the cause of your problem or not, but when I had similar intermittent issues with SQLite, it turned out to be that Android (or Eclipse) was compressing the database file when compiled with the Assets folder. The solution was to give the file an extension of a file type that Android would not compress, such as an image file with a .jpg extension. It sounds weird, but I put my database file in Assets as mydb.jpg and then in the CopyDatabase method, it changes the extension to mydb.db and stores it in the /data/data/databases app folder.

Upvotes: 0

Related Questions