Get all unique values from SQLite column as string array

Question

I try to get all unique values from database coulmn using SELECT DISTINCT sql command. But i get exception when my activity is loading, i have this error code in logcat:

05-05 09:08:32.637: E/AndroidRuntime(1314): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.workoutlog/com.example.workoutlog.AddWorkOutPage}: android.database.sqlite.SQLiteException: near "SELECT": syntax error (code 1): , while compiling: SELECT * FROM exerciseTable WHERE SELECT DISTINCTexercise_typefromexerciseTable

I think that i have not wrote the command correctly, here is my code:

public String[] getAllExercies() {
        String selecet = "SELECT DISTINCT" + COLUMN_EXERCISE + "from" + TABLE_NAME;
        Cursor c = ourDatabase.query(TABLE_NAME, null, selecet, null, null, null, null);
        int dayExercise = c.getColumnIndex(COLUMN_EXERCISE);

        String[] list = new String[c.getCount()-1];
        int j = 0;
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
            list[j] = c.getString(dayExercise);
            j++;
        }

        return list;
    }

score 3 · Accepted Answer · edited May 23 '17 at 12:09

3

I think you should first checkout these answers here and here in order to see the working of .query() function. Please note that while using ourDatabase.query() function, the parameters are as follows:

String Table Name: The name of the table to run the query against
String [ ] columns: The projection of the query, i.e., the columns to retrieve
String WHERE clause: where clause, if none then pass null
String [ ] selection args: The parameters of the WHERE clause
String Group by: A string specifying group by clause
String Having: A string specifying HAVING clause
String Order By by: A string Order By by clause

So your third variable should be a WHERE clause, something like:

String[] args = { "first string" };
Cursor c = ourDatabase.query("TABLE_NAME", null, "exercise_type=?", args, null, null, null);

Since you don't need a WHERE clause, for your purposes you might want to use rawQuery() method instead.

String selecet = "SELECT DISTINCT " + COLUMN_EXERCISE + " FROM " + TABLE_NAME;
ourDatabase.rawQuery(selecet, null);

Update Try the answer from here. Do something like this:

Cursor c = ourDatabase.query(true, "exerciseTable", new String[] {"exercise_type"}, null, null, "exercise_type", null, null, null);
int dayExercise = c.getColumnIndex(COLUMN_EXERCISE);
//... continue with your further code

Hope this helps else please comment.

edited May 23 '17 at 12:09

Community

1
1

answered May 05 '13 at 09:39

Shobhit Puri

25,769
11
95
124

Thank you! My loop to convert the Cursor to string array will work good with rawQuery method? – tomer May 05 '13 at 10:16
Yes. It should. The Cursor concept is the same but just the way of querying is just different for both. If you just have a simple Sqllite query, you can directly use rawQuery() method to run it. – Shobhit Puri May 05 '13 at 10:38
*You need not change anything in the loop. – Shobhit Puri May 05 '13 at 10:45
I tried it, and i still got force close. here is the cat log:' 05-05 10:17:43.507: E/AndroidRuntime(1850): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.workoutlog/com.example.workoutlog.AddWorkOutPage}: android.database.sqlite.SQLiteException: near "SELECT": syntax error (code 1): , while compiling: SELECT * FROM exerciseTable WHERE SELECT DISTINCT exercise_typefrom exerciseTable – tomer May 05 '13 at 10:47
You can see from the error that the query you are making is wrong: `SELECT * FROM exerciseTable WHERE SELECT DISTINCT exercise_typefrom exerciseTable`. It should be something like: `SELECT DISTINCT exercise_type from exerciseTable`. See my updated answer. – Shobhit Puri May 05 '13 at 10:58
Thanks man, its finally works :) that line is working: Cursor c = ourDatabase.query(true, "exerciseTable", new String[] {"exercise_type"}, null, null, "exercise_type", null, null, null); – tomer May 05 '13 at 11:55

Mehul Joisar · Answer 2 · 2013-05-05T10:57:24.080

1

Issue: you have not maintained the space between the words.

Explaination:

suppose, String COLUMN_EXERCISE = "exercise";

and String TABLE_NAME = "tbl_workout";

then String selecet = "SELECT DISTINCT" + COLUMN_EXERCISE + "from" + TABLE_NAME;

simply means,SELECT DISTINCTexercisefromtbl_workout

Solution:

String selecet = "SELECT DISTINCT " + COLUMN_EXERCISE + " from " + TABLE_NAME;

Edit:

Kindly use following syntax to fire rawQuery

Cursor c = ourDatabase.rawQuery(selecet,null);

I hope it will be helpful !

edited May 05 '13 at 10:57

answered May 05 '13 at 10:08

Mehul Joisar

15,348
6
48
57

I tried it. I still got force close. here is the cat log:' 05-05 10:17:43.507: E/AndroidRuntime(1850): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.workoutlog/com.example.workoutlog.AddWorkOutPage}: android.database.sqlite.SQLiteException: near "SELECT": syntax error (code 1): , while compiling: SELECT * FROM exerciseTable WHERE SELECT DISTINCT exercise_typefrom exerciseTable – tomer May 05 '13 at 10:22

score 0 · Answer 3 · answered May 05 '13 at 09:23

0

You miss all the spaces in your query, you should replace with this:

String selecet = "SELECT DISTINCT " + COLUMN_EXERCISE + " FROM " + TABLE_NAME;

answered May 05 '13 at 09:23

Yoann Hercouet

17,894
5
58
85

i knew it was there... Thank you for the answer :) – tomer May 05 '13 at 10:14
I still got force close. here is the cat log:' 05-05 10:17:43.507: E/AndroidRuntime(1850): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.workoutlog/com.example.workoutlog.AddWorkOutPage}: android.database.sqlite.SQLiteException: near "SELECT": syntax error (code 1): , while compiling: SELECT * FROM exerciseTable WHERE SELECT DISTINCT exercise_typefrom exerciseTable – tomer May 05 '13 at 10:19

Get all unique values from SQLite column as string array

3 Answers3