How to Delete All Items From SQLite in Android-ThrowExceptions

Exception or error:

I would like to make an app where the user clicks a button, and the SQLite database is cleared. Here’s what I’ve tried so far:

db.delete(TABLE_NAME, null, null);

What am I doing wrong?

How to solve:

Your delete() call is correct. Did you get writable database? Here is example of method using delete:

/**
 * Remove all users and groups from database.
 */
public void removeAll()
{
    // db.delete(String tableName, String whereClause, String[] whereArgs);
    // If whereClause is null, it will delete all rows.
    SQLiteDatabase db = helper.getWritableDatabase(); // helper is object extends SQLiteOpenHelper
    db.delete(DatabaseHelper.TAB_USERS, null, null);
    db.delete(DatabaseHelper.TAB_USERS_GROUP, null, null);
}

###

db.delete(TABLE_NAME, null, null); is the correct syntax to delete all rows in a table. But I think you would have given table name directly without enclosing it in double-quotes. Try like this

db.delete("TABLE_NAME", null, null);

It might help 🙂

###

Use This Code for clear all database content

Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
String tableName="";
  if (c.moveToFirst()) {

      while ( !c.isAfterLast() ) {

          tableName = c.getString( c.getColumnIndex("name"));
          if(!tableName.equals('android_metadata')){
            db.execSQL("DROP TABLE '"+tableName+"'");
          }
          c.moveToNext();
      }
  }

c.close();

###

Actually another way I did it was to DROP the tables and then just call the onCreate method for the SQLiteDatabase. I don’t know which is most a efficient Delete or Drop because I haven’t really dug deep into how efficient each method is but it works great for me because in my initial database I have some default values set and so when I call the onCreate method for the database I have some PUT methods there also. This saves code replication. ( instead of doing the delete and then the puts I get a multi purpose out of my onCreate function).

I call it reset. So you’d just have to do db.reset() and then the default values are added in the onCreate after you make your tables.

public void reset () throws SQLException {
    db = DBHelper.getWritableDatabase ();
    db.execSQL ("drop table "+TABLE_NAME);
    db.close ();
    this.DBHelper.onCreate (this.db);
}

###

SQLiteDatabase db = this.getWritableDatabase(); //get database
        db.execSQL("DELETE FROM tablename"); //delete all rows in a table
db.close();

###

One thing you could try when developing on a device:

settings>>applications>>(your application name)>>Storage>> clear cache and clear data

by clearing the application data and the application cache, you also wipe the sql database.

###

use this code:

public void deleteAll()
{
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("delete from "+ TABLE_NAME);
    db.close();
}

you can add it DatabaseHandler class, this is full source code:

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
public static final String DATABASE_NAME = "OffLineMessagesClient";

// Contacts table name
public static final String TABLE_NAME = "messages_client";

// Contacts Table Columns names
private static final String KEY_ENTITY_ID = "entity_id";
private static final String KEY_MESSAGE = "message";

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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_NAME + "("
            + KEY_ENTITY_ID + " INTEGER,"
            + KEY_MESSAGE + " TEXT"
            +  ")";

    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
                      int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

    // Create tables again
    onCreate(db);
}

public void deleteAll()
{
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("delete from "+ TABLE_NAME);
    db.close();
}
}

###

for me classical mysql statement:

SQLiteDatabase sqLiteDatabase = context.openOrCreateDatabase(Database.DATABASE_NAME, Context.MODE_PRIVATE, null, null);
sqLiteDatabase.execSQL("DELETE FROM " + tableName1);
sqLiteDatabase.execSQL("DELETE FROM " + tableName2);
sqLiteDatabase.execSQL("DELETE FROM " + tableName3);
sqLiteDatabase.execSQL("DELETE FROM " + tableName4);

worked perfectly. good luck 🙂
ps.: without start/endtransaction or closeDatabase…

###

this work for me:

 public void removeAll()
{

    SQLiteDatabase db = this.getWritableDatabase(); 
    db.delete(TABLE_NAME, null, null);
    db.close();



}

Leave a Reply

Your email address will not be published. Required fields are marked *