CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Panache
Panache

Reputation: 1721

SQLite delete specific row not working

I have created a SQLite table with below command in my app.

 db.execSQL("Create table if not exists mytab(Id INTEGER PRIMARY KEY autoincrement not null,deviceid int,leftsof Real,rightsof Real)");

I have around 30000 rows in this table now and I am transferring data to server. Data is inserting to server table smoothly but on every insertion I want to delete specific SQLite table row being inserted.

Here is code for that.

     try {


                        for (int i = 0; i < databaseWorking.getlati().size(); i++) {
                            System.out.println("Local DB Size " + databaseWorking.getlati().size());

//code for insertion
                            if (response == 200) {

                                databaseWorking.deleterow(i);
                            }
                        }

Code for deleting row

  void deleterow(int id) {
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        String query = "Delete from mytab Where Id=" + id ;
                   sqLiteDatabase.execSQL(query);
    }

But rows are not deleted though delete method is being called and there is no error. Everytime I m getting same size of database.

can someone guide ?

Upvotes: 1

Views: 1274

Answers (1)

MikeT
MikeT

Reputation: 56928

Certainly for the first iteration there will be no deletion as i (the for loop counter) will be 0 and there will be no row with Id being 0 unless you have set the Id to 0 specifically. Whether or not subsequent rows will match i is impossible to say with the information given.

I'd suggest retrieving the actual Id for example

1) Add a new method to get all the rows to be transferred and deleted (this assumes all);

Cursor getRowsToDelete() {
    SQLiteDatabase db = this.getWriteableDatabase;
    Return db.query("mytab",null,null,null,null,null,null);
}

2) Modify the existing deleterow method to accept a long, to use the delete method (this will return the number of rows deleted for each) and return the number of rows deleted.

int deleterow(long id) {
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    sqLiteDatabase.delete("mytab",
        "Id=?,
        new String[]{Long.toString(id)});
}

3) Instead of the for loop use :-

SQliteDatabase databaseWorking;
Cursor csr = databaseWorking.getRowsToDelete();
int initialrowwcount = csr.getCount();
int deletecount = 0;
while (csr.moveToNext()) {
     //....... your insertion code .....
    if (response == 200) {
        if (databaseWorking.deleterow(csr.getLong(csr.getColumnIndex("Id"))) < 1) {
            System.out.println(" Row with Id " +
                csr.getLong(csr.getColumnIndex("Id")) + 
                " was not deleted.");
        } else {
            deletecount++;
        }
    }
}
csr.close();
System.out.println("Out of " +
        Integer.toString(initialrowcount) +
        " rows, " +
        integer.toString(deleteccount) +
        " rows were deleted.";
}

This retrieves all rows into an SQLite Cursor using the getRowsToDelete method created in step 1. It then loops through the rows using the moveToNext method of the Cursor, this will loop through the rows of the Cursor which may be in any order (you can specify the order in the query method).

The actual Id is extracted using the Cursor's getLong method (there is a getInt method however as rowid's (specifying Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL makes the Id column an alias for the ROWID column)).

The getLong method like all the get????? methods takes the column index(offset, so 0 is the first column) as an argument (Id would very likely be 0). However the getColumnIndex, which takes the column name, as a string, as it's argument returns the column index according to the column name (using this is less likely to result in coding the incorrect column index).

csr.close() closes/finishes with the cursor.

The above code would be a prime candidate for using an SQLite TRANSACTION. However, for brevity this has not been included. Using a transaction could markedly reduce the time taken.

Upvotes: 1

Related Questions