Database copied from assets is empty

Question

I have a pre-populated SQLite database that I am copying from the apk on install. The database copies successfully but then seems to be empty (I get a SQLite missing table exception on query).

I am using this code to handle the database file operations:

public class ExternalDbOpenHelper extends SQLiteOpenHelper {

    //Path to the device folder with databases
    public static String DB_PATH;

    //Database file name
    public static String DB_NAME;
    public SQLiteDatabase database;
    public final Context context;

    public SQLiteDatabase getDb() {
        return database;
    }

    public ExternalDbOpenHelper(Context context, String databaseName) {
        super(context, databaseName, null, 1);
        this.context = context;
//Write a full path to the databases of your application
        String packageName = context.getPackageName();
        DB_PATH = String.format("//data//data//%s//databases//", packageName);
        DB_NAME = databaseName;
        openDataBase();
    }

    //This piece of code will create a database if it’s not yet created
    public void createDataBase() {
        boolean dbExist = checkDataBase();
        if (!dbExist) {
            this.getReadableDatabase();
            try {
                copyDataBase();
            } catch (IOException e) {
                Log.e(this.getClass().toString(), "Copying error");
                throw new Error("Error copying database!");
            }
        } else {
            Log.i(this.getClass().toString(), "Database already exists");
        }
    }

    //Performing a database existence check
    private boolean checkDataBase() {
        SQLiteDatabase checkDb = null;
        try {
            String path = DB_PATH + DB_NAME;
            checkDb = SQLiteDatabase.openDatabase(path, null,
                    SQLiteDatabase.OPEN_READONLY);
        } catch (SQLException e) {
            Log.e(this.getClass().toString(), "Error while checking db");
        }
        //Android doesn’t like resource leaks, everything should
        // be closed
        if (checkDb != null) {
            checkDb.close();
        }
        return checkDb != null;
    }

    //Method for copying the database
    private void copyDataBase() throws IOException {
        //Open a stream for reading from our ready-made database
        //The stream source is located in the assets
        InputStream externalDbStream = context.getAssets().open(DB_NAME);

        //Path to the created empty database on your Android device
        String outFileName = DB_PATH + DB_NAME;

        //Now create a stream for writing the database byte by byte
        OutputStream localDbStream = new FileOutputStream(outFileName);

        //Copying the database
        byte[] buffer = new byte[1024];
        int bytesRead;
        while ((bytesRead = externalDbStream.read(buffer)) > 0) {
            localDbStream.write(buffer, 0, bytesRead);
        }
        //Don’t forget to close the streams
        localDbStream.close();
        externalDbStream.close();
    }

    public SQLiteDatabase openDataBase() throws SQLException {
        String path = DB_PATH + DB_NAME;
        if (database == null) {
            createDataBase();
            database = SQLiteDatabase.openDatabase(path, null,
                    SQLiteDatabase.OPEN_READWRITE);
        }
        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) {}
}

I have used this exact code in 3 nearly identical apps without problems. Any ideas as to why I may be running into issues with the same code in a new app? The app targets/builds on API level 25.

Answer 1

Below function will help you to copy your database to sd card or any other place. right now it is getting copied on SD card.

