android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database- Logic issue in application

Question

I am learning android yet and not expert. I think I have some logic issue in handle my sqllite database. Let me explain what I am doing and what I am getting.

I have one application of quotes. I am providing local database as well online. I have included database file in Assets and copying database from it during first time when user open application from splash screen. After that I am checking last author and last quote number of local database which are stored in user's device and comparing with online database. If there new data in online then I am downloading and storing it in splash screen.

Now my splash screen code for do same is like below

DAO database=DAO.getInstance(this);
int lastAuthor =database.getLastAuthor();
String  updatesUrl = constant.MainUrl + String.valueOf(lastAuthor)+ "/" + String.valueOf(lastQuote);

My DAO class is looking like this

public class DAO {
    private SQLiteDatabase database;
    private DBHandler dbHandler;
    private static final String TABLE_QUOTES = "quotes";
    private static final String TABLE_AUTHORS = "authors";
    private static final String TABLE_SETTINGS = "settings";
    private static final String QU_ID = "_quid";
    private static final String QU_TEXT = "qu_text";
    private static final String QU_AUTHOR = "qu_author";
    private static final String QU_FAVORITE = "qu_favorite";
    private static final String QU_TIME = "qu_time";
    private static final String AU_NAME = "au_name";
    private static final String AU_PICTURE = "au_picture";
    private static final String AU_PICTURE_SDCARD = "au_picture_sdcard";
    private static final String AU_WEB_ID = "au_web_id";

    private static DAO dBObject;
    private final Object lockObj=new Object();

    public static DAO getInstance(Context context){

        if(dBObject==null)dBObject=new DAO(context);
        return  dBObject;

    }


    private DAO(Context context) {
        synchronized (lockObj) {
            dbHandler = new DBHandler(context);
            try {
                dbHandler.createDataBase();
            } catch (IOException e) {
                e.printStackTrace();
            }
            dbHandler.openDataBase();
            open();
        }
    }

    public int getLastAuthor() {

        String query = "SELECT " + AU_WEB_ID + " FROM " + TABLE_AUTHORS
                + " ORDER BY " + AU_WEB_ID + " DESC LIMIT 1";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        int tmp = cursor.getInt(cursor.getColumnIndex(AU_WEB_ID));
        cursor.close();
        return tmp;

    }

    // ==============================================================================

    public int getLastQuote() {
        String query = "SELECT " + QU_ID + " FROM " + TABLE_QUOTES
                + " ORDER BY " + QU_ID + " DESC LIMIT 1";

        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        int tmp = cursor.getInt(cursor.getColumnIndex(QU_ID));

        cursor.close();
        return tmp;
    }

    // ==============================================================================

    public void addAuthor(String au_name, String au_picture, int au_web_id) {
        open();
        ContentValues v = new ContentValues();
        v.put(AU_NAME, au_name);
        v.put(AU_PICTURE, au_picture);
        v.put(AU_PICTURE_SDCARD, 1);
        v.put(AU_WEB_ID, au_web_id);

        database.insert(TABLE_AUTHORS, null, v);

    }

    // ==============================================================================

    public void addQuote(String qu_text, int qu_author, int _quid, String qu_time) {
        open();
        ContentValues v = new ContentValues();
        v.put(QU_TEXT, qu_text);
        v.put(QU_AUTHOR, qu_author);
        v.put(QU_FAVORITE, "0");
        v.put(QU_ID, _quid);
        v.put(QU_TIME, qu_time);

        database.insert(TABLE_QUOTES, null, v);

    }



    // ==============================================================================

    //method changed
    private void open() throws SQLException {
        if(database!=null&&database.isOpen())return; //changed line
        database = dbHandler.getWritableDatabase();

    }

    // ==============================================================================

    public void closeDAO(){
        synchronized (lockObj){
            if(dbHandler!=null)dbHandler.close();
            dbHandler=null;
            database=null;
        }
    }



    public static void dispose(){

        if(dBObject!=null){
            dBObject.closeDAO();
        }
        dBObject=null;
    }

}

