Getting android Sqlite syntax error running in api 14

Question

I have sequence of database adapter, which dumps default values in to the tables. It works fine on the devices running on API level 19 to 21. When I tried it in API 14. I'm getting the syntax error.

I/SqliteDatabaseCpp﹕ sqlite returned: error code = 1, msg = near ",": syntax error, db=/data/data/com.myapp/databases/myappdb

Here are my sql lines

private static final String DATABASE_INSERT_SEARCH =
"INSERT INTO varngle_word (name, count, type) VALUES" +
                    " ('Kindle', 0, 'item')," +
                    " ('Google', 0, 'site');";

private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE_SEARCH);
            db.execSQL(DATABASE_INSERT_SEARCH);
   }
}

`DATABASE_INSERT_SEARCH` seems to work (http://sqlfiddle.com/#!5/1f2be) where is your `DATABASE_CREATE_SEARCH`? — Trinimon, Mar 13 '15 at 19:14
@Trinimon DATABASE_CREATE_SEARCH is another string to create table. no problem in that. "create table varngle_word (_id integer primary key autoincrement, " + "name text not null, " + "count int not null, " + "type text not null);"; — Vrangle, Mar 13 '15 at 19:24
I know, but I wondered whether table creation could have an impact on the insert. Meanwhile I found it - see answer below ... — Trinimon, Mar 13 '15 at 19:28

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

1

As per this post multi-values syntax was introduced in SQLite version 3.7.11: "Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause. ".

SQLite version 3.7.11 is only in Android 4.1 and up.

SQLite version 3.7.11 available with API levels: 19, 18, 17, 16
SQLite version 3.7.4 available with API levels: 15, 14, 13, 12, 11

Check this post for more details about SQLite versions.

Alternatively you could loop through items and insert row by row or use the following:

 INSERT INTO varngle_word (name, count, type) 
 SELECT 'Kindle', 0, 'item'
  UNION
 SELECT 'Google', 0, 'site';

See this Fiddle.

edited May 23 '17 at 12:06

Community

1
1

answered Mar 13 '15 at 19:27

Trinimon

13,839
9
44
60

Does it supports all version of sqlite? – Vrangle Mar 13 '15 at 19:36
1

`INSERT INTO (...) SELECT ...` is pretty much SQL standard. There is a note on 3.5.3: _"Fix a long-standing bug in INSERT INTO ..."_ but this is not applicable to any SQLite versions used with Android. Take care with 3.8.5: _"Fix a bug ... that caused crashes when doing an INSERT INTO ... SELECT statement ..."_. – Trinimon Mar 13 '15 at 19:46

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

1

You can't use this syntax to insert multiple rows. It comes after sqlite version 3.7.11, and android 4.0 devices usually comes with sqlite 3.7.4.

see this link (Insert Error SqLite in Android 4.0.3) for some solutions

edited May 23 '17 at 11:50

Community

1
1

answered Mar 13 '15 at 19:30

avjr

73
11

Getting android Sqlite syntax error running in api 14

2 Answers2