Search code examples
androidandroid-sqlite

Create VIEW using my SQLiteDatabase.execSQL(String, Object[])

I am trying to create a view using the following source code:

SQLiteDatabase db = mManagerDbHelper.getWritableDatabase();

String sql = "SELECT * FROM users WHERE name = ?";
String[] selectionArgs = new String[] {"Bob"};
db.execSQL("CREATE VIEW bob_user AS " + sql, selectionArgs);

However, this code always returns this error:

android.database.sqlite.SQLiteException: parameters are not allowed in views (code 1)

How do I use the bindArgs parameter of execSQL(String, Object[]) method?

Solution

  • How do I use the bindArgs parameter of execSQL(String, Object[]) method?

    In short you can't, a VIEW is intended to be stored and thus cannot be dynamically changed and hence why you can't bind parameters.

    You could however get around it by using an expression for the right hand expression of the WHERE clause and if that expression were to select the value from a table then the value could be changed in that table (i.e. mimicbind in the following) e.g.

    DROP VIEW IF EXISTS a_user;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS mimicbind;
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES('bob'),('mary'),('fred'),('sue');
CREATE TABLE IF NOT EXISTS mimicbind (mimicname TEXT PRIMARY KEY, value TEXT);
INSERT INTO mimicbind VALUES('a_user','bob');
CREATE VIEW a_user AS SELECT * FROM users WHERE name = (SELECT value FROM mimicbind WHERE mimicname = 'a_user');

SELECT * FROM a_user;
UPDATE mimicbind SET value = 'sue' WHERE mimicname = 'a_user';
SELECT * FROM a_user;
DROP VIEW IF EXISTS a_user;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS mimicbind;

    The result from the queries being:-

    enter image description here

    and then after the value column in the mimicbind table is changed from bob to sue then:-

    enter image description here

    However, you may wish to consider what benefit there is to using a view as it would appear that for what you want it is an unnecessary complexity, when a straight forward select would likely be as, if not more efficient. That is a VIEW will, I believe. take up a minimum of 4k per VIEW so bob, alice, etc .... and that's going to be storage space largely wasted.

    Even the solution via another table is probably not going to afford any benefit over just using a query. The end result of both would be a Cursor accessing exactly the same core data *(of course in the solution then mimicbind table is additionally accessed)(