[Gambas-user] Possible sqlite3 component error

[B Bruen]

The following query is a "standard" CTE based query to return the contents of a hierarchical table (i.e. columns are id, parentid, data):
WITH RECURSIVE tmp AS ( 
  SELECT *, 0 AS depth 
  FROM pages p 
  WHERE p.parent = 0 
 UNION ALL 
  SELECT p.*, tmp.depth + 1 
  FROM pages p 
    JOIN tmp ON (p.parent=tmp.id)
) 
SELECT id, title, parent, category, indexed, htmlsrc, depth 
FROM tmp 
ORDER BY depth;

I use the following method in Gambas:
Private Sub LoadTOC()                                             '' Build the index tree

  Dim rslt As Result
  Dim pnode As String
  Dim sQry As String = "WITH RECURSIVE tmp AS ( SELECT *, 0 AS depth FROM pages p WHERE p.parent = 0 UNION ALL SELECT p.*, tmp.depth + 1 FROM pages p JOIN tmp ON (p.parent=tmp.id)) SELECT id, title, parent, category, indexed, htmlsrc, depth FROM tmp ORDER BY depth"
    
  tvwIndex.Clear

db.Debug = True
  rslt = $conn.Exec(sQry) 'Find("pages")
db.Debug = False

  For Each rslt
    pnode = rslt!parent
    If Not pnode Then Continue
    If Not rslt!indexed Then Continue
    tvwIndex.Add(rslt!id, rslt!title,, IIf(pnode = 0, "", pnode))
  Next
  
  tvwIndex["1"].Expanded = True
  tvwIndex["1"].Text = "Index"
  tvwIndex["1"].Picture = Picture["icon:/32/book"]

Catch
    Error Subst("&1\nERR: &2 (&3)\n&4\n&1\n", String$(40, "-"), Error.Text, Error.Code, Error.Backtrace.Join("\n"))
    Error db.Error
    Stop

End

When this is executed it fails at the rslt=$conn.Exec line with 
ERR: Query failed: SQL error or missing database (-1)
and db.Error is "1"
the $conn connection is valid and is open.

Similarly when I try to execute that query in the connection browser in the IDE I get the same error.

However, when I run the same query in the sqlite3 cli tool it works faultlessly.

The output produced by the db.debug is:
sqlite3: 0x8551560: WITH RECURSIVE tmp AS ( SELECT *, 0 AS depth FROM pages p WHERE p.parent = 0 UNION ALL SELECT p.*, tmp.depth + 1 FROM pages p JOIN tmp ON (p.parent=tmp.id)) SELECT id, title, parent, category, indexed, htmlsrc, depth FROM tmp ORDER BY d
epth

apart from the line break in the ORDER BY clause (which I dont think is the problem) I cannot see any corruption of the query string.

I am not sure but have a suspicion that this has arisen after the recent changes for db collations etc.

regards
Bruce
-- 
B Bruen <bbruen at ...2308...>