I can't execute "WITH RECURSIVE" clause in java (android) using ORMLite library

Question

I've a problem on this query:

WITH RECURSIVE dates(d)
AS (VALUES('2014-03-01')
    UNION ALL
    SELECT date(d, '+1 day')
    FROM dates
    WHERE d < '2014-03-31')
SELECT d AS date

If I execute this query on dedicated SqLite's application for windows, the query works fine.

If I execute this query on my android app, I receive this message as log:

/com.robertot.timereport E/SQLiteLog﹕ (1) near "WITH": syntax error
/com.robertot.timereport W/System.err﹕ java.sql.SQLException: Could not perform raw query for WITH RECURSIVE dates(d) AS (VALUES('2014-04-28') UNION ALL SELECT date(d, '+1 day') FROM dates WHERE d < '2014-06-01') SELECT d AS date

I don't understand why...

Anyway, this is my java code:

GenericRawResults<String[]> rawResults;
List<String[]> results = null;
DbHelperJob findjob = new DbHelperJob(getActivity());

      try
        {
            rawResults = findjob.getJobDao().queryRaw("WITH RECURSIVE dates(d) " +
                                                        "AS (VALUES('" + firstWeek + "') " +
                                                        "UNION ALL " +
                                                        "SELECT date(d, '+1 day') " +
                                                        "FROM dates " +
                                                        "WHERE d < '" + lastWeek + "') " +
                                                        "SELECT d AS date"

            results = rawResults.getResults();
            findjob.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

Thanks!! :)

Recursive queries in SQLite are a relatively new feature; what version are you using? — Colonel Thirty Two
– Colonel Thirty Two, Commented May 9, 2014 at 0:30
I''m developing on Android Kitkat and, in according to previous answer in this post, sqlite doesn't support this function... — user3449772
– user3449772, Commented May 9, 2014 at 6:14

Community · Accepted Answer · 2017-05-23 12:32:20Z

5

According to this, the syntax that you're looking for was added in SQLite 3.8.3 But according to this, KitKat uses 3.7.11

So, it's just not supported yet on Android.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered May 9, 2014 at 0:32

GreyBeardedGeek

30.3k2 gold badges51 silver badges71 bronze badges

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

2 Comments

user3449772 Over a year ago

Thanks! :) Anyway, bad news...Is there an alternative to retrieve some data from other query without compatibility issues? Thanks man!

Googie Over a year ago

If you are using SQLite just to get range of dates in Java, then you should just generate dates in Java (without SQLite), but if you really need dates in the SQLite query, then here's a solution: stackoverflow.com/questions/2157282/…

Collectives™ on Stack Overflow

I can't execute "WITH RECURSIVE" clause in java (android) using ORMLite library

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related