mardi 7 avril 2015

Inconsistent results on Android SQLite query

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?


Aucun commentaire:

Enregistrer un commentaire