And My database handler class looking like this

public class DBHandler extends SQLiteOpenHelper {
    private static String DB_PATH;
    private static String DB_NAME = "xxx";
    private SQLiteDatabase myDataBase;
    private final Context myContext;
    public DBHandler(Context context) {

        super(context, DB_NAME, null, constant.DATABASE_VERSION);
        this.myContext = context;
        DB_PATH = context.getDatabasePath(DB_NAME).toString();
    }
    public void createDataBase() throws IOException {

        boolean dbExist = checkDataBase();

        if (dbExist) {
        } else {

            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() {

        SQLiteDatabase checkDB = null;

        try {
            String myPath = DB_PATH;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

        } catch (SQLiteException e) {

            // database does't exist yet.

        }

        if (checkDB != null) {

            checkDB.close();

        }

        return checkDB != null ? true : false;
    }


    private void copyDataBase() throws IOException {

        InputStream myInput = myContext.getAssets().open(DB_NAME);

        String outFileName = DB_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();

    }

    // ==============================================================================

    public void openDataBase() throws SQLException {


        // Open the database
        String myPath = DB_PATH;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    // ==============================================================================

    @Override
    public synchronized void close() {

        if (myDataBase != null)
            myDataBase.close();

        super.close();

    }

    // ==============================================================================

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    // ==============================================================================

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

    }


}

I am getting errors in logcat like this

Failed to open database '/data/user/0/com.newdeveloper.test/databases/xxx'.
 android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
 at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:835)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:820)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:723)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:692)
at com.newdeveloper.test.utility.DBHandler.checkDataBase(DBHandler.java:69)
at com.newdeveloper.test.utility.DBHandler.createDataBase(DBHandler.java:32)
at com.newdeveloper.test.utility.DAO.<init>(DAO.java:41)
at com.newdeveloper.test.utility.DAO.getInstance(DAO.java:31)
at com.newdeveloper.test.activities.SplashScreensActivity.checkForUpdate(SplashScreensActivity.java:111)
at com.newdeveloper.test.activities.SplashScreensActivity.access$000(SplashScreensActivity.java:41)
at com.newdeveloper.test.activities.SplashScreensActivity$1.onAnimationEnd(SplashScreensActivity.java:98)
at android.view.animation.AnimationSet.getTransformation(AnimationSet.java:400)

However application does not getting crashed and working fine as I need. But This errors are confusing me what I need to change for resolve this errors. I am trying to solve from last two days but not found any working solution for it. Let me know if any senior developer can help me for come out from this. Thanks

https://stackoverflow.com/questions/17034511/android-database-sqlite-sqlitecantopendatabaseexception-unknown-error-code-14 — IntelliJ Amiya, Nov 01 '17 at 05:35
@IntelliJAmiya Thanks for suggestion But I have already checked it and there not any answer have solved my issue. — , Nov 01 '17 at 05:40

score 0 · Answer 1 · edited Mar 25 '18 at 15:54

If you are not already using a bundled database with your apk, you have not actually created the database here in your code.

 @Override
    public void onCreate(SQLiteDatabase db) {
       //you must create the database here
    }

    // ==============================================================================

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
     //Handle the database update here
    }

You need to do something like this.

 @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            db.execSQL(CREATE_STRING);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Log.e(TAG, "Table Created");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_STRING);
        onCreate(db);
        Log.d(TAG, "Table upgraded to Version :" + newVersion);
    }

I would also recommend to start using ROOM Android Architecture Component, which provides much better abstraction level for the Sqlite for the pattern are using.

Hi ! Thanks for answer. I am using bundled database.I have written it in question. Thanks — , Nov 01 '17 at 06:52
ok that seems to be some difference in path of the bundled database then the mentioned, verify using DDMS for the path u get and try calling it using command line or problematically first to see if the fileExists to verify path — Niraj Sanghani, Nov 01 '17 at 06:57

android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database- Logic issue in application

1 Answers1