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.

In first query you are passing '42'. sql executor will always get that as string so you don't need to use "'"(Single Quote). you can directly give value. if you are using "like" at that time you need single quotes otherwise its ok. — Armaan Stranger
– Armaan Stranger, Commented Aug 2, 2013 at 9:57

CL. · Accepted Answer · 2013-08-02 10:15:52Z

8

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.

answered Aug 2, 2013 at 10:15

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

uncle Lem Over a year ago

I tried to declare ID field as ID INTEGER PRIMARY KEY and ID TEXT PRIMARY KEY, but it didn't help. Is there any way to use selectionArgs without embedding values into selection string?

uncle Lem Over a year ago

Found it: CAST(? as INTEGER) works fine. It looks more like dirty hack, but it works.

uncle Lem Over a year ago

But, CAST(column AS TEXT) should be better solution, I guess.

uncle Lem · Accepted Answer · 2013-08-02 12:49:26Z

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);

waqaslam · Accepted Answer · 2013-08-02 10:03:01Z

0

You need to convert your int id into string before passing to your query because the parameter array is of type string. For example:

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

The reason why it works in second type of query is because you are appending the integer value with string which automatically converts the int into String. For example:

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

edited Aug 2, 2013 at 10:03

answered Aug 2, 2013 at 9:57

waqaslam

68.3k17 gold badges172 silver badges180 bronze badges

3 Comments

uncle Lem Over a year ago

Sorry, I forgot to say that getSID(db) returns String object. If it would return int, I'd just got incompatible types compile error.

Simon Dorociak Over a year ago

I prefer way with String.valueOf(i), i think it's more clean and human-readable i think.

uncle Lem Over a year ago

String.valueOf(String)? That's nonsense. I also tried to make getSID return int and use String.valueOf, but it didn't help too.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related