Enhance Insert on API 15 (ICS) to still allow inserting multiple sets of values at once

[monzee]

I had a statement like this in an onTablesCreated

tryExecStatement(Insert.into(Group.TABLE)
  .columns(Group.NAME, Group.DESCRIPTION, Group.ROLE)
  .values("admin", "Admin", 1)
  .values("staff", "Staff", 2)
  .values("users", "Default group", 3));

My onError handler captured this exception message in an API 15 AVD:

Failed to execute statement: INSERT INTO groups (name,description,role) VALUES (?,?,?),(?,?,?),(?,?,?)
    android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO groups (name,description,role) VALUES (?,?,?),(?,?,?),(?,?,?)

Multi-row inserts are supported in SQLite 3.7.11 and above. ICS uses 3.7.4.

A workaround is to explode the statement into individual statements.

[sbosley]

Thanks for reporting this! We'll work on a fix.

[sbosley]

For now, we're going to throw an exception if you try to add multiple values clauses to an Insert statement on API < 16. This is really just status quo with writing raw SQL, so it's a temporary measure to make sure things don't fail silently. We've also added documentation to the values() method to reflect the change. In the future, we'd like to do some things behind the scenes to split the insert up into multiple statements so that we can remove this restriction, but that's more of a feature request that we'll need a little more time to think about. I'm going to update the issue title to reflect that future direction.

[monzee]

Nice. I can live with it; squidb has already bought me a lot. Insert queries like these only appear in migration and setup methods only anyway. It's a minor annoyance at worst.

[sbosley]

Cool, good to hear. For what its worth, some planned future enhancements to the API will allow you to do dao-style inserts using model objects even from within migration and setup, which should further reduce the need for the Insert objects.

[jdkoren]

Fun fact: you can do this inside of setup/migration methods:

DatabaseDao dao = new DatabaseDao(this);

Although, as @sbosley noted, future changes should obviate the need for this.