Getting a prepopulated database located in the Assets folder into the data/data/mypackage/databases

Question

I have been trying to follow the example: http://blog.softeq.com/2012/12/using-pre-populated-sqlite-database-in.html

This example is supposed to show you how to open a prepopulated database stored in the Assets folder.

There have been threads created related to this topic and I have tried the suggestions made, but its not helping me to figure out why the program doesn't open the database file. What am I doing wrong? Here is what I'm getting in the first few lines of LogCat:

01-13 15:54:33.171: E/Trace(30747): error opening trace file: No such file or directory (2)
01-13 15:54:33.171: D/ActivityThread(30747): setTargetHeapUtilization:0.25
01-13 15:54:33.171: D/ActivityThread(30747): setTargetHeapIdealFree:8388608
01-13 15:54:33.171: D/ActivityThread(30747): setTargetHeapConcurrentStart:2097152
01-13 15:54:33.251: D/AbsListView(30747): Get MotionRecognitionManager
01-13 15:54:33.261: E/SQLiteLog(30747): (14) cannot open file at line 30245 of [00bb9c9ce4]
01-13 15:54:33.261: E/SQLiteLog(30747): (14) os_unix.c:30245: (2) open(/data/data/com.example.prepopdb/databases/yourdb.db) - 
01-13 15:54:33.261: E/SQLiteDatabase(30747): Failed to open database '/data/data/com.example.prepopdb/databases/yourdb.db'.
01-13 15:54:33.261: E/SQLiteDatabase(30747): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:278)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:217)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:464)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:186)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:178)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at com.example.prepopdb.ExternalDbOpenHelper.checkDataBase(ExternalDbOpenHelper.java:62)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at com.example.prepopdb.ExternalDbOpenHelper.createDataBase(ExternalDbOpenHelper.java:43)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at com.example.prepopdb.ExternalDbOpenHelper.openDataBase(ExternalDbOpenHelper.java:97)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at com.example.prepopdb.ExternalDbOpenHelper.<init>(ExternalDbOpenHelper.java:37)
01-13 15:54:33.261: E/SQLiteDatabase(30747):    at com.example.prepopdb.PrepopSqliteDbActivity.onCreate(PrepopSqliteDbActivity.java:35)

I created the test database with SQLite Database Browser as mentioned in the above link with the exception of my database extension being ".db" instead of ".sqlite3". (Though I have tried using ".sqlite3" as well, with similar/same errors).

Here is the code for my PrepopSqliteDbActivity:

package com.example.prepopdb;

import com.example.prepopdb.ExternalDbOpenHelper;
import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class PrepopSqliteDbActivity extends ListActivity {

private static final String DB_NAME = "yourdb.db";

// Database field names
private static final String TABLE_NAME = "friends";
private static final String FRIEND_ID = "_id";
private static final String FRIEND_NAME = "name";

private SQLiteDatabase database;
private ListView listView;
private ArrayList<String> friends;

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    ExternalDbOpenHelper dbOpenHelper = new ExternalDbOpenHelper(this,
            DB_NAME);
    database = dbOpenHelper.openDataBase(); // Database is open

    /*SOME OTHER CODE HERE*/
}

Here is the code for my ExternalDbOpenHelper:

package com.example.prepopdb;

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

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class ExternalDbOpenHelper extends SQLiteOpenHelper {

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

// Database filename
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 the full path to the databases of your application
    String packageName = context.getPackageName();
    DB_PATH = String.format("%s/data/%s/databases/", Environment.getDataDirectory(), packageName);
    DB_NAME = databaseName;
    openDataBase();

}

// Create a database if its 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");
    }

    if (checkDb != null){
        checkDb.close();
    }
    return checkDb !=null;
}

private void copyDataBase() throws IOException {
    //Open a stream for reading, 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;

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

    //Copy the database
    byte[] buffer = new byte[1024];
    int bytesRead;
    while((bytesRead = externalDbStream.read(buffer)) > 0){
        localDbStream.write(buffer, 0, bytesRead);
    }

    //Close the streams
    localDbStream.close();
    externalDbStream.close();
}

