What is the correct way to do inserts/updates/deletes in Android SQLiteDatabase using a query string?

Question

I've been looking at the official documentation (http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) and cross-referencing with StackOverflow posts and actual observed behavior, and the documentation seems to be misleading and possibly just wrong in several respects.

I am trying to perform parameterized queries to insert/update/delete, just as one might do for selecting with rawQuery(String sql, String[] arguments). I need to do it via a query rather than by using the insert/update/delete methods on the SQLiteDatabase class because in many cases the queries span multiple tables and in any case we have written a large set of queries that we use for this app across all our platforms (also written for SQLite), and it would be highly preferable to use it as-is.

Here's what I find confusing/misleading:

• The doc says for rawQuery that statements must not be terminated by a semicolon, but it doesn't actually seem to make any difference. This matters to me because we have a huge XML document filled with queries I use in my app across several platforms and I'd prefer to keep it identical if possible.

• rawQuery doesn't seem to work for inserts (yes, I've tried with and without the semicolon). The doc don't say anything about this. I do see that it returns a Cursor, which I suppose could be an oblique hint that it only works with a select, but not necessarily -- it could simply return a zero-length or null Cursor when they was no result set.

• execSQL(String sql, Object[] bindArgs) explicitly says that it does not work with inserts, but in fact it does!

• Furthermore, although execSQL(String, Object[]) specifically tells you not to try CRUD operations, its parameterless version contains no such warning, and also works fine for that purpose (with the disadvantage of not allowing SQL parameters).

The result of all of this is that the only way I can find to successfully execute inserts with parametrized arguments is to use the one working method that the docs explicitly instruct you not to use for that purpose.

Also, it's really a bummer that rawQuery won't work for inserts/updates/deletes, because we have a generalized data layer and it would be more elegant if we could use a single, unified API call to run all our CRUD queries on the database.

So what is going on here? Is this documentation hopelessly out of date? Is it okay to use execSql to do inserts, updates, and so on? Has anyone succeeded in doing inserts with rawQuery?

---

Appendix:

Running against OS: Android 4.3.

Sample queries (what's working, what's not)

// Works
db.execSql("INSERT into MyTable (Col1, Col2) VALUES (?, ?)", new String[] { "Foo", "Bar" });
db.execSql("INSERT into MyTable (Col1, Col2) VALUES (?, ?);", new String[] { "Foo", "Bar" });

// No exception thrown, but no changes made to the database
db.rawQuery("INSERT into MyTable (Col1, Col2) VALUES (?, ?)", new String[] { "Foo", "Bar" });
db.rawQuery("INSERT into MyTable (Col1, Col2) VALUES (?, ?);", new String[] { "Foo", "Bar" });

Answer 1

You are right. The documentation is confusing. Overall, the design attempts to provide a convenient Java wrapper around the sqlite3 C API. For most of the time it works fine if you use it the way the designers intended, e.g. using the convenience methods for CRUD operations. But they also needed to provide raw query methods execSQL() and rawQuery() for cases where the convenience CRUD methods are not powerful enough or not applicable at all (CREATE TABLE and so on). This causes leaky abstractions.

The doc says for rawQuery that statements must not be terminated by a semicolon, but it doesn't actually seem to make any difference. This matters to me because we have a huge XML document filled with queries I use in my app across several platforms and I'd prefer to keep it identical if possible.

The docs are bad. In fact, Android SQLiteDatabase itself calls rawQuery with a semicolon-terminated query string.

rawQuery doesn't seem to work for inserts (yes, I've tried with and without the semicolon). The doc don't say anything about this. I do see that it returns a Cursor, which I suppose could be an oblique hint that it only works with a select, but not necessarily -- it could simply return a zero-length or null Cursor when they was no result set.

It does work but you'll need to understand how it works on native level.

Think of execSQL() as sqlite3_exec() that runs the query and returns either success or an error code.

Think of rawQuery() as sqlite3_prepare() that compiles the query but does not run it yet. To actually run it, use one of the moveTo...() methods on the Cursor. Think of this as sqlite3_step(). Combining any rawQuery() with moveTo...() will actually alter the database.

execSQL(String sql, Object[] bindArgs) explicitly says that it does not work with selects, but in fact it does!

Furthermore, although execSQL(String, Object[]) specifically tells you not to try CRUD operations, its parameterless version contains no such warning, and also works fine for that purpose (with the disadvantage of not allowing SQL parameters).

It works fine for all CRUD operations. For the R read part of CRUD, there's just no way to get the selected data.