Write access for SQLite database on sdcard

Question

I have a SQLite database on my sdcard (/storage/sdcard/MyDir/mydb.sqlite) and try to write some data in it. Due to the Storage Access Framework I can not access the file directly and I need to get write access like it is described here. This grants me write access via a DocumentFile.

However, the database needs to accessed via JDBC. JDBC requires a String to the database file to establish a database connection. This leads to the following problems:

If I use the Java-Path I can access the database, but I have no writing permission. Even if the access was granted like described above.
If I use the Uri of the DocumentFile (having writing permission) JDBC does not find the database file.

I can place my database in the private directory of the application on the sdcard (/storage/sdcard/Android/data/my.application.package/) where I have writing permission. However, I would like to store the database outside that directory, since the database should be accessed by multiple applications.

I tried this suggestion, which works on the device storage, however does not work on the sdcard. On the sdcard I get this stacktrace:

 android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 1294 SQLITE_CANTOPEN_ENOENT[1294]): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:284)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:705)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:272)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:239)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:1292)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:1247)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:930)
    at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:947)

When I access the database on the device storage (internal storage and external storage) I can write the database. Only if I access the database on the sdcard I have no writing permission.

Is there any possibility to get write access to specific files on the sdcard based on java.io.File like it is possible for DocumentFile?

Does anybody have a different idea how I can access the data on the sdcard with writing permission?

score 1 · Answer 1 · answered Oct 17 '19 at 10:50

I have a SQLite database on my sdcard (/storage/sdcard/MyDir/mydb.sqlite)

Using removable storage as a transfer location for a SQLite database is OK. For example, using removable storage for backup and restore operations is fine.

However, for a live database, removable storage is very risky. On many devices, removable storage is removable, and you will suffer catastrophic problems if the user removes the storage while you are trying to write to your database.

And, as you are discovering, using removable storage for a live data is impractical.

the database needs to accessed via JDBC

Android has a built-in SQLite database API. Attempting to use Android's half-baked JDBC classes for accessing a SQLite database is very strange.

When I access the database on the device storage (internal storage and external storage) I can write the database

You will encounter problems on Android 10 and higher, as you no longer have direct filesystem access to most of external storage.

Is there any possibility to get write access to specific files on the sdcard based on java.io.File like it is possible for DocumentFile?

No, sorry. You do not have filesystem access to arbitrary locations on removable storage as of Android 4.4. Android 10 extends that to arbitrary locations on external storage. Either:

Limit yourself to the directories that you can access, or
Use removable storage only for backup/restore operations, with the live database in a more conventional location (getDatabasePath() on Context)

Write access for SQLite database on sdcard

1 Answers1