android.database.sqlite.SQLiteException: near ","

Question

I have many crashes in some android devices. (I dont have this crash on my phone LG G3s). So, it my code:

public void doUpdateDB(int appVersionCode) {

    if (getProperty(DB_VERSION).length() == 0) { // Before versionCode 36
        db.execSQL("INSERT INTO `dn_catalog_model` (`model_id`, `mark_id`, `model_name`) VALUES\n" +
                "(2022, 11, 'S1'),\n" +
                "(2023, 201, 'Runner 700C'),\n" +                  
                "(2463, 160, 'Nomad');");
    }

}

and I have crash:

android.database.sqlite.SQLiteException: near ",": syntax error: INSERT INTO `dn_catalog_model` (`model_id`, `mark_id`, `model_name`) VALUES

I know about methods dataBaseOpenHelper ))

Phantômaxx · Accepted Answer · 2014-12-05T09:04:59.670

2

Prior to as of SQLite 3.7.11 you can insert only one record with an INSERT command.

i.e.:

public void doUpdateDB(int appVersionCode)
{
    if (getProperty(DB_VERSION).length() == 0)
    { // Before versionCode 36
        db.execSQL("INSERT INTO dn_catalog_model (model_id, mark_id, model_name) VALUES " +
                "(2022, 11, 'S1')";
        db.execSQL("INSERT INTO dn_catalog_model (model_id, mark_id, model_name) VALUES " +
                "(2023, 201, 'Runner 700C')";                  
        db.execSQL("INSERT INTO dn_catalog_model (model_id, mark_id, model_name) VALUES " +
                "(2463, 160, 'Nomad')");
    }
}

As of SQLite 3.7.11 multiple record insertion is supported in SQLite:

public void doUpdateDB(int appVersionCode)
{
    if (getProperty(DB_VERSION).length() == 0)
    { // Before versionCode 36
        db.execSQL("INSERT INTO dn_catalog_model (model_id, mark_id, model_name) VALUES " +
                "(2022, 11, 'S1'), (2023, 201, 'Runner 700C'), (2463, 160, 'Nomad')");
    }
}

edited Dec 05 '14 at 09:04

answered Dec 05 '14 at 08:22

Phantômaxx

37,901
21
84
115

Actually you can insert multiple records in one line: http://dev.mysql.com/doc/refman/5.5/en/insert.html See [this question](http://stackoverflow.com/questions/12502032/insert-multiple-rows-with-one-query-mysql) – JorgeGRC Dec 05 '14 at 08:30
Re-read that post. It's about **mySQL**! – Phantômaxx Dec 05 '14 at 08:32
1

whoops, you're right, anyways I'll leave this one here about [how to insert multiple values in SQLite in one line](http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) – JorgeGRC Dec 05 '14 at 08:34
A little overkill, for the everyday queries, but it's a nice trick. – Phantômaxx Dec 05 '14 at 08:40
1

Just came across this, I guess SQLite now supports multiple records in one line: [Link to documentation](http://www.sqlite.org/lang_insert.html) – JorgeGRC Dec 05 '14 at 08:45
1

*"The first form (with the "VALUES" keyword) creates one or more new rows in an existing table"* . As you can see, there is a feedback in the last stage of the flow diagram, where multiple VALUES could be inserted, couldn't them?. – JorgeGRC Dec 05 '14 at 08:52
Yes, it seems you are right. I overlooked that one! – Phantômaxx Dec 05 '14 at 08:58

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

Why are you using those \n ? Can you try to execute the query without them?

EDIT: For SQLite see this answer: https://stackoverflow.com/a/5009740/3736964

SQLite supports now multiple records inserting in one line, as understood from documentation here.

Whenever you make a SQL query, you have to cut off the \n (this is why you're getting errors). The correct code would be:

public void doUpdateDB(int appVersionCode) {

if (getProperty(DB_VERSION).length() == 0) { // Before versionCode 36
    db.execSQL("INSERT INTO `dn_catalog_model` (`model_id`, `mark_id`, `model_name`) VALUES " +
            "(2022, 11, 'S1'), " +
            "(2023, 201, 'Runner 700C'), " +                  
            "(2463, 160, 'Nomad');");
}

}

You can always execute a SQL query in just one line, without linebreaks, actually the engine will just ignore them, you'll usually see the queries with linebreaks but that's just for making it easier to read :)

android.database.sqlite.SQLiteException: near ","

2 Answers2