CODEWITHSUNDEEP
androidsqlitesql-delete
Cg2916
Cg2916

Reputation: 1117

How to Delete All Items From SQLite in Android

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?

Upvotes: 23

Views: 77497

Answers (9)

Mersad Nilchy
Mersad Nilchy

Reputation: 612

this work for me:

 public void removeAll()
{

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



}

Upvotes: 0

Fakhriddin Abdullaev
Fakhriddin Abdullaev

Reputation: 4910

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();
}
}

Upvotes: 1

Jordan Hochstetler
Jordan Hochstetler

Reputation: 1416

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);
}

Upvotes: 3

Sm0k3Scr33n
Sm0k3Scr33n

Reputation: 129

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.

Upvotes: 1

cV2
cV2

Reputation: 5319

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…

Upvotes: 0

Cristiana Chavez
Cristiana Chavez

Reputation: 11519

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

Upvotes: 2

Sandeep
Sandeep

Reputation: 986

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();

Upvotes: 4

suraj
suraj

Reputation: 1898

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 :)

Upvotes: 17

petrnohejl
petrnohejl

Reputation: 7759

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);
}

Upvotes: 46

Related Questions