Interacting with external SQLite databases from Android.

Question

I'm trying to work with a web service (that I have no control over) that returns a SQLite database when you query it. Is there any way to do this?

Answer 1

If you have no option to the format of the result, then a solution for you is to save the database file into the database directory of your app in /data/data/package_name/databases, and after each request load the database file and query for the results.

Here are a couple of pages on databases.

1. http://developer.android.com/guide/topics/data/data-storage.html
2. http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Answer 2

I am working on a project that's doing something similar. I request updates to a database and depending on the volume of updates required, it either sends down an entirely new database or a series of SQL statements. I would strongly suggest keeping the databases on the sdcard if it is available, especially if the size of the databases you're downloading are variable and beyond just a few kilobytes in size.

I'm doing the following to get the file (modify to taste since you may have XML to deal with in the SOAP response, also ignore the progress stuff.)

fileStream = new FileOutputStream(new File(I-build-my-filename-stuff-above-here));

/* fileData is a byte[8192] */
while((i = httpResponse.read(fileData)) > -1) {
    fileStream.write(fileData, 0, i);

    position += i;

    if (item.getUpdateType() == UpdateItem.FULL_FILE) {
        progress = position / item.getSize();
    } else {
        progress = position / (item.getSize() * 2);
    }

    item.setProgress(progress);
} // end loop through getting http response stream

fileStream.close();
httpResponse.close();

Then I do the following to access the database. Since these databases are also used on an iPhone app, they do not have an Android-specific table and therefore the SQLiteDatabase.NO_LOCALIZED_COLLATORS flag is needed to access the DB. Note that my Database class extends the SQLiteOpenHelper class:

public Database(Context context, String fileName, String title) {
    super(context, fileName, null, 1);
    this.context = context;
    this.title = title;
    this.fileName = fileName;

    if (fileName != null && fileName.contains("/sdcard")) {
        db = SQLiteDatabase.openDatabase(fileName, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS | SQLiteDatabase.CREATE_IF_NECESSARY);            
    } else {
        db = getWritableDatabase();
    }

}

Answer 3

@MattC

Is it possible to post all your code from the database and for the creating of your database file?

Thanks,

Wouter