Android Sqlite Selection Args[] Incorrect Value?

I have different variants of a query. All value types are Strings! (don't ask why...) The first one might look like this: SELECT * FROM item WHERE arg1=false The string ('sqlWhere

Solution 1:

Use the SQLiteDatabase methods intead.

SELECT*FROM item WHERE (arg1=40OR arg2=42) AND arg3=false

should be

SQLiteDatabase db;
String table = "item";
String projection = null;
String selection = "(arg1=? OR arg2=?) AND arg3=?";
String[] selectionArgs = newString[] { "40", "42", "false" };
String sortOrder = null;

db.query(item, projection, selection, selectionArgs, sortOrder);

It's not as concise, but it's much safer in that it'll protect against SQL injections and is less likely to cause problems (such as SQL syntax errors, etc.).

Solution 2:

The documentation for SQLiteDatabase.query() says:

selectionArgs - You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.

When you execute this query:

SELECT*FROM item WHERE (arg1=40OR arg2=42) AND arg3=false

and bind the arguments using the selectionArgs-parameter, the integers and the boolean will be bound as strings. This is not the same as in your database. I know you said all your columns are of type TEXT, but read the SQLite Documentation:

The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another.

That means, when you insert your integer values in your string-table, SQLite will convert those and store them as integers. The next important point is comparison:

SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. [...]

• An expression that is a simple reference to a column value has the same affinity as the column.

The docs give the following example:

CREATETABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Because column "a" has text affinity, numeric values on the-- right-hand side of the comparisons are converted to text before-- the comparison occurs.SELECT a <40,   a <60,   a <600FROM t1;
0|1|1

In your case that means the following:

1. Your TEXT-table gets integers inserted, which are then converted (and stored) as INTEGERs.
2. When performing your comparison, you give in TEXT-values (which are your integers). Those should be (but don't need to be) converted to TEXT-values, because your table indicates it's values are of type TEXT.
3. You compare INTEGER to TEXT, which is not equal and therefor doesn't give you the correct results.

---

To insert different data-types (other then string) into the SQLiteDatabase, use Prepared Statements.

Solution 3:

Ok, arg1 is the column name, it is not a variable name. So, when you do the first query, you are looking for all rows where the column named arg1=false. In all the subsequent queries you add column arg2 and then column arg3, are there columns arg2 and arg3? Can you post the code you used to create the table?