SelectionArgs in SQLiteQueryBuilder does not work with integer values ​​in columns

Question

I am trying to select some data from a database, and I have two pieces of code:

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 the two is that the first seems more correct according to the documentation, but it also does not work - the cursor is empty. Instead of the second, I get all the necessary data.

So, I tried to execute different SQL queries on my computer with a copy of the database and what I have:

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

This does not work (and this query is obviously equal to the query generated by the first code sample)

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

And this one works fine (equal to the request from the second code sample).

As I know, SQLite should do the type auto run, but something went wrong and I don’t know why. Do you have any ideas on how to fix the first code sample? (Or maybe a database?)

If that matters, the CREATE script of the table with the id field is simplified here:

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

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

Answer 1

According to the SQLite documentation ,

Any column in the SQLite version 3 database, with the exception of the INTEGER PRIMARY KEY column, can be used to store the value of any storage class.

In the context of my case, this means that we cannot be sure what type of data will be stored in the columns. If you can manage and convert data types when they are entered into the database, you can convert the id values ​​to TEXT when adding data to the database and it is easy to use selectionArgs . But this is not the answer to my question, because I have to deal with the contents of the database as it is.

So, possible solutions:

a) insert integer values ​​in the selection line without transferring them to ' :

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

b) distinguishes values ​​from selectionArgs: CAST(? as INTEGER) or CAST(id AS TEXT) . I believe that converting a column to TEXT is the best solution, because the right operand is always TEXT , but anything can be left. So:

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

Answer 2

These query parameters can only be strings - this is a terrible design error in the Android database API.

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

Note that while SQLite uses dynamic typing , values ​​of different types are not compared the same in most cases ( SELECT 42='42'; returns 0 ). There are cases where SQLite automatically converts values ​​due to the affinity type (in your case, this will happen if you declare the id column as INTEGER ), but it is rather inconsistent, so you cannot rely on it.

Answer 3

You need to convert the int id to string before moving on to your request, because the parameter array has a string of type. For instance:

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

The reason it works in the second type of query is because you add an integer value with a string that automatically converts int to String. For instance:

 int i = 10; String s = i + ""; //now 10 is in string