public static void copyDbToSdCard(Context context) {
        try {
            File sd = Environment.getExternalStorageDirectory();
            //File data = Environment.getDataDirectory();

            if (sd.canWrite()) {
                String currentDBPath = "/data/data/" + context.getPackageName() + "/databases/JSandeshEmpTracker";
                String backupDBPath = "db_backup.db";
                File currentDB = new File(currentDBPath);
                File backupDB = new File(sd, backupDBPath);

                if (currentDB.exists()) {
                    FileChannel src = new FileInputStream(currentDB).getChannel();
                    FileChannel dst = new FileOutputStream(backupDB).getChannel();
                    dst.transferFrom(src, 0, src.size());
                    src.close();
                    dst.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Answer 2

Without details of the log the issue can only be guessed at.

The first issue may be that the database has been created at some time without the tables. So the first step would be to delete the App's Data or uninstall the App and to then rerun the App.

Note this could occur if the App is run without a file named exactly the same as the database name used, the App crashes with

05-01 20:22:41.988 1309-1309/? E/AndroidRuntime: FATAL EXCEPTION: main java.lang.Error: Error copying database! at .......

The database is however created but is empty (as such). So a subsequent run will not attempt the DB copy as the DB now exists.

If this doesn't resolve the issue then you are either copying a file from the assets folder that doesn't include the mentioned table or that you have used the wrong table name.

To determine what has actually been copied can be accomplished by interrogating the sqlite_master table. However, here's a class that can be copied which has a logDatabaseInfo method that does this and a little more. Simply create CommonSQLiteUtilities.java and copy the code from the answer with the title Addition 1 - logDatabaseInfo and then immediately after the instantiation of the ExternalDbOpenHelper call the logDatabaseInfo method e.g. :-

    ExternalDbOpenHelper extdbhlpr = new ExternalDbOpenHelper(this,"your_database_name");
    CommonSQLiteUtilities.logDatabaseInfo(extdbhlpr.getWritableDatabase());

If you have an empty database logDatabaseInfo would log something along the lines of :-

05-01 20:43:59.424 1706-1706/? I/class mjt.adapters.ExternalDbOpenHelper: Database already exists
05-01 20:43:59.424 1706-1706/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.adapters/databases/my_db
    Database Version = 1
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
05-01 20:43:59.428 1706-1706/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0

• Note the first line is issued by the instantiation of the ExternalDbOpenhelper

The following is an example of the first run (when the DB is copied from the assets folder) and the database is populated :-

05-01 20:50:01.396 1784-1784/mjt.adapters E/SQLiteLog: (14) cannot open file at line 30174 of [00bb9c9ce4]
    (14) os_unix.c:30174: (2) open(//data//data//mjt.adapters//databases//my_db) - 
05-01 20:50:01.404 1784-1786/mjt.adapters D/dalvikvm: GC_CONCURRENT freed 211K, 9% free 6187K/6791K, paused 11ms+1ms, total 27ms
05-01 20:50:01.404 1784-1784/mjt.adapters E/SQLiteDatabase: Failed to open database '//data//data//mjt.adapters//databases//my_db'.
    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:804)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
        at mjt.adapters.ExternalDbOpenHelper.checkDataBase(ExternalDbOpenHelper.java:59)
        at mjt.adapters.ExternalDbOpenHelper.createDataBase(ExternalDbOpenHelper.java:40)
        at mjt.adapters.ExternalDbOpenHelper.openDataBase(ExternalDbOpenHelper.java:98)
        at mjt.adapters.ExternalDbOpenHelper.<init>(ExternalDbOpenHelper.java:35)
        at mjt.adapters.MainActivity.onCreate(MainActivity.java:39)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)
05-01 20:50:01.404 1784-1784/mjt.adapters E/class mjt.adapters.ExternalDbOpenHelper: Error while checking db
05-01 20:50:01.444 1784-1784/mjt.adapters D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.adapters/databases/my_db
    Database Version = 0
    Table Name = user_table Created Using = CREATE TABLE user_table (ID TEXT, OCC INTEGER)
    Table = user_table ColumnName = ID ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = user_table ColumnName = OCC ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = sqlite_sequence Created Using = CREATE TABLE sqlite_sequence(name,seq)
    Table = sqlite_sequence ColumnName = name ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = sqlite_sequence ColumnName = seq ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = tasks Created Using = CREATE TABLE tasks (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT date DATE, new_colum INTEGER DEFAULT 0)
    Table = tasks ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = tasks ColumnName = title ColumnType = TEXT date DATE Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = tasks ColumnName = new_colum ColumnType = INTEGER Default Value = 0 PRIMARY KEY SEQUENCE = 0
    Table Name = Words Created Using = CREATE TABLE Words (Word TEXT, LIKEWORD TEXT)
05-01 20:50:01.448 1784-1784/mjt.adapters D/SQLITE_CSU: Table = Words ColumnName = Word ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = Words ColumnName = LIKEWORD ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0

• Note the stack-trace is due to the way that the database existence is checked (personally I check that the file exists so you don't get the stack trace). So you'd expect this stack-trace when the DB is actually copied from the assets folder.
• As you can see the tables are listed along with the columns.
• Note android_metadata and any table starting with sqlite_ are internal tables (android_metadata is created by SQLiteOpenHelper, sqlite_ tables are SQLite tables).