Accessing a certain downloaded .sqlite database in external storage from an android app

Question

I am trying to implement an offline map feature in an android WebView-based app:

Using DownloadManager, I download an sqlite database file (of a structure like .mbtiles) from our webserver, which contains blobs of the map tile images (the "offline map") and store it in the android external storage through

public void downloadMap() {
    downloadManager = (DownloadManager) getActivity().getSystemService(Activity.DOWNLOAD_SERVICE);
    DownloadManager.Request r = new DownloadManager.Request(Uri.parse("http://sry.youtoknow.idontwant/map.sqlite"));

    r.setDestinationInExternalFilesDir(getContext(), "map", "map.sqlite");

    downloadManager.enqueue(r);
}

The Download seems to work perfectly well. I register a BroadcastReceiver to receive DownloadManager.ACTION_DOWNLOAD_COMPLETE and store the path to "map.sqlite" using SharedPreferences.

To access the database from JavaScript for using the tile images in the WebView, I intercept XHRequests by checking for a custom fictive schema name customhttp: in the URL:

webView.setWebViewClient(new WebViewClient() {
    public WebResourceResponse shouldInterceptRequest(WebView view, WebResourceRequest request) {
        String url = request.getUrl().toString();
        if (!url.startsWith("customhttp:")) {
            return null;
        } else {
            SharedPreferences preferences = PreferenceManager.getDefaultSharedPreferences(getContext());
            SQLiteDatabase db = SQLiteDatabase.openDatabase(preferences.getString("map_uri", Environment.getExternalStorageDirectory().getPath() + "berlin.sqlite"), null, SQLiteDatabase.OPEN_READONLY);
        }
    }
});

My problem is that I seem to implement openDatabase() in a wrong way as I get the following unpleasant error with the corresponding line, but can't figure out why:

E/SQLiteLog(#####):      (14) cannot open file at line ##### of [##########]
E/SQLiteLog(#####):      (14) os_unix.c:#####: (2) open(//file:///storage/emulated/0/Android/data/idontwant.youtoknow.sry/files/map/map.sqlite) - 
E/SQLiteDatabase(#####): Failed to open database 'file:///storage/emulated/0/Android/data/idontwant.youtoknow.sry/files/map/map.sqlite'.
E/SQLiteDatabase(#####): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database

[...]

The file exists, is not corrupt and its path seems to be correct. I was able to open the file using an SQLite viewing app.

Question: Why does my Code not work; how can I make it work?

I am aware that there exist at least three very similar questions with the above error message here, but they are not that specific, detailed and/or do not exactly fit my needs. I also didn't manage to solve the problem after reading this article:

http://blog.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/.

Thank you very much for your help!

UPDATE:

I tried creating a new database using SQLiteDatabase.openOrCreateDatabase() in external storage in order to compare it to the original database. It was not created - instead I surprisingly confronted the same error message (could not open database).

Then I tried again to open a new database, this time in RAM, using SQLiteDatabase.openOrCreateDatabase(":memory:", null) and it did not throw any error.

Thus I begin to think that the problem is some access restriction to external storage or a mistake when addressing external storage rather than the database file itself.

Did you checked the version of the SQlite used to create the file and the one used in Android (that is not the vanilla Sqlite I believe) might be a compatibiliy error — AxelH, Oct 03 '16 at 12:37
The SQLite version the file was created with is 3.11.0 which is a quite recent version (2016). I will look into this. Thank you @AxelH ! — M.S., Oct 03 '16 at 13:32
Refer to https://developer.android.com/reference/android/database/sqlite/package-summary.html for API level - SQLite version reference. — M.S., Oct 03 '16 at 14:09
Tryto create the file with a version corresponding to your API. This will check if this is the source of the error — AxelH, Oct 03 '16 at 14:24
I guess you have the access to the external storage (Permission into the Manifest) (if you download the file from the app, you should). Check the downloaded file compared to the original one. Might be something here too. — AxelH, Oct 03 '16 at 14:47
This library might help you: https://github.com/jgilfelt/android-sqlite-asset-helper — Vektor88, Oct 03 '16 at 15:02
@AxelH Yes, I have permissions to write to and read from external storage. I also compared the downloaded file to the original file (albeit not bitwise) but it seems to be the same. — M.S., Oct 03 '16 at 15:14
@Vektor88 Thank you very much for your comment. From the linked page I get that the library is designed for asset databases. My database is downloaded from a webserver. Please excuse the misunderstanding (if you agree we had one of course)! — M.S., Oct 03 '16 at 15:23
@M.S. My bad, for some reason I remembered that the database folder could be customized. — Vektor88, Oct 03 '16 at 15:29
Just to exclude permission issues: Can you open the database when you copy it into your private files directory first? — F43nd1r, Oct 03 '16 at 18:58
@AxelH Both files, original and download, have equal checksums. — M.S., Oct 03 '16 at 19:53
Well,we can't blame the file then ^^ Next target, your code, how do you test it ? Running a full query or just opening the database ? I already have this errorr (but with a `locked database` message) when I to close a cursor or anything. Could you post your test code ? — AxelH, Oct 04 '16 at 07:02
@AxelH Thank you very much for your help! I now solved this little misery myself having been _inspired_ by some of your great comments. Please refer to my answer for further information. — M.S., Oct 04 '16 at 21:40
@F43nd1r Thank you! `this.getAbove().isValidFor(you) = true;`. — M.S., Oct 04 '16 at 21:46

score 4 · Accepted Answer · answered Oct 04 '16 at 21:33

I finally managed to find the mistake, a quite sobering one.

I received DownloadManager.ACTION_DOWNLOAD_COMPLETE and stored the URI to the downloaded database in SharedPreferences for later reference inside the shouldInterceptRequest method using:

...
Cursor c = downloadManager.query(query);
if (c.moveToFirst()) {
    if (c.getInt(c.getColumnIndex(DownloadManager.COLUMN_STATUS)) == DownloadManager.STATUS_SUCCESSFUL) {
        Editor editor = PreferenceManager.getDefaultSharedPreferences(getContext()).edit();
        editor.putString("map_uri", c.getString(c.getColumnIndex(DownloadManager.COLUMN_LOCAL_URI)));
        editor.commit();
    }
}
c.close();

In shouldInterceptRequest I then called

SharedPreferences preferences = PreferenceManager.getDefaultSharedPreferences(getContext());
SQLiteDatabase db = SQLiteDatabase.openDatabase(preferences.getString("map_uri", Environment.getExternalStorageDirectory().getPath() + "map.sqlite"), null, SQLiteDatabase.OPEN_READONLY);

and thereby passed the URI to openDatabase, not the Path alone.

I now Uri.parse(preferences.getString("map_uri", ...)).getPath() and everything works. The map loads.

Thank you very much for your help!

Accessing a certain downloaded .sqlite database in external storage from an android app

1 Answers1