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, May 09 '14 at 00:30
I''m developing on Android Kitkat and, in according to previous answer in this post, sqlite doesn't support this function... — user3449772, May 09 '14 at 06:14

score 5 · Answer 1 · edited May 23 '17 at 12:32

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 '17 at 12:32

Community

1
1

answered May 09 '14 at 00:32

GreyBeardedGeek

29,460
2
47
67

Thanks! :) Anyway, bad news...Is there an alternative to retrieve some data from other query without compatibility issues? Thanks man! – user3449772 May 09 '14 at 06:10
1

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: http://stackoverflow.com/questions/2157282/generate-days-from-date-range – Googie May 09 '14 at 14:25

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

1 Answers1