Bulk inserting using an array of ContentValues

Question

im trying to do a batch insert of about 700 floats. The method i'm using is below and as well as the content provider's bulkInsert. The issue is that when i put all the floating point values into the ContentValues nothing happens. What's a better way to insert those floating point values into the ContentValues object?

    private void saveToDatabase( float[] tempValues )
    {
    ContentValues values = new ContentValues();

    // WM: TODO: add patient id and sensor type
    for (float tempVal : tempValues){
        values.put( DataTable.COLUMN_DATA, tempVal );
    }

    ContentValues[] cvArray = new ContentValues[1];
    cvArray[0] = values;

    ContentResolver resolver = getContentResolver();
    resolver.bulkInsert( HealthDevContentProvider.CONTENT_URI_DATA, cvArray);

    public int bulkInsert(Uri uri, ContentValues[] values){
    int numInserted = 0;
    String table = null;

    int uriType = sURIMatcher.match(uri);

    switch (uriType) {
    case RAWINPUT_TABLE:
        table = RAWINPUT_TABLE_PATH;
        break;
    }

    db.beginTransaction();
    try {
        for (ContentValues cv : values) {
            long newID = db.insertOrThrow(table, null, cv);
            if (newID <= 0) {
                throw new SQLException("Failed to insert row into " + uri);
            }
        }
        db.setTransactionSuccessful();
        getContext().getContentResolver().notifyChange(uri, null);
        numInserted = values.length;
    } finally {         
        db.endTransaction();
    }
    return numInserted;
}

Answer 1

If you want each float to have it's own record in your database, you need an instance of ContentValues for each new record. Right now you have one instance of ContentValues and you are writing the same key to it (meaning you are writing over the value) 700 times.

private void saveToDatabase( float[] tempValues ) {
    final int count = tempValues.legnth;
    ContentValues[] cvArray = new ContentValues[count];
    for (int i = 0; i < count; i++) {
        float tempVal = tempValues[i];
        ContentValues values = new ContentValues();
        values.put( DataTable.COLUMN_DATA, tempVal );
        cvArray[i] = values;
    }

    /* all the rest */
}

Answer 2

I use batch inserts, not sure what the difference between bulk and batch is but all I do is this

ArrayList<ContentProviderOperation> operations = new ArrayList<ContentProviderOperation>();

for(int j=0;j<locationAry.length;j++){
    ContentValues values2 = new ContentValues();
    values2.put(MapPoints.ELEMENT_ECM2ID, ecm2id);
    values2.put(MapPoints.ELEMENT_ID, newElementId);
    values2.put(MapPoints.LATITUDE, locationAry[j+1]);
    values2.put(MapPoints.LONGITUDE, locationAry[j]);
    values2.put(MapPoints.LAYER_ID, layerID);
    operations2.add(ContentProviderOperation.newInsert(MapPoints.CONTENT_URI).withValues(values2).build());


}

getContentResolver().applyBatch(MapElements.AUTHORITY, operations);

did you override the bulkInsert method in your ContentProvider?

Answer 3

If one insert fails, your whole transaction fails. Without seeing your table create statement for unique keys, try a replace after your insert fails.. Also your numInserted will always be the same as values.length no matter what insert/replace fails. this doesnt seem correct either.

  ...
        db.beginTransaction();
        int numInserted = 0;
        try {

            for (ContentValues cv : values) {
                long newID;
                try {
                    newID = database.insertOrThrow(table, null, cv);
                } catch (SQLException ignore) {
                    newID = database.replace(table, null, cv);
                }
                if (newID <= 0) {
                    Log.e("TAG, "Failed to insert or replace row into " + uri);
                } else {
                    // you are good...increment numInserted
                    numInserted++;
                }
            }
            db.setTransactionSuccessful();
            getContext().getContentResolver().notifyChange(uri, null);

        } finally {
            db.endTransaction();
        }
        return numInserted;

Answer 4

I know that this will be rude, but just throw away this code. Providers have primary methods to deal with most SQLite operations and you tried to blend three of them (insert(), bulkInsert(), and applyBatch()) into some kind of Frankenstein. Here are the main mistakes:

1) This line values.put(DataTable.COLUMN_DATA, tempVal) is not inserting new entries at each iteration; it is overriding them. After all iterations, values contains only the 700th float value of your array.

2) As @Karakuri remembered, there is only one ContentValues instance inside cvArray. bulkInsert() doc states about its second parameter:

An array of sets of column_name/value pairs to add to the database. This must not be null.

So cvArray must contain a ContentValues instance (a set) for every entry you want to insert into the database.

3) Not exactly an error, but something you should watch out. There are no guarantees that mTables will exist, and trying to make operations without specifying a table will throw a SQLException.

4) These three lines are basically useless:

if (newId <= 0) {
    throw new SQLException("Failed to insert row into " + uri);
}

insertOrThrow() already throws an exception if some error happens during the insert operation. If you want to check manually for an error, try insert() or insertWithOnConflict() (or add a catch to your try block and deal with the exception there).

5) And finally, there is the problem about numInserted @petey pointed (and there's no need to repeat).

One last advice: forget that bulkInsert() exists. I know that this will require more lines of code, but using applyBatch() you can achieve better results (and more easily, since you do not have to implement it). Wolfram Rittmeyer wrote a series of excellent articles about transactions, check if you have any doubt.

Last but not least (yes, I'm in a good mood today), this is how I would do a basic implementation of your code:

@Override
public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db // TODO: retrieve writable database
    final int match = matcher.match(uri);

    switch(match) {
        case RAWINPUT_TABLE:
            long id = db.insert(RAWINPUT_TABLE, null, values); // TODO: add catch block to deal.
            getContext().getContentResolver().notifyChange(uri, null, false);
            return ContentUris.withAppendedId(uri, id);

        default: 
            throw new UnsupportedOperationException("Unknown uri: " + uri);
    }
}

private void saveToDatabase( float[] tempValues ) {

    ArrayList<ContentProviderOperation> operations = new ArrayList<ContentProviderOperation>();

    for (float tempVal : tempValues){     

        operations.add(ContentProviderOperation
                        .newInsert(HealthDevContentProvider.CONTENT_URI_DATA)
                        .withValue(DataTable.COLUMN_DATA, tempVal).build();
                        .withValue() // TODO: add patient id
                        .withValue() // TODO: add sensor type);        
    }

// WARNING!! Provider operations (except query if you are using loaders) happen by default in the main thread!!

    getContentResolver().applyBatch(operations); 
}