0

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.

Improve this question
4
  • What is your intention with LIMIT -999? Why are you not using a positive number? By the way, using a native query is not so bad in this case, because there isn't much risk in being tightly coupled to SQLite running on the phone. Commented May 10, 2019 at 5:54
  • @TimBiegeleisen negative number is no limit. Commented May 10, 2019 at 5:58
  • Then what is the point of even using a LIMIT clause in the first place? This is probably the reason why the API is rejecting it; it doesn't see the point. What is your intend SQL query? Commented May 10, 2019 at 5:58
  • @TimBiegeleisen API wants LIMIT, in question first SQL not have LIMIT. Says public Cursor query (boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) Commented May 10, 2019 at 6:21

2 Answers 2

Reset to default
1

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.

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

4 Comments

SELECT say If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned.
Right, but again, what is the point of doing this? If you don't want an upper limit, then just leave out LIMIT :-)
@TimBiegeleisen Some round the round. SQL in question working good. Change to Android query has 4 buildres. 2 builders with distinct. Both want limit as well as distinct not think of using null but triedout numbers and expressions.
@dintheb You're right about sqlite but it's the android sqlite wrapper causing your problem. Edited to add details.
0

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.

Improve this answer

Comments

Your Answer

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