ios - sqlite 查询在 iOS 8.0 - 8.1 中不工作，在 IOS 8.2 中工作

Question

Sql 查询在 IOS 8.0 8.1 中不工作，在 IOS 8.2 中工作请找到下面的 sqlite 查询。

-(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;
}

非常感谢您的反馈。

Answer 1

据我所知，RECURSIVE 关键字是在 Sqlite 3.8.3 版本中引入的。因此，iOS 8.0 8.1 可能正在运行较旧的 sqlite 版本，因此会出现错误。

相关帖子:

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

basic recursive query on sqlite3?