title: How to Implement Recursive Function of sqlite3 on Android Devices
body: <p>I'm trying to implement recursive function within an android app;</p>
  
  <p>First, i try it (recursive function) from 'cmd' windows:</p>
  
  <p>I create a table :     </p>
  
  <pre><code> CREATE TABLE tree(
   id_tree integer PRIMARY KEY AUTOINCREMENT,
   id_boss TEXT,
   id_child TEXT,
   answ TEXT);
  </code></pre>
  
  <p>Then insert some values:</p>
  
  <pre><code> 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');
  </code></pre>
  
  <p>Then I run a recursive query that is now availabe for sqlite 3.8 :</p>
  
  <pre><code> 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;
  </code></pre>
  
  <p>It's running fine with result:</p>
  
  <pre><code>  1
    ...2
    ......4
    ......P1
    ...3
    ......8
    ......P2
  </code></pre>
  
  <p>Then I select id_child where id_boss='1' and answ='T' with this query;</p>
  
  <pre><code>  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;
  </code></pre>
  
  <p>It's running fine with this result:</p>
  
  <pre><code> 1
   ...2
  </code></pre>
  
  <p>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):</p>
  
  <p>Here's my code to execute the query from my android app:</p>
  
  <pre><code> 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
      }
      }
  </code></pre>
  
  <p>How do I make this work?
  Is there anything wrong with my query?</p>