Android bulk insert syntax error near ","

Question

I would appreciate a shade of enlightenment on the following code, since I just can't seem to figure out why it has a syntax error near ",".

I'm trying to insert multiple rows at once in a single statement. I'm targeting API 10+. If I only have one set of VALUES(...) it's all good, and the row gets inserted, but as soon as I have more than one, it sends out an exception with the syntax error message.

SQLiteDatabase db = getWritableDatabase();
        String insertRows = "INSERT INTO " + TABLE_FRIENDS +
                " (" + KEY_USER_EMAIL + ", " + KEY_FRIEND + ", " + KEY_FRIEND_INTERACTIONS +
                       ", " + KEY_ENABLED +") VALUES ";

        int index; 
        for(index=0; index<friends.size(); ++index){
            insertRows += "('" + userAccount.getEmail() +"', ";

            Friend friend = friends.get(index);

            insertRows+= "'" + friend.getEmail() +"', ";
            insertRows+= "'" + friend.getInteractions() +"', ";

            int enabled = friend.isEnabled() ? 1 : 0;

            insertRows+= "'"+ enabled + "')";

            if(index != friends.size()-1)
                insertRows+= ",";
        }

        insertRows += ";";

        db.execSQL(insertRows);
        db.close();

I print the query out on my log and get:

INSERT INTO friends (user_email, friend_email, friend_interactions, friend_enabled) VALUES ('test2@test.com', 'vila@hotmail.com', '0', '0'),
('test2@test.com', 'anotherone@hotmail.com', '0', '0'),
('test2@test.com', 'yetanother@hotmail.com', '0', '0');

Help is very much appreciated.

instead of building an insert query like this, why dont you use contentvalues and perform inserts ?? you are anyways running a loop — Rat-a-tat-a-tat Ratatouille, Mar 26 '14 at 00:03
I thought multiple inserts would be bad for performance against a single insert with all the values. — Chayemor, Mar 26 '14 at 00:21
yes, it is bad, bt i am doubting that the `,` after the first values combo causes the problem, or search for bulk insert in android and ul get a better solution using insert helper but it is deprecated. — Rat-a-tat-a-tat Ratatouille, Mar 26 '14 at 04:00

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

The multi-values syntax was added in sqlite 3.7.11. Many devices ship with an older version.

For compatibility, insert only one row at a time. For performance, consider the following:

Use compileStatement() to pre-compile your SQL and just change the bind args for each row.
Wrap the inserts in a transaction to minimize waiting on I/O.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 26 '14 at 06:30

laalto

150,114
66
286
303

Will look into compileStatement() and report later. – Chayemor Mar 26 '14 at 21:26

score -1 · Answer 2 · edited May 23 '17 at 12:28

-1

Android bulk insert syntax error near ","

2 Answers2