2

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;
    }
Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you! My loop to convert the Cursor to string array will work good with rawQuery method?
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.
*You need not change anything in the loop.
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
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.
|
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 !

Improve this answer

1 Comment

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
0

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

String selecet = "SELECT DISTINCT " + COLUMN_EXERCISE + " FROM " + TABLE_NAME;
Improve this answer

2 Comments

i knew it was there... Thank you for the answer :)
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.