CODEWITHSUNDEEP
androidsqlite
jasonflaherty
jasonflaherty

Reputation: 1944

Bulk insert into SQLite from Parsed XML Android - Switching to Transaction

I am having trouble adding to my sqlite db with the transaction. I have about 1700 rows I'll be adding and it is taking WAY to long doing it this way:

public SnoTelSiteData createSnoTelSiteData(String markerID,
            String sitename, String sitedesc, String lat, String lon) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_SITEMARKERID, markerID);
        values.put(MySQLiteHelper.COLUMN_SITENAME, sitename);
        values.put(MySQLiteHelper.COLUMN_SITEDES, sitedesc);
        values.put(MySQLiteHelper.COLUMN_SITELAT, lat);
        values.put(MySQLiteHelper.COLUMN_SITELON, lon);
        long insertId = database.insert(MySQLiteHelper.TABLE_SITES, null,
                values);
        Cursor cursor = database.query(MySQLiteHelper.TABLE_SITES, allColumns,
                MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null,
                null);
        cursor.moveToFirst();
        SnoTelSiteData newSite = cursorToSnoTelSiteData(cursor);
        cursor.close();
        return newSite;
    }

So I am trying to rework this method into a transaction like so:

public SnoTelSiteData bulkCreateSnoTelSiteData(String markerID,
            String sitename, String sitedesc, String lat, String lon) {

        String sql = "INSERT INTO " + MySQLiteHelper.TABLE_SITES
                + " VALUES (?,?,?,?,?,?);";
        SQLiteStatement statement = database.compileStatement(sql);
        database.beginTransaction();

        statement.clearBindings();
        statement.bindString(1, markerID);
        statement.bindString(2, sitename);
        statement.bindString(3, sitedesc);
        statement.bindString(4, lat);
        statement.bindString(5, lon);

        statement.execute();

        Log.w("EXECUTE", "Done");
        database.setTransactionSuccessful();
        database.endTransaction();
        return null;

    }

That isn't working since i get an error due to it not adding the COLUMN_ID which is my primary key. Any suggestions on how to make this transaction work?

EDIT:::

Here is my for loop that takes the info out of a SparseArray

for (int i = 0; i < vsize; i++) {

                    MapMarkers marks = new MapMarkers();
                    String title = visibleMarkers.valueAt(i).getTitle();
                    String desc = visibleMarkers.valueAt(i).getDesc();
                    Float latitude = visibleMarkers.valueAt(i).getLat();
                    Float longitude = visibleMarkers.valueAt(i).getLon();
                    String lat = latitude.toString();
                    String lon = longitude.toString();
                    m = map.addMarker(new MarkerOptions()
                            .position(new LatLng(latitude, longitude))
                            .title(title).icon(icon));

                    datasource.bulkCreateSnoTelSiteData(m.getId(), title, desc,
                            lat, lon);

                    marks.setTitle(title);
                    marks.setDesc(desc);

                    // markerInfo.put(m, marks);
                    m.setData(marks);

                }

I should do this transaction within that for loop correct? I see no other way.

Upvotes: 0

Views: 372

Answers (1)

HalR
HalR

Reputation: 11083

hopefully you created your table with a sql string like this:

 create table MySQLiteHelper.TABLE_SITES(_id integer primary key autoincrement, ...

Then you should be able to insert and get your auto-incrementing id by sending a null for the first parameter like this:

String sql = "INSERT INTO " + MySQLiteHelper.TABLE_SITES
        + " VALUES (NULL,?,?,?,?,?);";
SQLiteStatement statement = database.compileStatement(sql);
database.beginTransaction();

statement.clearBindings();
statement.bindString(1, markerID);
statement.bindString(2, sitename);
statement.bindString(3, sitedesc);
statement.bindString(4, lat);
statement.bindString(5, lon);

Also note that you are binding 5 values, which is correct (but before you had 6 ?'s and only 5 values) and they are 1-based, not 0-based, which is also correct.

Upvotes: 2

Related Questions