Android Question Query error - SQLite Version?

[adriano.freitas]

Helo! I'm developing a project that uses a recursive query. It happens that when testing the SQL sentence in several tools, it works ok, however, when inserting in my application made in B4A the application closes. I was able to identify that the problem occurs in the execution of the query, which leads me to believe that it is a question of the SQLite version that might not support recursion as I did. Could someone help me see in the query what could be wrong and, if applicable, help change it so that it works? Thank you all.

with recursive itens (nivel, codigo, pai   , estrutura,
                      ordem, titulo, status, tipo     , temfilhos) as (
     select 0  as nivel, codigo,   pai, estrutura,
                  ordem, titulo, status, substr(conteudo || ' ',1,1) tipo,
                  (CASE WHEN ((SELECT Count(*)  FROM arvore arvore2
                                               WHERE pai=arvore.codigo)=0)
                                                THEN 'N'
                                                ELSE 'S'
                                                 END) as temfilhos
    from arvore
    where pai = 0
union all
    select    itens.nivel + 1, arvore.codigo, arvore.pai   ,
            arvore.estrutura, arvore.ordem, arvore.titulo,
            arvore.status,   substr(arvore.conteudo || ' ',1,1) tipo,
            (CASE WHEN ((SELECT Count(*)  FROM arvore arvore2
                                         WHERE pai=arvore.codigo)=0)
                                          THEN 'N'
                                          ELSE 'S'
                                           END)  as  temfilhos
    from itens   
  join arvore  on itens.codigo = Arvore.pai
    order by itens.nivel+1 desc, arvore.ordem )
select    nivel, codigo, pai   , estrutura,
        ordem, titulo, status, tipo    , temfilhos
from itens;

 

[drgottjr]

requires a higher version of sqlite than used by android

 

[adriano.freitas]

Oops! How boring! That would make all the difference in the application's performance... Do you know which version it works from?

 

[Mahares]

Do you know which version it works from?

I just ran WITH RECURSIVE query on my device with SQLite version 3.19.4 and it worked.
I also ran it on a much older device with SQLite version: 3.8.6.1 and it worked. So, this is telling me the WITH RECURSIVE clause has been around for many years.
Do you know that you can check the version on your device using this syntax:

Dim sqliteversion As String =SQL1.ExecQuerysingleresult( "SELECT SQLite_version()")
 Log(sqliteversion)

 

[adriano.freitas]

Hi! Great tip for SQLite version verification! It will be very useful. I notice that some devices here in my country curiously keep an older version of SQLite, which prevents the use of recursion. From what I was able to search on the internet, the query structure I'm trying to use was implemented in version 3.8.3 of SQLite, which is not present, as I said, on ALL devices (which for me is quite curious), so, how the difference in application performance is very different, with your help, I decided to add a conditional in the application that returns data directly from the query when the version is adequate or uses a simpler query with loops in the application when the version does not support it, generating bigger slow, but working... That's what I can think of at the moment.