SQLiteDatabase openDataBase() throws SQLException {
    String path = DB_PATH + DB_NAME;
    if (database == null) {
        createDataBase();
        database = SQLiteDatabase.openDatabase(path, null,
                SQLiteDatabase.OPEN_READWRITE);
        //database = getWritableDatabase();
    }
    return database;
}

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

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

}
}

UPDATE

Thank you Carlos. I agree its not a good idea to hardcode the path as it might be different device to device. I made the changes as you suggested, however I'm still getting the same error. I've verified that the yourdb.db file has read/write permissions for all levels.

I mentioned this in a previous comment, but could any of this be happening because I'm using a non-rooted phone? Could the permission of "yourdb.db" possibly change after copying it from the Asssets folder into /data/data/com.example.prepopdb/databases/ and then my program attempting to open it? I know how to change the permission using adb, but how do go about doing this from within the program?

Here's what my Logcat says:

01-14 12:18:02.240: E/Trace(18167): error opening trace file: No such file or directory (2)
01-14 12:18:02.240: D/ActivityThread(18167): setTargetHeapUtilization:0.25
01-14 12:18:02.240: D/ActivityThread(18167): setTargetHeapIdealFree:8388608
01-14 12:18:02.240: D/ActivityThread(18167): setTargetHeapConcurrentStart:2097152
01-14 12:18:02.360: D/AbsListView(18167): Get MotionRecognitionManager
01-14 12:18:02.370: E/SQLiteLog(18167): (14) cannot open file at line 30245 of [00bb9c9ce4]
01-14 12:18:02.370: E/SQLiteLog(18167): (14) os_unix.c:30245: (2) open(/data/data/com.example.prepopdb/databases/yourdb.db) - 
01-14 12:18:02.370: E/SQLiteDatabase(18167): Failed to open database '/data/data/com.example.prepopdb/databases/yourdb.db'.
01-14 12:18:02.370: E/SQLiteDatabase(18167): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:278)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:217)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:464)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:186)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:178)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.example.prepopdb.ExternalDbOpenHelper.checkDataBase(ExternalDbOpenHelper.java:63)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.example.prepopdb.ExternalDbOpenHelper.createDataBase(ExternalDbOpenHelper.java:45)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.example.prepopdb.ExternalDbOpenHelper.openDataBase(ExternalDbOpenHelper.java:97)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.example.prepopdb.ExternalDbOpenHelper.<init>(ExternalDbOpenHelper.java:39)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.example.prepopdb.PrepopSqliteDbActivity.onCreate(PrepopSqliteDbActivity.java:35)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.Activity.performCreate(Activity.java:5048)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1094)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2052)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2113)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.ActivityThread.access$700(ActivityThread.java:139)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1224)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.os.Handler.dispatchMessage(Handler.java:99)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.os.Looper.loop(Looper.java:137)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at android.app.ActivityThread.main(ActivityThread.java:4918)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at java.lang.reflect.Method.invokeNative(Native Method)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at java.lang.reflect.Method.invoke(Method.java:511)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1004)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:771)
01-14 12:18:02.370: E/SQLiteDatabase(18167):    at dalvik.system.NativeStart.main(Native Method)
01-14 12:18:02.370: E/class com.example.prepopdb.ExternalDbOpenHelper(18167): Error while checking db
01-14 12:18:02.480: D/libEGL(18167): loaded /system/lib/egl/libEGL_adreno200.so
01-14 12:18:02.500: D/libEGL(18167): loaded /system/lib/egl/libGLESv1_CM_adreno200.so
01-14 12:18:02.500: D/libEGL(18167): loaded /system/lib/egl/libGLESv2_adreno200.so
01-14 12:18:02.540: I/Adreno200-EGLSUB(18167): <ConfigWindowMatch:2087>: Format RGBA_8888.
01-14 12:18:02.560: E/(18167): <s3dReadConfigFile:75>: Can't open file for reading
01-14 12:18:02.560: E/(18167): <s3dReadConfigFile:75>: Can't open file for reading
01-14 12:18:02.560: D/OpenGLRenderer(18167): Enabling debug mode 0
01-14 12:18:21.380: W/IInputConnectionWrapper(18167): beginBatchEdit on inactive InputConnection
01-14 12:18:21.380: W/IInputConnectionWrapper(18167): endBatchEdit on inactive InputConnection

