Android Studio - SQLite Cursor not returning any data

Question

I am trying to return the names of distinct values in the 'category' column of an SQLite database. The cursor does not appear to be returning any results.

I'm building an Android app using a pre-existing database of bird species. The aim is to allow the user to explore the database through the app. The problem I'm having is in trying to return the distinct categories of bird species that exist in the database. The database appears to be opening successfully - no SQLite exception is being thrown, - but after using the query, the '.moveToNext' method does not appear to be returning any data.

public ArrayList<String> getCategory(String[] name){
        String TABLE_BIRDS = "main";
        String[] COLUMN = name;
        ArrayList<String>categories = new ArrayList<>();

        if (name[0]!=null)
        {
            Log.d(LOG_TAG, name[0]);
        } else {
            Log.d(LOG_TAG, "name[0] has not been passed");
        }
        x = db.query(true, TABLE_BIRDS, new String[] { COLUMN[0] } , null, null, COLUMN[0], null, null, null );
        if (x.moveToNext()) {
            Log.i(LOG_TAG, x.getString(x.getColumnIndex("category")));
        }
        else {
            Log.i(LOG_TAG, "The cursor is not returning any data");
        }
        //Simple cursor loop
        if (x.moveToNext()){
            String category = new String();
            category = x.getString(x.getColumnIndex(category));
            categories.add(category);
            Log.i("cursor loop", category);
        }
        return categories;

In the above code, the log messages are showing that: getCategory is receiving the expected string "category" before the query, but right after the query, the if/else loop is reporting that "The cursor is not returning any data".

What I expected is that the query would return six Strings, the cursor loop would add them to the ArrayList 'categories', and this ArrayList would be returned.

Please any help would be appreciated.

Answer 1

Assuming the cursor is not null, you should iterate over the results with a while/for loop.

e.g :while(x.moveToNext()) { //your logic }

It is always useful when debugging this kind of issues to install an SQLite DB browser then check the cursor's query against your DB to see if you have any data.

Answer 2

Here is a simpler version of your method:

public ArrayList<String> getCategory(String[] name) {
    String TABLE_BIRDS = "main";
    ArrayList<String> categories = new ArrayList<>();

    if (name[0] != null) {
        Log.d(LOG_TAG, name[0]);
    } else {
        Log.d(LOG_TAG, "name[0] has not been passed");
    }

    Cursor x = db.query(true, TABLE_BIRDS, new String[]{name[0]}, null, null, null, null, null, null);

    while (x.moveToNext()) {
        String category = x.getString(0);
        categories.add(category);
        Log.i("cursor loop", category);
    }

    if (x.getCount() == 0) {
        Log.i(LOG_TAG, "The cursor is not returning any data");
    }
    x.close();

    return categories;
}

I guess name[0] contains the string 'category' which is the column that you want to query.
Since you pass true as the 1st argument in the method query() this means that you will get distinct values, so no need for the group by argument.
You don't need the variable COLUMN since you have what you need in the variable name.
Also you don't need getColumnIndex() since the cursor returns only 1 column.
Edit
Instead of:

Cursor x = db.query(true, TABLE_BIRDS, new String[]{name[0]}, null, null, null, null, null, null);

try rawQuery():

Cursor x = db.rawQuery("SELECT DISTINCT " + name[0] + " FROM " + TABLE_BIRDS, null);