Android - SQLiteStatement (multiple insert) execute fails with API Level 14

Question

I use the following code to insert new lines efficiently in a DB :

@Override
public void insertImpacts(HolderExpense expense, int[] shares, int[] amounts, HolderUser[] users) {

    try {

        mDb.beginTransaction(); // the insertion of the impacts of one expense are considered to be ONE block

        String sql = "INSERT INTO "+DATABASE_TABLE_IMPACTS+" "+
        "("+IMPACT_USERROWID+", "+IMPACT_EXPENSEROWID+", "+IMPACT_TTROWID+", "+IMPACT_NUMBEROFPARTS+", "+IMPACT_FIXEDAMOUNT+") VALUES (?, ?, ?, ?, ?)";

        SQLiteStatement stmt = mDb.compileStatement(sql);

        stmt.bindLong(2, expense.rowId);
        stmt.bindLong(3, expense.tt.rowId);

        int i = 0;

        while (i < shares.length) {

            if (users[i] != null) {

                Log.v(TAG, "user " +i+": users[i].rowId:"+users[i].rowId+" expense.rowId:"+expense.rowId+" expense.tt.rowId:"+expense.tt.rowId+" shares[i]:"+shares[i]+" amounts[i]:"+amounts[i]+" ");

                stmt.bindLong(1, users[i].rowId);
                stmt.bindString(4, shares[i]+"");
                stmt.bindString(5, amounts[i]+"");

                stmt.execute();

            }
            i++;
        }

        stmt.close();

        mDb.setTransactionSuccessful();

    } 
    catch (Exception e) { e.printStackTrace(); throw new RuntimeException("insertImpacts() failed"); } 
    finally { mDb.endTransaction(); }

}

It works until android 4.x where I get that error:

02-26 14:27:46.179: W/System.err(937): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed

02-26 14:27:46.179: W/System.err(937):  at android.database.sqlite.SQLiteStatement.native_execute(Native Method)

02-26 14:27:46.219: W/System.err(937):  at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:92)

02-26 14:27:46.219: W/System.err(937):  at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:70)

Seems it crashes at stmt.execute() when inserting the second line into the table.

Any clue ?

-- EDITION --

The schema of the table is the following:

 private static final String DATABASE_CREATE_TABLE_IMPACTS =
        "create table " + DATABASE_TABLE_IMPACTS + " (" 
        + IMPACT_ROWID + " integer primary key autoincrement, "
        + IMPACT_USERROWID + " integer not null, "
        + IMPACT_EXPENSEROWID + " integer not null, "
        + IMPACT_TTROWID + " integer not null, "
        + IMPACT_NUMBEROFPARTS + " integer not null, "
        + IMPACT_FIXEDAMOUNT + " integer not null, "
        + "constraint i_cstr1 unique ("+IMPACT_USERROWID+", "+IMPACT_EXPENSEROWID+")); ";

This code works like a charm on Android 2.2 (but fails on Android 4.0).

Print of the two first lines I insert (it crashes when trying to insert the second):

02-26 14:27:46.069: E/DatabaseAdapter.java(937): user 0: users[i].rowId:7 expense.rowId:2 expense.tt.rowId:2 shares[i]:1 amounts[i]:-1 
02-26 14:27:46.069: E/DatabaseAdapter.java(937): user 1: users[i].rowId:5 expense.rowId:2 expense.tt.rowId:2 shares[i]:1 amounts[i]:-1 

Answer 1

Found. The different version of android do not behave the same way. On Android 4.x, all the 'bindXXX' lines must be placed in the 'while' loop. Even if it is repetitive.

while (i < shares.length) {

    if (users[i] != null) {

        stmt.bindLong(1, users[i].rowId);
        stmt.bindLong(2, expense.rowId);
        stmt.bindLong(3, expense.tt.rowId);
        stmt.bindString(4, String.valueOf(shares[i]));
        stmt.bindString(5,  String.valueOf(amounts[i]));

        stmt.execute();
    }

    i++;

}

Answer 2

This is perfect Gilbou! I also made your data binding solution for my task: csv file importing to SQLite database. There was about 25.000 rows in the csv file, and importing it and insert to SQLite takes for me about 5 seconds (before it took 5 minutes without data binding!!)

Thank you so so much!

I also share mine, maybe it can helps for somebody, too (Android 4.0):

public boolean updateFromCsv() {

    boolean ok = true;
    String line = "";
    BufferedReader br = null;

    try {
        FileInputStream fis = new FileInputStream(Environment
                .getExternalStorageDirectory().getAbsolutePath()
                + "/Servantes/Be/leltariv_export.csv");
        br = new BufferedReader(new InputStreamReader(fis));
    } catch (FileNotFoundException e) {
        ok = false;
        e.printStackTrace();
    }       
    try {

        database.beginTransaction(); 

        String sql = "INSERT INTO "+LeltarTable.TABLE_LELTAR_NEV+" "+
        "("+LeltarTable.COLUMN_ID+", "+LeltarTable.COLUMN_LSZ+", "+LeltarTable.COLUMN_MEGN+", "+LeltarTable.COLUMN_HELY+", "+LeltarTable.COLUMN_DARAB+") VALUES (?, ?, ?, ?, 0)";

        SQLiteStatement stmt = database.compileStatement(sql);

        while ((line = br.readLine()) != null) {
            String[] colums = line.split(";");
            stmt.bindAllArgsAsStrings(colums);
            stmt.execute();
}
        stmt.close();

        database.setTransactionSuccessful();

    }catch (Exception e) {
        e.printStackTrace();
        ok = false;
    }
    finally { 
        try {
            br.close();
        } catch (IOException e) {
            e.printStackTrace();
            ok = false;
        }
        database.endTransaction(); 
        }

    return ok;
}