CODEWITHSUNDEEP
javaandroidandroid-sqlite
user11478861
user11478861

Reputation:

How to not use LIMIT when using DISTINCT with SQLiteDatabase query method?

I want to use the SQL SELECT DISTINCT book FROM bible ORDER BY book; using the SQliteDatabase query method, I try :-

Cursor csr = mDB.query(true,TABLE_BIBLE,new String[]{COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK,"-999");

Two of the 4 query have boolean true for DISTINCT, both have to have LIMIT parameter. SQLite says negative for no limit but doing this gives error like

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.bible/com.example.bible.MainActivity}: java.lang.IllegalArgumentException: invalid LIMIT clauses:-999

I know you can do with rawQuery but it not recommended to use it from Android Devloper Guide. So want to use query.

I know also you can do using large number that not good though it can confuse.

Upvotes: 0

Views: 156

Answers (2)

laalto
laalto

Reputation: 152847

SQLite says negative for no limit

Where does it say so? I read the docs as:

Passing null denotes no LIMIT clause.

...

I know you can do with rawQuery but it not recommended to use it from Android Devloper Guide

rawQuery() is just fine, even if the API designers wanted you to use fancier methods for constructing the SQL.

SELECT say If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned

That's true and works in raw sqlite SQL.

If you use query(), you get additional validation from SQLiteQueryBuilder that throws this exception you're seeing. See the source.

Upvotes: 1

MikeT
MikeT

Reputation: 56958

You can use null (like for most of the other parameters (except the 1st (table name)) :-

Cursor csr = mDB.query(true,TABLE_BIBLE,new String[]{COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK,null);

Alternately you can include DISTINCT (in this case) along with the column name e.g. :-

Cursor csr = mDB.query(TABLE_BIBLE,new String[]{"DISTINCT " + COL_BIBLE_BOOK},whereclause,whereargs,null,null,COL_BIBLE_BOOK);

The first would likely be the preferable.

Upvotes: 0

Related Questions