Android Sqlite Transaction and Content Provider

Question

I'm Parsing a JSON WebService and creating a array with data to INSERT and DELETE entries in a database.

I found the solution bulkInsert to insert multiple rows using database transactions inside a content provider, however, I am trying to do the same procedure to delete multiple lines.

The INSERT solution:

@Override
public int bulkInsert(Uri uri, ContentValues[] allValues) {

    SQLiteDatabase sqlDB = mCustomerDB.getWritableDatabase();

    int numInserted = 0;
    String table = MyDatabase.TABLE;

    sqlDB.beginTransaction();

    try {
        for (ContentValues cv : allValues) {
            //long newID = sqlDB.insertOrThrow(table, null, cv);
            long newID = sqlDB.insertWithOnConflict(table, null, cv, SQLiteDatabase.CONFLICT_REPLACE);
            if (newID <= 0) {
                throw new SQLException("Error to add: " + uri);
            }
        }
        sqlDB.setTransactionSuccessful();
        getContext().getContentResolver().notifyChange(uri, null);
        numInserted = allValues.length;
    } finally {
        sqlDB.endTransaction();
    }

    return numInserted;
}

Using this call:

mContext.getContentResolver().bulkInsert(ProviderMyDatabase.CONTENT_URI, valuesToInsertArray);

Is there any way to delete multiple rows (with this array ID's) of database using content provider.

UPDATE:

I found this solution, using the `IN clause:

List<String> list = new ArrayList<String>();

for (ContentValues cv : valuesToDelete) {
    Object value = cv.get(DatabaseMyDatabase.KEY_ROW_ID);
    list.add(value.toString());
}

String[] args = list.toArray(new String[list.size()]);
String selection = DatabaseMyDatabase.KEY_ROW_ID + " IN(" + new String(new char[args.length-1]).replace("\0", "?,") + "?)";

int total = mContext.getContentResolver().delete(ProviderMyDatabase.CONTENT_URI, selection, args);
LOGD(TAG, "Total = " + total);

The problem is that, if the JSON return more than 1000 rows to insert, occurs error, because the SQLITE_MAX_VARIABLE_NUMBER is set to 999. It can be changed but only at compile time.

ERROR: SQLiteException: too many SQL variables

Thanks in advance

score 2 · Accepted Answer · answered Aug 30 '13 at 16:08

I solved this issue with this code:

if (!valuesToDelete.isEmpty()) {

        StringBuilder sb = new StringBuilder();
        String value = null;

        for (ContentValues cv : valuesToDelete) {

            value = cv.getAsString(kei_id);

            if (sb.length() > 0) {
                sb.append(", ");
            }
            sb.append(value);
        }

        String args = sb.toString();

        String selection = kei_id + " IN(" + args + ")";

        int total = mContext.getContentResolver().delete(uri, selection, null);
        LOGD(TAG, "Total = " + total);


    } else {
        LOGD(TAG, "No data to Delete");
    }

Thanks

Take a look at ContentProviderOperation. It should be the right way to do batch update/deletion/insertion: http://stackoverflow.com/a/22823592/727768 — X.Y., Apr 02 '14 at 21:40

score 0 · Answer 2 · answered Aug 20 '13 at 02:19

User ContentResolver object to delete multiple rows.

// get the ContentResolver from a context
// if not from any activity, then you can use application's context to get the ContentResolver
//    'where' is the condition e.g., "field1 = ?" 
//    whereArgs is the values in string e.g., new String[] { field1Value } 
ContentResolver cr = getContentResolver();
cr.delete(ProviderMyDatabase.CONTENT_URI, where, whereArgs);

So any row with (field1 = field1Value) will be deleted.

If you want to delete all the rows then

cr.delete(ProviderMyDatabase.CONTENT_URI, "1 = 1", null);

Android Sqlite Transaction and Content Provider

2 Answers2