[Gambas-user] Possible sqlite3 component error

[Benoît Minisini]

Le 13/11/2014 00:04, B Bruen a écrit :
> 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
>

This is fixed in revision #6737.

Regards,

-- 
Benoît Minisini