Pier Giorgio Misley Pier Giorgio Misley - 5 months ago 7
Android Question

Android SQLite queries not retrieving datas

I'm using Android

RowQueries
way to retrieve my folders from db.

this is the parameter i'm checking:

protected final static String FOLDER_COLUMN_ID = "_id";


Those queries are retrieving 1 row as expected:

String query1 = "SELECT * FROM " + FOLDER_TABLE + " WHERE 1";
Cursor c1 = db.rawQuery(query1, null);
c1.moveToFirst();


String query2 = "SELECT * FROM " + FOLDER_TABLE + " WHERE _id = 1";
Cursor c2 = db.rawQuery(query2, null);
c2.moveToFirst();


And here is all perfect. My problem is when the parameters are replaced by
?
and
string[]
.

PS: The retrieved row has the
"_id"
column with the value 1


Those queries are all not working (same debug istance, no data retrieved):

String query = "SELECT * FROM " + FOLDER_TABLE + " WHERE ? = ?";
Cursor c = db.rawQuery(query, new String[]{FOLDER_COLUMN_ID, String.valueOf(_id)});
c.moveToFirst();


String query1 = "SELECT * FROM " + FOLDER_TABLE + " WHERE _id = ?";
Cursor c1 = db.rawQuery(query1, new String[]{String.valueOf(_id)});
c1.moveToFirst();


String query2 = "SELECT * FROM " + FOLDER_TABLE + " WHERE ? = 1";
Cursor c2 = db.rawQuery(query2, new String[]{FOLDER_COLUMN_ID});
c2.moveToFirst();


I can't figure out why I have this problem, what I'm missing? Thanks all

EDIT

This is how I create my entity:

protected final static String FOLDER_TABLE = "folder";
protected final static String FOLDER_COLUMN_ID = "_id";
protected final static String FOLDER_COLUMN_PASSWORD_PROTECTED = "_password_protected";
protected final static String FOLDER_COLUMN_PASSWORD = "_password";
protected final static String FOLDER_COLUMN_NAME = "_name";
protected final static String FOLDER_COLUMN_PARENT_FOLDER_ID = "_parent_folder_id";
protected final static String FOLDER_COLUMN_PARENT_FOLDER_NAME = "_parent_folder_name";
protected final static String FOLDER_COLUMN_FULL_PATH = "_full_path";


String queryFolder = "CREATE TABLE " + FOLDER_TABLE + "(" +
FOLDER_COLUMN_ID + " INTEGER PRIMARY KEY, " +
FOLDER_COLUMN_PASSWORD_PROTECTED + " INTEGER, " +
FOLDER_COLUMN_PASSWORD + " TEXT, " +
FOLDER_COLUMN_NAME + " TEXT, " +
FOLDER_COLUMN_PARENT_FOLDER_ID + " INTEGER, " +
FOLDER_COLUMN_PARENT_FOLDER_NAME + " TEXT, " +
FOLDER_COLUMN_FULL_PATH + " TEXT " +
");";
db.execSQL(queryFolder);

Answer

Concerning the last query which is

String query2 = "SELECT * FROM " + FOLDER_TABLE + " WHERE ? = 1";
Cursor c2 = db.rawQuery(query2, new String[]{FOLDER_COLUMN_ID});
c2.moveToFirst();

This will be interpreted as .. WHERE '_id' = 1 that is it will compare text constant with int constant. rawQuery() can not substitute parameters for database objects names. If you need variable columns, it should be done the same way as you build query string with FOLDER_TABLE. Kind of

String query2 = "SELECT * FROM " + FOLDER_TABLE + " WHERE "+ FOLDER_COLUMN_ID + "= 1";

And the first query, ..WHERE 1 is effectively no WHERE at all, it returns all rows.