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

by

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.

More Related Contents:

  1. SQLite in Android How to update a specific row
  2. Can’t add parameter ‘this’
  3. How to use an existing database with an Android application [duplicate]
  4. SQlite Getting nearest locations (with latitude and longitude)
  5. Any good ORM tools for Android development? [closed]
  6. Simple export and import of a SQLite database on Android
  7. Foreign key constraints in Android using SQLite? on Delete cascade
  8. Using Singleton design pattern for SQLiteDatabase
  9. Database not copying from assets
  10. Android search with Fragments
  11. Which Android Data Storage Technique to use?
  12. Get generated id after insert
  13. How to persist permission in android API 19 (KitKat)?
  14. Upgrade SQLite database from one version to another?
  15. Android: Cannot perform this operation because the connection pool has been closed
  16. SQLiteOpenHelper failing to call onCreate?
  17. What is the use of BaseColumns in Android
  18. The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption
  19. Android : Table has no column named “variable name” MySql Database error
  20. android.database.sqlite.SQLiteException: no such column
  21. UNIQUE constraint failed: sqlite database : android
  22. How To Test If Cursor Is Empty in a SQLiteDatabase Query
  23. android.database.CursorWindowAllocationException when moving a Cursor
  24. Read data from SQLite where column name contains spaces
  25. Is it OK to have one instance of SQLiteOpenHelper shared by all Activities in an Android application?
  26. Got a NumberFormatException [duplicate]
  27. Date object SimpleDateFormat not parsing timestamp string correctly in Java (Android) environment
  28. ‘:app:lintVitalRelease’ error when generating signed apk
  29. Android change Material elevation shadow color
  30. What does transitive = true in Gradle exactly do (w.r.t. crashlytics)?

Leave a Comment