How To Drop Android Sqlite Database Programmatically

Question

I am trying to provide an option in my App for the user to wipe out all data and start afresh. I have tried a few things but none seem to work in the sense that the data is still there. Here is my first attempt:

public void clearDatabase(Context context) {
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase database = helper.getWritableDatabase();
        database.delete(Constants.TRANSACTION_TABLE, null, null); //erases everything in the table.
        database.delete(Constants.CUSTOMER_TABLE, null, null);
        database.delete(Constants.RETAILER_TABLE, null, null);

        database.close();
    }

This seems to work, but then the app will crash on restart.

Then I try this one from within the SQliteOpenHelper sub class

  public void resetDatabase() {
    SQLiteDatabase database = getWritableDatabase();
    database.execSQL("DROP TABLE IF EXISTS customer_table");
    database.execSQL("DROP TABLE IF EXISTS transaction_table");
    database.execSQL("DROP TABLE IF EXISTS retailer_table");
    database.execSQL(CREATE_CUSTOMER_TABLE);
    database.execSQL(CREATE_TRANSACTION_TABLE);
    database.execSQL(CREATE_RETAILER_TABLE);
    database.close();
}

This does not crash on restart but the data is still there. The only thing that worked is if I change the version number programmatically. But then when I run the app from the IDE it will have an older version number and the app will not start because the version number on the device is higher than the version number in the code.

What else can I try?

Are you sure the table names are correct? Try to do execSQL without 'IF EXISTS' and see if the system throws an exception — rickyalbert, Nov 05 '14 at 19:06
Thanks I tried it and put some logging statement which did confirm that the tables are being deleted, however the app will not restart again, it crashes saying tables does not exist, I am investigating — Val Okafor, Nov 05 '14 at 19:40

score 6 · Accepted Answer · edited May 23 '17 at 12:01

Finally this is worked for me

First drop and create the database

  public void resetDatabase() {
        SQLiteDatabase database = getWritableDatabase();
        database.execSQL("DROP TABLE IF EXISTS " + Constants.CUSTOMER_TABLE);
        database.execSQL("DROP TABLE IF EXISTS " + Constants.TRANSACTION_TABLE);
        database.execSQL("DROP TABLE IF EXISTS " + Constants.RETAILER_TABLE);
        database.execSQL(CREATE_CUSTOMER_TABLE);
        database.execSQL(CREATE_TRANSACTION_TABLE);
        database.execSQL(CREATE_RETAILER_TABLE);
        database.close();
    }

And then restart the app borrowed from this question how to programmatically "restart" android app?

public static void resetApp() {
        sDatabase.resetDatabase(); 
        preferenceEditor.clear();
        preferenceEditor.commit();
        Intent mStartActivity = new Intent(sContext, MainActivity.class);
        int mPendingIntentId = 123456;
        PendingIntent mPendingIntent = PendingIntent.getActivity(sContext, mPendingIntentId,    mStartActivity, PendingIntent.FLAG_CANCEL_CURRENT);
        AlarmManager mgr = (AlarmManager)sContext.getSystemService(Context.ALARM_SERVICE);
        mgr.set(AlarmManager.RTC, System.currentTimeMillis() + 100, mPendingIntent);
        System.exit(0);

    }

score 2 · Answer 2 · answered Feb 13 '17 at 12:40

2

I think the best and easiest way to remove the complete DB file is context.deleteDatabase(DATABASE_NAME);

but before this you have to close all your DB connection else you have to restart your application. Hope this will help you

answered Feb 13 '17 at 12:40

Summved Jain

872
2
18
21

score 1 · Answer 3 · answered Nov 05 '14 at 19:06

1

Try this code

    SQLiteDatabase db = dbHelper.getWritableDatabase(); 

    count = db.delete("table_name", selection, selectionArgs);

answered Nov 05 '14 at 19:06

Darish

11,032
5
50
70

How To Drop Android Sqlite Database Programmatically

3 Answers3