Inconsistent results on Android SQLite query

Question

I'm experiencing some unexpected inconsistencies on the result of a SQL query on Android.

Given the following Schema (which is pretty simple):

public static final String TABLE_TOTD = "totd";
public static final String COLUMN_ID = "id";
public static final String COLUMN_TG = "tg";
public static final String COLUMN_EX = "ex";

private static final String DATABASE_NAME = "TOTD.db";

private static final String DATABASE_CREATE = "CREATE TABLE "
        + TABLE_TOTD + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COLUMN_TG + " TEXT NOT NULL, "
            + COLUMN_EX + " TEXT"
        + ");";

And the database being initialized and filled with a few sample texts:

    SQLiteDatabase db = mdbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_TG, "This is a text (1)");
    values.put(MySQLiteHelper.COLUMN_EX, "Example goes here");

    db.insert(MySQLiteHelper.TABLE_TOTD, null, values);
    [...]

I'm retrieving inconsistent results when querying with SQLiteDatabase.query() The following code does what it's expected to do, by returning a Cursor with the first row of the table.

    String[] projection = null;
    String selection = "id = 1";
    String[] selectionArgs = null;
    Cursor cursor = db.query(
            MySQLiteHelper.TABLE_TOTD,
            projection,
            selection,
            selectionArgs,
            null, null, null);

It works exactly the same by changing the selection and selectionArgs like this:

    String selection = "id = ?";
    String[] selectionArgs = {"1"};

But now, differently than expected, by performing the following changes, the returned Cursor will now be empty:

    String selection = "? = ?";
    String[] selectionArgs = {"id", "1"};

Actually, trying to push anything that is not the "1" into the arguments will cause the query to return an empty cursor.

I've tried the following:

    String selection = "? = 1";
    String[] selectionArgs = {"id"};

    String selection = "id ? 1";
    String[] selectionArgs = {"="};

    String selection = "?";
    String[] selectionArgs = {"id = 1"}; //This is where I couldn't take it anymore...

Now, I'm open-minded to any sort of explanations about this. My code works, and I know that I can put all my arguments on the selection string since they do not contain any forbidden characters. I only wanted to know, why isn't it working, and why isn't there any mention to this clear limitation on the developer documentation?

score 0 · Accepted Answer · answered Apr 07 '15 at 23:36

the selection argument is the WHERE clause of your sqlite query. When you execute the query with a ? it will look this argument in the selectionArgs String array. However you can only specified the value as ? and not the key. A few valid example:

String selection = "id = ?";
String[] selectionArgs = new String[]{"1"};

is similar to

String selection = "id = 1";
String[] selectionArgs = null;

However you can not do

String selection = "? = ?";

You can also specify multiple ? in the selection.

String selection = "id = ? AND carId = ?";
String[] selectionArgs = new String[]{"1", "2"};

From the documentation:

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.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(boolean, java.lang.String, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String)

It does make sense that only the values can be replaced on the `select` statement, yet this behavior isn't explained very well on the documentation... I'm so used to C-like string formatting, that I didn't expected these limitations on this specific call. — Henpemaz, Apr 08 '15 at 01:26

Inconsistent results on Android SQLite query

1 Answers1