1

Below query is not working in ios 8.1 below version. Its give syntax error "With"

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')

Same query is working iOS 8.2 above.

Since there is no recursive method in iOS 8.1 supported, sqlite will be at version 3.7.13.

Sqlite3 version are automatically changed based on iOS version changes

So is there any alternative way i need to get my query work in below version?

Your feedback is highly appreicated.

kiran
  • 4,285
  • 7
  • 53
  • 98
  • You have to implement the recursion manually. – CL. Dec 21 '16 at 22:41
  • Previously asked here: https://stackoverflow.com/questions/40970282/sqlite-query-is-not-working-in-ios-8-0-8-1-and-working-in-ios-8-2 – Steven Fisher Dec 22 '16 at 18:15
  • Also, I doubt at this point that there are many people using iOS 8.0 or 8.1. You'd probably be better off raising your minimum to 8.2. – Steven Fisher Dec 22 '16 at 18:18
  • @StevenFisher You are correct 100%. some thing which i got to failed to explain the statistics users for my application user – kiran Dec 23 '16 at 06:58

1 Answers1

1

You can include sqlite3 in your source instead of dynamically linking it with whatever version is provided by iOS. SQLite provides an amalgamation version of their source code that combines all source files into a single sqlite3.c file making it easier to use.

You can download the amalgamation source from https://sqlite.org/download.html. Grab the files sqlite3.c and sqlite3.h and add it to your iOS project. Import the SQLite header #import "sqlite3.h" and continue using the recursive query.

According to the changelog, common table expressions were added in 3.8.3, so grab that or something newer.

Also, remove libsqlite3 from Build Phases > Link Binary With Libraries.

Anurag
  • 140,337
  • 36
  • 221
  • 257
  • Its working perfect good. Is there any constrain in future after adding those classes or any updated comes in future? – kiran Dec 23 '16 at 06:32
  • 1
    You can ship a newer version of SQLite with the future releases of your app. Older versions of the app will remain at whatever you originally shipped with, but that's no different than with iOS and the problem you ran into with versions prior to 3.8.3. – Anurag Dec 23 '16 at 19:07
  • you feedback is appreciated its also useful in the computing – kiran Dec 26 '16 at 07:26