Android SQLite queries not retrieving datas

Question

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

this query is correct `String query1 = "SELECT * FROM " + FOLDER_TABLE + " WHERE _id = ?";` .. first check you db has a row with `_id=1` — Bharatesh, Jun 08 '16 at 14:16
@Selvin I don't know why but noone of the second queries is getting any row — Pier Giorgio Misley, Jun 08 '16 at 14:21
@skadoosh I checked it whith the first queries, I wrote in the "PS" note that with the working queries I get a row with _id=1, This is why I'm a bit confused — Pier Giorgio Misley, Jun 08 '16 at 14:22
I added the create query. Is it possible to be a problem with the INTEGER-TEXT conversion? — Pier Giorgio Misley, Jun 08 '16 at 14:25
I bet that `_id` is not 1 but some non existing in table value ... `query1` should work — Selvin, Jun 08 '16 at 14:31
@Selvin the row with _id = 1 exists since the second working query prove it, maybe the Serg's answer is the solution — Pier Giorgio Misley, Jun 08 '16 at 14:48
_id should not be passed into your String[]. You don't have it declared as a variable. You only have FOLDER_COLUMN_ID="_id" — Chris Newman, Jun 08 '16 at 14:55
Tip: The [`query()`](http://stackoverflow.com/questions/10600670/sqlitedatabase-query-method) method is an alternative way to write a select statement — OneCricketeer, Jun 08 '16 at 15:20

Serg · Accepted Answer · 2016-06-08T14:50:55.517

1

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.

edited Jun 08 '16 at 14:50

answered Jun 08 '16 at 14:43

Serg

22,285
5
21
48

I guess, as you said, the string was interpreted with something like '_id' = '1' and this might be why all my try were failing – Pier Giorgio Misley Jun 08 '16 at 14:50
About the first query I was using it only for checking if there was any row in my db :) – Pier Giorgio Misley Jun 08 '16 at 14:51
this was the problem, the db was replacing my strings in String[] with ' before and after the value. thanks – Pier Giorgio Misley Jun 08 '16 at 15:03

Chris Newman · Answer 2 · 2016-06-08T15:14:29.360

It looks like you are pretty close with the 2nd of your not working queries.

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

Two things though. First, I don't understand why you are using String.valueOf.

Second, I would suggest declaring a test variable, something like row_i_want, and using that instead of _id.

It looks like you dont have _id declared as a variable so passing it as a parameter would result in a null pointer exception. So your query would look something like this:

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

UPDATE for different explanation:

The reason why your 2nd query works is because you have hardcoded in the value of 1.

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

This kind of query is nice for tests and proving you have something in your db, but not when you want a user to be able to provide input and search your db. For that, you need to use a dynamic condition, you need to use ? and a String [].

Lets pretend your Android app asks the user to search by a row, and oyu have an EditText that a user can type into.

EditText et_input;

After initializing the EditText, you can pull out its contents with this:

String input = user_input.getText().toString();

Now that you have input from the user, you can use it to query your db like so:

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

Your problem in your 4th query that you mistakenly used _id as your dynamic condition with your String[].

Obviously, `String.valueOf` is there because var `_id` is declared as `int` or `long` — Selvin, Jun 08 '16 at 14:29
Technically, he doesn't have a variable called _id in his code. We only know that there is a column in his SQLite db called _id (and we know that the values in that column can be int or long) because that's what is required. — Chris Newman, Jun 08 '16 at 14:36
@ChrisNewman I omitted to post also my class since I copyed my db rows and i'm always working on them, the names posted — Pier Giorgio Misley, Jun 08 '16 at 14:47
@PierGiorgioMisley do you have a variable named _id? I see that you have a variable called FOLDER_COLUMN_ID = "_id". But that would still mean you get NullPointerException from using _id as if it was a variable. — Chris Newman, Jun 08 '16 at 14:53
@ChrisNewman if you check the second working query, i'm using "WHERE _id = 1" and it works, so the problem is not in the column's name — Pier Giorgio Misley, Jun 08 '16 at 14:56

Android SQLite queries not retrieving datas

2 Answers2