Android SQLiteException: near ",": syntax error: , while compiling INSERT

Question

I'm trying to insert values into table

public class DatabaseHandler extends SQLiteOpenHelper {
    private final static String INSERT_INTO_COUNTRIES = "INSERT INTO " + TABLE_COUNTRIES
                + " VALUES";

    @Override
    public void onCreate(SQLiteDatabase db) {
            final String countries = "('1','Andorra','93','flag_andorra','AND'),
        ('2','Austria','43','flag_flag_austria','AUT')";
        String query = INSERT_INTO_COUNTRIES + countries + ";";
        db.execSQL(query);
    }

}

And get the following error:

android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO cardberry_countries VALUES('1','Andorra','93','flag_andorra','AND'),('2','Austria','43','flag_flag_austria','AUT');

Error occurs on Android 4.0.4 HTC Sense 3.6. I tested on multiple devices runnin on Android 4.2 and higher and it works fine.

Does anybody know what's the problem?

Answer 1

The multi-values syntax was introduced in sqlite version 3.7.11

Sqlite version 3.7.11 is only in Android 4.1 and up.

Split your inserts into separate inserts, one row at a time.

Answer 2

It'll be easier to spot if you print out the SQL INSERT string.

But even if you fix the problem, this is still a bad way to go. You ought to be using PreparedStatement to escape and bind variables. It'll be less error prone. You'll be safer from SQL injection attacks as an added bonus.