Android SQLite Selection Args[] with Int Values

Android Sqlite selection args[] with int values

The answer is in the title: you are trying to pass integer values into the query, but what you actually end up with are string values.

This is a horrible design bug in the Android database API; you can use parameters only for strings.

Integer numbers do not have the formatting and SQL injection problems that string values would have, so you can just insert the numbers directly into the SQL expression:

Cursor c = database.query(
    DATABASE_TABLE, columns, 
    KEY_YEAR  + "=" + year  + " AND " +
    KEY_MONTH + "=" + month + " AND " +
    KEY_DAY   + "=" + day,
    null, null, null,
    KEY_MONTH + "," + KEY_DAY);

(And the orderBy syntax was wrong.)

Using int value in selection args argument of sqlite for android

Yes, you can use the following ways to convert int to String.

int i=10;
String[] str = new String[]{String.valueOf(i)};
String[] str1 = new String[]{Integer.toString(i)};

Android Sqlite selection args[] incorrect value?

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=40 OR 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:

CREATE TABLE 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 < 600 FROM 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.

Android SQLite query with integer parameter

Second option with ? is the suggested way of using query method.

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.

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

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

---

Related Topics