sqlite Query is not working in iOS 8.0 - 8.1 and working in IOS 8.2

Question

Sql Query is not working in IOS 8.0 8.1 and working in IOS 8.2 Please find the below sqlite query.

-(BOOL)insertRecordsFromlastDateToCurrentDate{

    NSString *resultString = @"";
    if(database == nil)
    {
        NSString *dbPath = [DatabaseHandler getDBPath];

        if (sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK) {

            NSAssert1(0, @"Error while opening Database. '%s'", sqlite3_errmsg(database));
            return nil;
        }

    }
    NSString *queryString = [NSString stringWithFormat:@"WITH RECURSIVE dates(category, points,diagonisePoints, date) AS (VALUES('Healthy Weight', '0','0', (select DATE(selectedDate, '+1 day') as selectedDate from glanceTable ORDER BY ROWID DESC LIMIT 1)),('Healthy Diet', '0','0',(select DATE(selectedDate, '+1 day') as selectedDate from glanceTable ORDER BY ROWID DESC LIMIT 1)),('Exercise', '0','0',(select DATE(selectedDate, '+1 day') as selectedDate from glanceTable ORDER BY ROWID DESC LIMIT 1)),('Smoking Cessation', '0','0',(select DATE(selectedDate, '+1 day') as selectedDate from glanceTable ORDER BY ROWID DESC LIMIT 1)) UNION ALL SELECT category, points,diagonisePoints, date(date, '+1 day') FROM dates WHERE date < date('now')) insert into glanceTable SELECT category, points,diagonisePoints, date  as selectDate FROM dates  WHERE date <= date('now')"];

    const char *sql = [queryString cStringUsingEncoding:NSASCIIStringEncoding];
    sqlite3_stmt *selectstmt;
    if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {


        while(sqlite3_step(selectstmt) == SQLITE_ROW) {
            resultString = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)] ;
        }
    }
    return YES;
}

Your feedback is highly appreciated.

@Mr.Bista Error Message: "WITH" Syntax Error. – kiran Dec 05 '16 at 09:29 — kiran, Dec 05 '16 at 09:29

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

As far as I have looked around that RECURSIVE keyword was introduced in Sqlite 3.8.3 version. So, iOS 8.0 8.1 might be running with older sqlite versions, hence the error.

Related posts:

How to fix "near 'with': syntax error" in recursive CTE query (flask/sqlalchemy)

basic recursive query on sqlite3?

edited May 23 '17 at 11:53

Community

1
1

answered Dec 05 '16 at 09:36

Bista

7,869
3
27
55

I using sqlite 3.13.0 Version which will not work in < iOS 8.1? – kiran Dec 05 '16 at 09:47
I want to make work the above question in iOS 8.0 and 8.1 – kiran Dec 13 '16 at 08:55

score 1 · Answer 2 · answered Dec 05 '16 at 09:59

1

Recursive query supported from iOS 8.2 in older version it is not there. Its on your requirement if you want recursive than you have to set minimum os version as 8.2.

In Android also Recursive is not supporting older than Lollipop...

answered Dec 05 '16 at 09:59

Sagar Snehi

398
3
13

That means iOS < 8.2 didn't have internal support for the recursive query, independent of Sqlite version. – Bista Dec 05 '16 at 10:30

sqlite Query is not working in iOS 8.0 - 8.1 and working in IOS 8.2

2 Answers2

Linked