Emmanuel Brunet Emmanuel Brunet - 7 months ago 12
SQL Question

Android SQliteOpenHelper delete statement issue

Trying to refresh a catalog items table .. just a basic database operation I'm stuck with a strange behaviour

String purgeItems ="DELETE FROM CatalogItems";

String count = "SELECT * FROM CatalogItems";

SQLiteDatabase db = mDb.getWritableDatabase(); // mDb extends SQLiteOpenHelper

db.beginTransaction();
db.rawQuery(purgeItems,null);
db.setTransactionSuccessful();
db.endTransaction();

Cursor c = db.rawQuery(count, null);
Log.e(TAG, "CatalogItems after purge " + String.valueOf(c.getCount()));


returnsĀ 642 : the original table rows count

... ! perhaps I'm tired ^^. it does work fine with other tables ..

the SQLiteOpenHelper (mDb) creation table code

private void createTables(SQLiteDatabase db) {
// --- CATALOG ITEMS ---
String catalogItemsTable = "CREATE TABLE CatalogItems (" +
"idCatalogItem INT(11) PRIMARY KEY, " +
"idCatalog INT(11), " +
"idProduct INT(11), " +
"image BLOB, " +
"price FLOAT, " +
"quantity INT(11), " +
"unit VARCHAR(5), " +
"status INT(11), " +
"extras VARCHAR(512) " +
");" ;

db.execSQL(catalogItemsTable);
Log.i(TAG, "table CatalogItems created");

Answer

rawQuery is meant for queries that return rows. Instead use:

db.execSQL(purgeItems);