SQLiteOpenHelper problem with fully qualified DB path name

Question

In my app, I use...

myFilesDir = new File(Environment.getExternalStorageDirectory().getAbsolutePath()
                      + "/Android/data/" + packageName + "/files");
myFilesDir.mkdirs();

This is fine and the resulting path is...

/mnt/sdcard/Android/data/com.mycompany.myApp/files

I need a SQLite DB which I want to store on the SD card so I extend SQLiteOpenHelper as follows...

public class myDbHelper extends SQLiteOpenHelper {

    public myDbHelper(Context context, String name, CursorFactory factory, int version) {
        // NOTE I prefix the full path of my files directory to 'name'
        super(context, myFilesDir + "/" + name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create tables and populate with default data...
    }
}

So far so good - the first time I call getReadableDatabase() or getWriteableDatabase() the empty DB is created on the SD card and onCreate() populates it.

So here's the problem - the app is in beta testing with maybe 5 or 6 people and, like me, they're running Android v2.2 and everything works fine. I have one tester, however, running v2.1 and when myDbHelper tries to create the DB on first use, it crashes with the following...

E/AndroidRuntime( 3941): Caused by: java.lang.IllegalArgumentException: File /nand/Android/data/com.mycompany.myApp/files/myApp-DB.db3 contains a path separator
E/AndroidRuntime( 3941): at android.app.ApplicationContext.makeFilename(ApplicationContext.java:1445)
E/AndroidRuntime( 3941): at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:473)
E/AndroidRuntime( 3941): at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
E/AndroidRuntime( 3941): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
E/AndroidRuntime( 3941): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:158)

The path for the files directory is an odd one ("/nand") as it's internal memory although not the phone's own internal memory - but it is the path returned by getExternalStorageDirectory() for this device.

I can see three possible answers...

1. Although acceptable on v2.2, specifying a fully qualified path for DB name isn't recommended and will fail on earlier versions
2. Fully qualified paths are acceptable for SD card storage but the "/nand" path is being interpreted as 'internal' and only relative paths are acceptable in this case
3. Something else which I'm missing completely

If any or all of the above apply I'd appreciate it if somebody could help with how I should approach this.

Anatolia

Answer 1

Historically, you have not been able to use paths with SQLiteOpenHelper. It only worked on simple filenames. I had not realized that they relaxed that restriction in Android 2.2.

If you wish to use databases on the SD card, and you wish to support Android 2.1 and earlier, you cannot use SQLiteOpenHelper.

Sorry!

Hypnos

No need for the 'Sorry', you've given a definitive answer and it is pretty much what I expected (but didn't know for sure). It'll be a bit of a pain reworking/rethinking my code but at least I now know that's what I need to do. Many thanks. – Anatolia

I just created a solution that works for my Android 2.2 (an may be other versions). See my answer below. – Unmade

Answer 2

You can use the SQLiteOpenHelper with a custom path if you provide a custom ContextClass and if you have write access in the target directory.

public class DatabaseHelper extends SQLiteOpenHelper {
  private static final int DATABASE_VERSION = 3;
    .....

  DatabaseHelper(final Context context, String databaseName)  {
    super(new DatabaseContext(context), databaseName, null, DATABASE_VERSION);
  }
}

And here is the custom DatabaseContext class that does all the magic:

class DatabaseContext extends ContextWrapper {

  private static final String DEBUG_CONTEXT = "DatabaseContext";

  public DatabaseContext(Context base) {
    super(base);
  }

  @Override
  public File getDatabasePath(String name)  {
    File sdcard = Environment.getExternalStorageDirectory();    
    String dbfile = sdcard.getAbsolutePath() + File.separator+ "databases" + File.separator + name;
    if (!dbfile.endsWith(".db")) {
      dbfile += ".db" ;
    }

    File result = new File(dbfile);

    if (!result.getParentFile().exists()) {
      result.getParentFile().mkdirs();
    }

    if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
      Log.w(DEBUG_CONTEXT, "getDatabasePath(" + name + ") = " + result.getAbsolutePath());
    }

    return result;
  }

  /* this version is called for android devices >= api-11. thank to @damccull for fixing this. */
  @Override
  public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler) {
    return openOrCreateDatabase(name,mode, factory);
  }

  /* this version is called for android devices < api-11 */
  @Override
  public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) {
    SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
    // SQLiteDatabase result = super.openOrCreateDatabase(name, mode, factory);
    if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
      Log.w(DEBUG_CONTEXT, "openOrCreateDatabase(" + name + ",,) = " + result.getPath());
    }
    return result;
  }
}

