B4J Question SQLite Query Running Away in B4J

[markm]

I'm trying the following recursive query to get a list of all nested categories from a Categories table in my SQLite DB:

WITH RECURSIVE 
        CategoryStructured(id, name, parent_id, level) As (
        VALUES(2,'Baked Goods',2,0)
        UNION ALL
        SELECT Ingredient_Categories.id, Ingredient_Categories.name, Ingredient_Categories.parent_id, CategoryStructured.level+1
        FROM Ingredient_Categories JOIN CategoryStructured ON Ingredient_Categories.parent_id = CategoryStructured.id
        ORDER BY 2 DESC)
        SELECT substr('__________',1,level*2) || name from CategoryStructured;

If I do the query in the SQLite DB Browser, it functions properly, giving me the parent (in this case 'Baked Goods') then its children and grandchildren, properly nested:
Baked Goods
_Liquids
__Oils
___Olive Oils
_Dry Goods
__Grains


If I run the query in my B4J app with the following:

    Dim query As String = "  WITH RECURSIVE " & _
                            " CategoryStructured(id, name, parent_id, level) As (VALUES(2,'Baked Goods',2,0) UNION ALL " & _
                            " Select Ingredient_Categories.id, Ingredient_Categories.name, Ingredient_Categories.parent_id, CategoryStructured.level+1 " &  _
                            " FROM Ingredient_Categories JOIN CategoryStructured ON Ingredient_Categories.parent_id = CategoryStructured.id " & _
                            " ORDER BY 2 DESC) " & _
                            "  Select substr('..........',1,level*3) || name from CategoryStructured;"
                            
    Dim rs As ResultSet = Company.DB.ExecQuery(query)
    Do While rs.NextRow
        Log("Child: " & rs.GetString("substr('..........',1,level*3) || name"))
    Loop

It runs away repeating itself and I have to break out of the code:

Child: ..........Dry Goods
Child: ..........Grains
Child: ..........Baking Supplies
Child: ..........Liquids
Child: ..........Oils
Child: ..........Olive Oils
Child: ..........Dry Goods
Child: ..........Grains
Child: ..........Baking Supplies
Child: ..........Liquids
Child: ..........Oils
Child: ..........Olive Oils
Child: ..........Dry Goods
Child: ..........Grains
Child: ..........Baking Supplies
Child: ..........Liquids
Child: ..........Oils
Child: ..........Olive Oils
Child: ..........Dry Goods
Child: ..........Grains
.
.
.

I've tried doing it without the substring formatting in the SQLite query and I've tried by pulling all 4 columns, unformatted from CategoryStructured, but get the same results.

I'm using a recent JDBC:

#AdditionalJar: sqlite-jdbc-3.50.3.0

with SQLite version 3.46.1

What's happening to make the results run away like that?

 

[teddybear]

It works here, could you upload your sqlite db?

 

[markm]

Thanks for the response! It looks like it was an oddity with the DB itself. I decided to test it by adding a 4th level child in the mix to see if the second level had a problem with it and it worked perfectly at all levels. I then deleted the new category, tested again, and it worked perfectly, again at all levels. I didn't change a bit of code and simply committed an add and then a delete to the DB and that fixed it.