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 <[email protected]>
------------------------------------------------------------------------------
Comprehensive Server Monitoring with Site24x7.
Monitor 10 servers for $9/Month.
Get alerted through email, SMS, voice calls or mobile push notifications.
Take corrective actions from your mobile device.
http://pubads.g.doubleclick.net/gampad/clk?id=154624111&iu=/4140/ostg.clktrk
_______________________________________________
Gambas-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/gambas-user
