selectionArgs in SQLiteQueryBuilder doesn't work with integer values in columns

Question

I'm trying to select some data from database and I have two slices of code to do it:

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = ?", new String[]{getSID(db)}, null, null, null);

and

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = " + getSID(db), null, null, null, null);

The difference between them is that first one seems to be more correct according to documentation, but it also doesn't work - cursor is empty. Instead of the second one - I'm getting all data I need.

So I tried to execute different SQL queries on my PC with a copy of database and that's what I've got:

SELECT col1, col2, col3 FROM SomeTables WHERE (id = '42')

This one doesn't work (and this query obviously equals to query, generated by first code sample)

SELECT col1, col2, col3 FROM SomeTables WHERE (id = 42)

And this one works fine (equals to query from second code sample).

As I know, SQLite should perform type cast automatically, but something went wrong and I don't know why. Do you have any ideas about how first code sample can be fixed? (Or, perhaps, database?)

If it matters, here's simplified CREATE script of the table with id field:

CREATE TABLE SomeTable ( ID PRIMARY KEY, col1, col2, [...] )

UPD: And, by the way, getSID(db) returns String Object.

Answer 1

That query parameters can only be strings is a horrible design error in the Android database API.

Despite what the documentation says, you should use parameters only for actual string values; integer values can be safely embedded directly into the SQL string. (For blobs, you must use a function that accepts ContentValues.)

Please note that while SQLite uses dynamic typing, values of different types do not compare equal in most cases (SELECT 42='42'; returns 0). There are some cases where SQLite does automatically convert values due to type affinity (in your case, this would happen if you declared the id column as INTEGER), but this is rather counterintuitive, so it should not be relied upon.

Answer 2

According to SQLite documentation,

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

In context of my case, that means that we can't be sure what data type will be stored in columns. If you can control and convert data types when they're putting into database - you can convert id values to TEXT when adding data to database and use selectionArgs easily. But it's not an answer for my question, because I have to deal with database content as is.

So, possible solutions:

a) embed integer values in selection string without wrapping them into ':

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "id = " + getSID(db), null, null, null, null);

b) cast values from selectionArgs: CAST(? as INTEGER) or CAST(id AS TEXT). I think, converting column to TEXT is better solution, because right operand is always TEXT, but the left one can be anything. So:

cursor = builder.query(db,
                    new String[]{"col1", "col2", "col3"},
                    "CAST(id AS TEXT) = ?", 
                    new String[]{getSID(db)}, null, null, null);