Continued issue with opening a pre-existing sqlite database in Android

Question

Newbie here. I received some good info in a previous post I made, and I'm hoping I can get a bit more help :) Thanks for looking.

I am shipping a prepopulated sqlite database with an application. I've gone through the process of copying the database from my assets folder within the source code to the /database/ folder through a routine I found online, that I'm sure you all are familiar with (http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/). This appears to be working correctly. Using DDMS I am able to see the database being copied over and showing up in the proper directory.

Now however, I am getting no results. When I examine logcat, I see the following errors when I attempt to open the DB:

02-19 14:59:40.490: I/Database(4071): sqlite returned: error code = 14, msg = cannot open file at source line 25467
02-19 14:59:40.490: E/Database(4071): sqlite3_open_v2("/data/data/com.testapp.test/databases/pbaMainDb.sqlite", &handle, 2, NULL) failed

EDIT: Here is the ListScreen class I am using to open the database after installation and do a simple query from:

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

   DbHelper db = new DbHelper(this);
    db = new DbHelper(this);



    try {
        db.createDatabase();
    } catch (IOException ioe) {
        throw new Error ("Unable to create database");
    }



    try {
    myDbHelper.close();
    db.openDataBase(); 
    } catch(SQLException sqle){
        throw sqle;
  }




}


public Cursor fetchAllTables() {

    return myDbHelper.rawQuery("SELECT name FROM sqlite_master WHERE type='table'",
            new String[]{});

}

private void fillData() {

    //Cursor c = fetchAllTables();
    //startManagingCursor(c);

    String[] from = new String[] {"name"};
    int[] to = new int[] {R.id.listview};


    SimpleCursorAdapter notes = 
            new SimpleCursorAdapter (this, R.layout.list_view, cursor, from, to);
    setListAdapter(notes);

            }

    }

EDIT: Also, from my DbHelper class (where the code is to copy my existing Db), here is the code where I do that (I apologize, it's a bit of a dump):

    public class DbHelper extends SQLiteOpenHelper {
// the default android path of the database files it creates
private static String DB_PATH = "/data/data/com.testdata.test/databases/";
private static String DB_NAME = "pbaMainDb.sqlite";
private SQLiteDatabase myDataBase;
private final Context myContext;

// constructor to keep a reference of the passed context in order to access the 

public DbHelper (Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

// Create an empty database and then rewrite with original

public void createDatabase() throws IOException {
    boolean dbExist = CheckDataBase();
    if (dbExist) {
        // if exists, do nothing
    }else {
        //when we call this method the empty database will be created into 
        this.getReadableDatabase();
        try {
                copyDataBase();
        }
        catch (IOException e) {
                throw new Error("Error copying database");
        }
}
    this.close();
}

        // Check if the database already exists to avoid re-copy
private boolean CheckDataBase() {
    SQLiteDatabase checkDB = null;

    try {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null                 SQLiteDatabase.OPEN_READWRITE);
        //File dbfile = new File(myPath);
        //return dbfile.exists();
    } catch (SQLiteException e) {
            //database doesn't exist yet
    }
    if (checkDB != null) {
            checkDB.close();
    }
    return checkDB != null ? true : false;
}   
  // Copies database from assets folder to new empty folder

    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        //path to just created DB

        String outFileName = DB_PATH + DB_NAME;

        OutputStream myOutput = new FileOutputStream (outFileName);

        //transfer bytes from input file to output file

        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 + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }
    @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) {
    }

}

Answer 1

I had same problem, but as one of comments in "http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications" said, I just did this:

replace this line

String myPath = DB_PATH + DB_NAME;

in checkDataBase() method with this:

String myPath = Environment.getExternalStorageDirectory() + "/" + DB_NAME;

It worked well for me.

Answer 2

This might seem simple, but try taking off the extension from the database name. i.e., make the path "/data/data/com.testapp.test/databases/pbaMainDb" and take the extension off the file when you put it in your assets folder as well.

Also, I think there might be a size limitation on the database file size. How big is your file?

And one final possibility: right after the app crashes, open it up again (not compile and install but just open it up again on the device) and see if it works. Sometimes the databases folder is not created on the first time around so you might have to go in and create it with some thing like

File f = new File(DB_PATH);
if (!f.exists()) {
    f.mkdir();
}

EDIT: A couple more question. After looking over the tutorial you used, did you also use the createDataBase() method as he shows at the very end?

Also, before you perform your query try calling myDbHelper.close(); and then call the open method again. It is possible that the empty version that he creates in the tutorial is still open and needs to be closed on the first time around

Let me know how these go and I will try to keep helping you if necessary.

EDIT: Replace your onCreate and fetchAllTables with my edits below. For some reason, you had created two instances of the DbHelper class so I removed that. Also, I can't see where you declared myDbHelper that you used on onCreate, but get rid of all references to that. We will only use one instance of DbHelper to interact with the database, and that is db. As you can see, I create this as a class variable that we will use throughout the entire class and instantiate and open once in onCreate. Also pay attention if the error changes, because I am only focusing right now on getting the opening of the database to work.

DbHelper db;

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

    db = new DbHelper(this);

    try {
        db.createDatabase();
    }
    catch (IOException ioe) {
        throw new Error ("Unable to create database");
    }

    try {
        db.close();
        db.openDataBase(); 
    } catch(SQLException sqle){
        throw sqle;
    }
}

public Cursor fetchAllTables() {
    return db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
}