Agreed. Please use `SQLiteAssetHelper`. The code that you are using is dreadful. — CommonsWare, Jan 13 '14 at 21:34
Thank you for your suggestions. I tried the example in the link that you both provided, however I'm receiving the same errors as I was with my previous code. I really don't understand what I'm doing wrong. Could this have anything to do with the fact that I have a non-rooted phone? — hkg, Jan 13 '14 at 22:10
LogCat: 01-13 17:07:42.064: E/Trace(4960): error opening trace file: No such file or directory (2) 01-13 17:07:42.064: D/ActivityThread(4960): setTargetHeapUtilization:0.25 01-13 17:07:42.064: D/ActivityThread(4960): setTargetHeapIdealFree:8388608 01-13 17:07:42.064: D/ActivityThread(4960): setTargetHeapConcurrentStart:2097152 01-13 17:07:42.124: E/SQLiteLog(4960): (14) cannot open file at line 30245 of [00bb9c9ce4] 01-13 17:02:15.055: E/SQLiteLog(4363): (14) os_unix.c:30245: (2) open(/data/data/com.example.assethelper/databases/northwind) - — hkg, Jan 13 '14 at 22:11
@hkg you dont need a rooted phone. dont worry about that. it also has nothing to do with file permissions, since the user that is creating the file is the same that is trying to read it. Probably it has to do with write permissions, in the case the file is being write to external sdcard, or something like that. please take a look at my update. — Carlos Robles, Jan 14 '14 at 17:54
@Carlos Robles, thanks for advice on updating my post. I'm quite new to android programming, so please forgive me if my questions/comments sound juvenile. You mentioned that my problem may have something to do with write permissions in that the "yourdb.db" might be being written to the external sdcard. How can that be possible? Wouldn't I need to specify that I want to write to external memory and this permission enabled in the manifest? Wouldn't the yourdb.db file actually end up on external sdcard when I search for the filename on the card? — hkg, Jan 14 '14 at 18:32
actually i don't believe its ben saved to the sdcard, i just say so since some devices internal memory is actually an sdcard, and somethimes applications go crazy, but probably this is not the case. take a look at the update in my answer and try to get a clue about where your file is, and if it is being created. — Carlos Robles, Jan 14 '14 at 18:41

score 1 · Accepted Answer · edited May 23 '17 at 10:34

You are assuming too much about the data folder. You rather should let the system give you the correct path. You can get database path with context.getDatabasePath(); you pass the desired name to the file (no matter if it exists or not). And actually you are supposed to do it that way

For that, at any place you are using something like String outFileName = DB_PATH + DB_NAME; you should change it for

 String outFileName =myContext.getDatabasePath(DB_NAME).getPath() ;

and that's the real valid location for your file. So wour helper will be like this

public class ExternalDbOpenHelper extends SQLiteOpenHelper {

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

// Database filename
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;
        DB_NAME = databaseName;
            DB_PATH =context.getDatabasePath(DB_NAME).getPath() ;
    openDataBase();

}

// Create a database if its 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 {

        checkDb = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY);
    } catch (SQLException e){
        Log.e(this.getClass().toString(), "Error while checking db");
    }

    if (checkDb != null){
        checkDb.close();
    }
    return checkDb !=null;
}

