How to Implement Recursive Function of sqlite3 on Android Devices

Question

I'm trying to implement recursive function within an android app;

First, i try it (recursive function) from 'cmd' windows:

I create a table :

 CREATE TABLE tree(
 id_tree integer PRIMARY KEY AUTOINCREMENT,
 id_boss TEXT,
 id_child TEXT,
 answ TEXT);

Then insert some values:

 INSERT INTO tree(id_boss,id_child,answ) VALUES('1','2','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('1','3','F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','P1','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','4','F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('3','P2','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('3','8','F');

Then I run a recursive query that is now availabe for sqlite 3.8 :

 WITH RECURSIVE
 under_alice(name,level) AS (
   VALUES('1','0')
   UNION ALL
   SELECT tree.id_child, under_alice.level+1
     FROM tree, under_alice
    WHERE tree.id_boss=under_alice.name 
    ORDER BY 2 DESC
    )
   SELECT  substr('..........',1,level*3) || name FROM under_alice;

It's running fine with result:

  1
  ...2
  ......4
  ......P1
  ...3
  ......8
  ......P2

Then I select id_child where id_boss='1' and answ='T' with this query;

  WITH RECURSIVE 
  under_alice(name,level) AS ( 
  VALUES('1','0') 
  UNION ALL 
  SELECT tree.id_child, under_alice.level+1 FROM tree, under_alice WHERE 
  tree.id_boss=under_alice.name and answ = 'T' and tree.id_boss='1' 
  ORDER BY 2 DESC
  )
  SELECT  substr('..........',1,level*3) || name FROM under_alice;

It's running fine with this result:

 1
 ...2

It all can be run within a cmd prompt, but when I'm try it to implement it in my android app, I get some errors (ex. Cannot Be Executed):

Here's my code to execute the query from my android app:

 try {
 SQLiteDatabase db = dbHandler.getWritableDatabase();
      String q = "WITH RECURSIVE 
      under_alice(name,level) 
      AS ( VALUES('1','0') 
      UNION ALL 
      SELECT tree.id_child, under_alice.level+1 FROM tree, under_alice 
      WHERE tree.id_boss=under_alice.name and answ = 'T' and tree.id_boss='1'         ORDER BY 2 DESC)
      SELECT  max(name) as au FROM under_alice;";     
      Cursor eq = db.rawQuery(q, null);
      eq.moveToFirst();
      int au = eq.getColumnIndex("au"); 
      String sau = eq.getString(au);
      makeToast(""+sau); // if execute succes, create a toast message

        } catch (Exception e) {
    // TODO: handle exception
    makeToast("failed query"); // if query filed
    }
    }

How do I make this work? Is there anything wrong with my query?

You might be interested in [this information about Android SQLite version (not so updated)](http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android) — Andrew T., May 05 '14 at 05:26
i tried a method from that link to check my android devices sqlite version, and yes, my android 4.2.2 devices using android.sqlitedatabase version 3.7.11 , is this posible to upgrade it ? some external libraries maybe ? — user3599629, May 05 '14 at 05:44
For this case, I honestly don't know if it's possible to upgrade SQLite manually. Sorry. — Andrew T., May 05 '14 at 05:47
thank's , for answer it, i know now why my query didn't work — user3599629, May 05 '14 at 05:54

How to Implement Recursive Function of sqlite3 on Android Devices

0 Answers0