How do I update a database I am testing with a new app?

Question

I am building an Android app that includes a built in database, and I am regularly testing it on my Samsung Galaxy S3 device.

I am having an issue now however, since I noticed that the SQLite database on the app is not updating even after I make changes to it on my computer using SQLiteBrowser.

I have verified that I am indeed writing the changes to the database and then rerunning the app from scratch on the device, but still the query results when performed by the app are unchanged, despite the changed data.

I've tried updating the version of the database, but this gives me all sorts of errors because it wants me to write an update script which I don't think is necessary for what I need to do.

Does anyone know how to refresh the data in the database on the device?

How are you transferring the db from your comp to the device? Also, is your Galaxy S3 rooted? — Reed, Oct 16 '14 at 22:28

score 1 · Accepted Answer · answered Oct 16 '14 at 22:15

The easiest way to fix this is to go into Application Manager, select your app and then tap "Clear Cache". This will delete all the original databases that the app created. Android devices maintain this cache to persist databases even after the original app was uninstalled (for a few reasons and when they are deleted depends on the manufacturer's firmware). It is because of this, that even though you see that you are creating your new database, android is still querying the old ones.

You can then do a fresh install of your app and this should fix the problem. Outside of this, your only option is to write an upgrade script within the onUpgrade() method which does the upgrade.

score 1 · Answer 2 · answered Oct 16 '14 at 22:24

You might not be writing to the database that is on the device, but in the folder on your computer. You can try having the app itself do some test updates to the database so that you know it is updating the SQLite Database on the device:

public class SQLHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_NAME = "name goes here";
    private static final String[] KEYS = {"column","names","go","here"};
    private static final String DICTIONARY_TABLE_NAME = "tablename";
    private static final String DICTIONARY_TABLE_CREATE =
                "CREATE TABLE " + DICTIONARY_TABLE_NAME + " (" +
                        KEY[0] + " TEXT, " +
                        KEY[1] + " TEXT, " . . .;

    public SQLHelper(Context context) {
        super(context, DATABASE_NAME , null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // creates table if does not exist
        db.execSQL(DICTIONARY_TABLE_CREATE);

        // . . . update database with test info here...
        db.execSQL("INSERT INTO . . .");
        db.execSQL("UPDATE . . .");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }
}

any advice for what should go in the onUpgrade method in this case? — CodyBugstein, Oct 16 '14 at 22:55
onUpgrade() is called (you do not call it yourself) when version of your DB changed which means underlying table structure changed etc. http://stackoverflow.com/questions/13159210/onupgrade-sqlite-database-in-android — Xiaoyu, Oct 16 '14 at 23:07

score 0 · Answer 3 · answered Oct 16 '14 at 22:14

0

You can use ADB to start a shell and use sqlite to directly act on the database http://developer.android.com/tools/help/sqlite3.html

Once you started sqlite3 just execute regular SQL commands

answered Oct 16 '14 at 22:14

Gregriggins36

905
10
20

But I'm testing it on an actual device, not on an emulator – CodyBugstein Oct 16 '14 at 22:53
You can do it as well on an actual device. You should even be able to run sql commands directly from your phone as long as you have access to a terminal on your phone. – Gregriggins36 Oct 18 '14 at 13:10
Wow - how do you get Terminal access on your phone? – CodyBugstein Oct 18 '14 at 16:57
Here is an application : https://play.google.com/store/apps/details?id=jackpal.androidterm&hl=en you'll most likely need root access to have fun with it – Gregriggins36 Oct 19 '14 at 08:25

How do I update a database I am testing with a new app?

3 Answers3