Insert Error SqLite in Android 4.0.3

Question

i have a Sqlite problem in my tablet Android 4.0.3v;

Error:

07-28 14:28:18.495    6995-7125/com.titan.tablet E/AndroidRuntime﹕ FATAL EXCEPTION: Thread-544
android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO  tb_ingrediente  ( ID_INGREDIENTE ,  DESCRICAO ,  ATIVO ,  UNIDADE ) VALUES
(1, 'CARNE', 1, 'UN'),
(2, 'QUEIJO',  1, 'UN'),
(3, 'PRESUNTO',  1, 'UN'),
(4, 'OVO',  1, 'UN'),
(5, 'FARINHA', 1, 'UN'),
(6, 'SALADA',  1, 'UN'),
(7, 'CEBOLA',  1, 'UN'),
(8, 'COCA-COLA 2L',  1, 'UN'),
(9, 'ÓLEO', 1, 'UN'),
(10, 'TOMATE', 1, 'UN');

In my Cellphone Android 4.1.2 this problema not happen.

Insert Code:

 db.execSQL("INSERT INTO  tb_ingrediente  ( ID_INGREDIENTE ,  DESCRICAO ,  ATIVO ,  UNIDADE ) VALUES\n" +
                "(1, 'CARNE', 1, 'UN'),\n" +
                "(2, 'QUEIJO',  1, 'UN'),\n" +
                "(3, 'PRESUNTO',  1, 'UN'),\n" +
                "(4, 'OVO',  1, 'UN'),\n" +
                "(5, 'FARINHA', 1, 'UN'),\n" +
                "(6, 'SALADA',  1, 'UN'),\n" +
                "(7, 'CEBOLA',  1, 'UN'),\n" +
                "(8, 'COCA-COLA 2L',  1, 'UN'),\n" +
                "(9, 'ÓLEO', 1, 'UN'),\n" +
                "(10, 'TOMATE', 1, 'UN');" );

Could someone help me discover why the error only occurs in android 4.0.3?

why the line breaks "\n" in the sql? – Pozzo Apps Jul 28 '14 at 17:40 — Pozzo Apps, Jul 28 '14 at 17:40

score 0 · Accepted Answer · answered Jul 28 '14 at 17:42

When inserting values, you can only insert a single record at a time. There's no way to mass-insert multiple records with a single statement. If you're going to be making a large number of inserts, you can cache all of your insert statements and commit them all at once to make the process faster.

    db.beginTransaction();
    try {
        db.execSQL("delete from " + Table);
        int Xtra = cols.getColumnCount() - tabledata.get(0).size();
        for (ArrayList<String> item : tabledata) {
            for (int a=0;a<Xtra;a++) {item.add("");}
            db.execSQL("insert into " + Table + " values " + rowValues(item) + ";");
        }
        db.setTransactionSuccessful();
    } catch (Exception e) {
        Log.e("DBWrapper", "insertArrayList error: " + e.getMessage());
    } finally {
        db.endTransaction();
    }

Thank you!, That was the problem. Just do not understand why the error does not occur on my other device. On my other device the mass-insert works. — Cedil, Jul 28 '14 at 17:53

score 0 · Answer 2 · edited May 23 '17 at 12:19

The syntax for multiple rows in VALUES was only introduced in sqlite version 3.7.11:

Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.

4.0.x ICS devices usually ship with sqlite 3.7.4.

To work around it, either split the inserts to one row at a time and use transaction for performance as in Scott's answer, or use SELECT ... UNION ALL as follows:

INSERT INTO tb_ingrediente  ( ID_INGREDIENTE ,  DESCRICAO ,  ATIVO ,  UNIDADE )
  SELECT 1, 'CARNE', 1, 'UN' UNION ALL
  SELECT 2, 'QUEIJO',  1, 'UN'
  ...

Insert Error SqLite in Android 4.0.3

2 Answers2

Linked