Insert Multiple rows in SQLITE with Android

In past days i came across an issue in which i need to synchronize the local device database from the live database data for that I am using the traditional way to insert the data and then execute that statement in loop

At some times if the loop is longer then its creating an issue at some time.. means sometime its inserts 4 rows and then throws error for database connection failure, at sometime it inserts only a single row and throws error for database communication error, so the error is not predicted when and at which line it occur.

The after searching on google for specific keywords i found a good  example that explains how to use the insert helper provided by android to insert the data

 

Following are two code snippets that used first one is traditional way and another is using the insert helper

while(moreValuesToInsert) {
ContentValues values = new ContentValues();
 values.put(BMIDataSQLHelper.TIP_ID, json_data.getInt("id"));
 values.put(BMIDataSQLHelper.TIP_CONTENT, json_data.getString("message"));
 values.put(BMIDataSQLHelper.TIP_LOCATION, json_data.getString("location"));
 values.put(BMIDataSQLHelper.TIP_LIKE_COUNT, json_data.getInt("likecount"));
 values.put(BMIDataSQLHelper.TIP_SENDER_NAME, json_data.getString("name"));
 db1.insert(BMIDataSQLHelper.TABLE_TIPS, null, values);
}

The solution implemented as follows

 

import android.database.DatabaseUtils.InsertHelper; // import statement
InsertHelper ih = new InsertHelper(db, "TableToInsert"); initialize the helper with database object and table name
// Get the numeric indexes for each of the columns that we're updating
        final int col1 = ih.getColumnIndex("col1");
        final int col2 = ih.getColumnIndex("col2");
        //...
        final int col3 = ih.getColumnIndex("col3");
while (moreRowsToInsert) {
            // ... Create the data for this row (not shown) ...
 
            // Get the InsertHelper ready to insert a single row
            ih.prepareForInsert();
 
            // Add the data for each column
            ih.bind(col1, col1Data);
            ih.bind(col2, col2Data);
            //...
            ih.bind(col3, col3Data);
 
            // Insert the row into the database.
            ih.execute();
        }

So After implementing the solution the error for database communication error vanished away.

Advertisements

What you think ? Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s