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

Well, looks like you're violating a constraint. What's your table schema? — Mat, Feb 26 '12 at 14:40
I agree with @Mat, you should post your create table statement. — dmon, Feb 26 '12 at 14:51
@Gilbou: so, it looks like you're violating that unique constraint you have on that table. The only one that can fix this is you by looking at the values you're trying to insert and figuring out why you're pushing duplicates. — Mat, Feb 26 '12 at 14:56
I understand... I already checked. What is weird in my opinion is that its works perfectly for the previous version of the OS. I started to receive emails from the users using my app on android 4.x. Is it possible that I use the wrong method to do the job? For example I see in the doc that execute() should not be used to insert lines (http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html#execute()). — Gilbou, Feb 26 '12 at 15:10

score 10 · Accepted Answer · edited Jun 18 '15 at 05:25

10

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++;

}

edited Jun 18 '15 at 05:25

dvrm

3,749
4
34
43

answered Feb 26 '12 at 17:00

Gilbou

5,244
6
24
27

Excellent - thanks for this. I would never have thought to rebind **ALL** parameters under 4.x – SoftWyer Mar 05 '12 at 11:39
1

How to `bind` `Boolean` values.? – Pratik Butani Nov 27 '13 at 05:23
@Pratik: Not sure if you've found your answer yet but SQLite doesn't have a boolean type. You need to use one of the other types. I use an integer 0/1 and convert on input/output. – William T. Mallard Dec 06 '13 at 15:36
Yaa Thanks @WilliamT.Mallard I have used like `statement.bindLong(1,"1");` – Pratik Butani Dec 07 '13 at 04:52
4

how to bind Integers? – Vasil Valchev Dec 10 '15 at 12:06

score 1 · Answer 2 · answered Oct 16 '14 at 20:25

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;
}

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

2 Answers2