private void copyDataBase() throws IOException {
    //Open a stream for reading, located in the assets
    InputStream externalDbStream = context.getAssets().open(DB_NAME);



    //Create a stream for writing the database byte by byte
    OutputStream localDbStream = new FileOutputStream(DB_PATH);

    //Copy the database
    byte[] buffer = new byte[1024];
    int bytesRead;
    while((bytesRead = externalDbStream.read(buffer)) > 0){
        localDbStream.write(buffer, 0, bytesRead);
    }

             //FLUSH THE OUT STREAM
             localDbStream.flush();

    //Close the streams
    localDbStream.close();
    externalDbStream.close();
}

SQLiteDatabase openDataBase() throws SQLException {

    if (database == null) {
        createDataBase();
        database = SQLiteDatabase.openDatabase(DB_PATH, null,
                SQLiteDatabase.OPEN_READWRITE);
        //database = getWritableDatabase();
    }
    return database;
}

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

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

}
}

with this it should work

UPDATE

Yo can take a few of considerations:

take a look at what path is the system returning for your database

DB_PATH =context.getDatabasePath(DB_NAME).getPath() ;
Log.i ("myApp", DB_PATH);

check the code in the emulator, to see if you have the same problems. there you can manually check if the file is actually created, as explained here
add permissions to write and read the sdcard (READ_EXTERNAL_STORAGE and WRITE_EXTERNAL_STORAGE). but i dont actually believe you need this

I did the Log.i and have verified that the system is sending my database to the correct path. I can't check if the file is being created using the emulator like you mentioned in your post because I don't have a rooted phone, but I can see the file created using adb shell. While stepping through my code, I can see the file size increase from 0 to 3K which is the size of the actual file I'm trying to copy. The permissions on the file are as follows: -rw-rw----. According to all this info, it looks like the file is there and should be able to open. Could the format of yourdb.db not be correct? — hkg, Jan 14 '14 at 19:03
yeah it could be that the file is not ok. I discarded that since in the logcat it uses to say something like "file corrupted at line x". if you want you can try to check this as i explain here [http://stackoverflow.com/a/20455597/2357411](http://stackoverflow.com/a/20455597/2357411) — Carlos Robles, Jan 14 '14 at 19:12
anyways, as i see, the exception you get is android.database.SQLException in checkDataBase(), and that exception is catched and thats what we expect, since after that we copy the file from assets. so at any rate you will have the file created, and im not sure if you have any further exception thrown in openDataBase(). do you? what is the last point in the code that you can reach? where do you know that the file is not being open? — Carlos Robles, Jan 14 '14 at 19:21
I added a try on SQLiteDatabase.openDatabase() and a catch for catching an error on opening db as you suggested. When I run the code, it does not fall into the catch statement, so that must mean the database is opening. If you look at the last 9 to 10 LogCat entries there is one that says ":Can't open file for reading". Is this referring to the database file or related to something else? — hkg, Jan 14 '14 at 19:42
that is not related with your app. Thats related with your Adreno GPU. I guess you are using a Galaxy S3. — Carlos Robles, Jan 14 '14 at 20:17
you should try to read of write something to the database. I think it will work — Carlos Robles, Jan 14 '14 at 20:18
I have verified that the database is open by calling database.isOpen(). I'm trying to read something from the database now. I think the code that I have for viewing my database entries is what is giving me the problem. Will let you know soon if it works. — hkg, Jan 14 '14 at 20:37
ok! let me know! anyways i think that so far i already deserve at least an upvote or accept if it is working to this point! — Carlos Robles, Jan 14 '14 at 20:39
Ok, I can read finally read an entry from my database! What a ride! The error comments in the Logcat were confusing me. Thank you so much for sticking by and helping me through this and for teaching me think methodically and ask the right questions along the way. I would give you numerous upvotes, but its telling me that I need a reputation of 15. I did "accept" your answer though. Thanks again! — hkg, Jan 14 '14 at 20:49

Getting a prepopulated database located in the Assets folder into the data/data/mypackage/databases

1 Answers1

Linked