What is the correct way to insert / update / delete in Android SQLiteDatabase using query string?

Question

I looked at the official documentation ( http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html ) and cross-references with StackOverflow messages and the actual observed behavior, and the documentation seems to be misleading and maybe just wrong in several ways.

I am trying to execute parameterized queries for insert / update / delete, as it can be done for selection using rawQuery(String sql, String[] arguments). I need to do this with a query, and not with the insert / update / delete methods in SQLiteDatabase because in many cases the queries span multiple tables, and in any case we wrote a large set of queries that we use for this application on all our platforms (also written for SQLite), and it would be very preferable to use it as is.

Here is what I find confusing / misleading:

• The rawQuery document says that instructions should not end with a semicolon, but in fact it does not make any difference. It matters to me because we have a huge XML document filled with queries that I use in my application on several platforms, and I would prefer it to be identical if possible.

• rawQuery doesn't seem to work for inserts (yes, I tried with and without a semicolon). Doc says nothing about this. I see that it returns a cursor, which, I suppose, may be an oblique hint that it only works with the selected one, but not necessarily - it can simply return a zero or zero cursor when they were not set.

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

• In addition, although execSQL(String, Object[]) specifically tells you not to try to perform CRUD operations, its parameterless version does not contain such a warning, and is also great for this purpose (with a drawback that does not allow SQL parameters).

The result of all this is that the only way to find successful insertions with parameterized arguments is to use the working method that the documents explicitly instruct you not to use for this purpose.

In addition, it is really a bummer that rawQuery will not work for attachments / 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 requests in database.

So what is going on here? Is this documentation hopelessly outdated? Is it possible to use execSql to insert, update, etc.? Has anyone succeeded in performing inserts using rawQuery?

---

Application:

Work with OS: Android 4.3.

Query examples (what works, what doesn't)

 // 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're right. The documentation is confusing. In general, the project tries to provide a convenient Java shell around the sqlite3 C API. For most of the time, it works fine if you use it as designers intended, for example. using convenience methods for CRUD operations. But they also needed to provide raw execSQL() and rawQuery() query methods for cases where the convenient CRUD methods are not powerful enough or not applicable at all ( CREATE TABLE , etc.). This leads to leaky abstractions.

In the rawQuery document rawQuery doc says that statements should not end with a semicolon, but in fact it does not make any difference. This matters to me, because we have a huge XML document filled with queries that I use in my application on several platforms, and I would prefer to save it if possible.

Documents are bad. In fact, Android SQLiteDatabase calls rawQuery with a query length with a semicolon .

rawQuery doesn't seem to work for inserts (yes, I tried with and without a semicolon). Doc says nothing about this. I see that it returns a cursor, which, I suppose, may be an oblique hint that it only works with the selected one, but not necessarily - it can simply return a zero or zero cursor when they were not set.

It works, but you need to understand how it works at the native level.

Think of execSQL() as sqlite3_exec() , which starts a query and returns either success or an error code.

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

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

In addition, although execSQL(String, Object[]) specifically tells you not to try to perform CRUD operations, its parameterless version does not contain such a warning, and is also great for this purpose (with a drawback that does not allow SQL parameters).

It works great for all CRUD operations. For the R read part of CRUD, there is no way to get the selected data.

Answer 2

You want to use execSQL () for insertions. Or you can do it like this:

 ContentValues values = new ContentValues(); values.put ("col1", "foo"); values.put ("cols2", "bar"); getDb().insertOrThrow ("MyTable", null, values); 

For queries, you can use rawQuery.