Update june 2012:
how does this work (@barry question):

Normal android apps have their local database files relative to the app folder. By using a customer context with overwritten getDatabasePath() the database is now relative to a different directory on the sd card.

Update feb 2015:
After replacing my old android-2.2 device with a new android-4.4 device I found out that my solution didn't work anymore. Thanks to @damccull-s answer I was able to fix it. I have updated this answer so this should be a working example again.

Update may 2017:

Statistics: This aproach is used in more than 200 github projects

Unmade

Nice example. I resorted to using the internal memory for the DB but I may re-visit using the SD card at some point in the future. Thanks for sharing your code - I'll bookmark this and try it out next time I update the app. – Anatolia

Thanks. good example. I had the same issue in Motorola Mileston. – Smilacaceous

i have a problems in this row: super(new DatabaseContext(context), databaseName, null, DATABASE_VERSION); the error is "No enclosing instance of type MoviesProvider is available due to some intermediate constructor invocation" – Vulgarian

It does indeed seem to work, but I'm not sure how - in the overridden openOrCreateDatabase it calls super.openOrCreateDatabase with a File object. The docs say this is equivalent to calling Equivalent to openDatabase(file.getPath(), factory, CREATE_IF_NECESSARY). But getPath() with just return the path with separators in, so I don't see how this is any different to the original problem... Any idea? – Peril

Excellent, thanks! Seems to work fine on 1.5 (Cupcake) too. This should be marked as the accepted answer, IMHO. – Profusive

You nailed it! This is the only elegant solution that it actually works on all android versions. Well done – Correa

Is there any reason, why factory, and mode are not passed down to SQLiteDatabase? – Prosthetics

Answer 3

k3b's answer is awesome. It got me working. However, on devices using API level 11 or higher, you may see it stop working. This is because a new version of the openOrCreateDatabase() method was added. It now contains the following signature:

openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags, DatabaseErrorHandler errorHandler)

This seems to be the method called by default on some devices with this method available.

In order to make this method work on these devices, you need to make the following alterations:

First, edit your existing method so that it simply returns the result of a call to the new method.

@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode,
        CursorFactory factory) {
    return openOrCreateDatabase(name, mode, factory, null);

}

Second, add the new override with the following code.

@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode, CursorFactory factory, DatabaseErrorHandler errorHandler) {
    SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name).getAbsolutePath(),null,errorHandler);

    return result;
}

This code is very similar to k3b's code, but note that SQLiteDatabase.openOrCreateDatabase takes a String instead of a File, and I've used the version of it that allows for a DatabaseErrorHandler object.

Thousandth

+1 This fixed my app afterupgrading from android 2.2 to android 4.4. I have added this to my answer so other users will have a working example – Unmade

Answer 4

user2371653's answere is very nice. but I found a issue:

@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode,
        CursorFactory factory) {
    return openOrCreateDatabase(name, mode, factory, null);
}

this may cause crasd, if install your app at android 2.x

so we can modify it like this

@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode,
        CursorFactory factory) {
    return super.openOrCreateDatabase(getDatabasePath(name).getAbsolutePath(), mode, factory);
}

because android 2.x does not has the api

openOrCreateDatabase(String name, int mode, 
CursorFactory factory, DatabaseErrorHandler errorHandler)

Glaucous

Answer 5

in my opinion I found a better solution on this side here (SQLite database on SD card) and wanted to inform you. Notice the entry in the constructor.

public class TestDB extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "usertest.db";
    private static final int DATABASE_VERSION = 1;

    public TestDB (Context context){
        super(context, context.getExternalFilesDir(null).getAbsolutePath() + "/" +  DATABASE_NAME, null, DATABASE_VERSION );
    }
    ...
}

Quote from the user website:
"It will create the database in the app's folder on the sdcard: /sdcard/Android/data/[your_package_name]/files. In that way the database will be seen as part of the app by android and removed automatically if the user uninstalls the app."

"I my app I have a large database and it will in most cases not fit on old phones internal memory, e.g. HTC Desire. It runs great on the sdcard, and most apps are "moved to sdcard" themselves anyway so don't worry about the database not being accessible, because the app won't be accessible it self."

Alisa

can you give us info on which android version this worked. I tried this with my android-api-8 device and it didn-t work. may be it works only for newer api-s – Unmade

@k3b: Sorry, I was using api lvl 14. – Alisa