Recursive Tree in Base Sqlite3

Aware there are limitations to what Base can do I’m seeking general guidance on how to use Base to process data in sqlite tables as a tree. I’d prefer to leave as much as possible in sql and use python if a supporting language is required.

Here’s a nice example [updated to sqlite following comment below by @Wanderer] demonstrating a tree reproduced from the sqlite documentation, particularly The WITH Clause using the “DESC” modifier.


INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');

  under_alice(name,level) AS (
    SELECT, under_alice.level+1
      FROM org JOIN under_alice ON
SELECT substr('..........',1,level*3) || name FROM under_alice;



Your example was written for SQL-Server, so you have to check if your selected database (SQLITE) can handle this statement (as you have done now in your updated question).

This is no limitation of Base. You can always send direct SQL to the database and get the results. But databases are different, even if they “speak” SQL.

For the example there are 3 important changes in the code converted from Sql Server to SQLITE
Keyword RECURSIVE is necessary by SQL 1999, but not enforced by Sqlite.
UNION ALL instead of UNION
JOIN instead of INNER JOIN

And we have the hint in the docs for SQLITE to use DESC for ordering.

1 Like

The question has been updated to a sqlite3 example but it’s obviously more than just Tools, SQL, Enter command and select Show output, Execute.
The org table is created in the sqlite database but the output is not generated.

  1. Why doesn’t the org table appear in Base Tables after running the SQL? (It does if the Base is closed saving database and reopened.)
  2. Can the SQL query be saved and run in Base?

Create sqlite database because Base has no option to create sqlite database:
type nul > demo.db
"C:\Program Files\LibreOffice\program\odbcconfig.exe"
On User DNS tab: Add, Create New Data Source: SQLite3 ODBC Driver, Finish - Opens SQLite3 ODBC DSN Configuration
Database Name: browse to demo.db, Data source Name: demo, Select Foreign Keys, OK

Base, Connect to existing database: ODBC, Name of ODBC datasource: Browse to demo, No user authentication, Save and Proceed: select Create tables using the table wizard, Finish, Save As Demo.odb
Base opens with Table Wizard, Cancel

Use Create Query in SQL View

Paste code above into SQL query design window, File, Save - Syntax error in SQL statement

Same error just using the Create Table clause.

Table in sqlite database after Base tools sql execute:

E:\demo>sqlite3 demo.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> select * from org;

One hint: For SQLite you only need an empty file. So Base doesn’t need an option to create such a file.

type nul > demo.db wont work here. I created a simple text file (for “kate” - texeditor in KDE) and named it “demo.db”. This file must be registered in ODBC to connect through ODBC with Base. Other connections won’t work well …

It seems that’s exactly what it is. Base can only process a restricted sql set so it falls over, can’t save the sql, and needs a reload to bring the table in.
The query is only needed to support a report. I’d like to force the sql to be stored as a query too, even if it’s hidden.

Don’t see this when on a Ubuntu 20.x system. Not sure of your restricted sql set and actual meaning, but tables display without issue and queries save. Even current test adding record to table with form.

You have to do two things:

  1. You enter the query in SQL, not with the GUI
  2. Switch to SQL direct mode (button), which I use most times, as it also keeps my formatting.
    Then your query is given to the database, without interpretation by base. Base only accepts the resulting table.

(However: then you can’t use :variable in your query… )

1 Like

I’m understanding this query runs and displays output on linux systems. Can someone show an output screen?


The last one creates the sqlite table but the results of the query are not returned.

In the query Run SQL command directly is on:

Also included main screen where table shows (highlighter) along with my other tables. And if I close & re-open this Base file, the table is shown.

1 Like

I got that output but only once and can’t repeat it. The command is ignored with Run SQL command directly. It won’t even run select * from org; which runs with Run Query or externally in sqlite.

Recreate empty sqlite database: type nul > demo.db
Base, Open existing database: Demo, Finish
Queries, Create Query in SQL View
Edit, Run SQL command directly
Tried whole query then just create table.
Ref: LibreOffice Base Guide, Query enhancement using SQL Mode

Can run query as many times as wanted with same result. Closing Base & re-opening without issue. Table is created once in Tools->SQL and the rest stored as shown in the query section.

It’s possible the Win 10 version has some other bug. Have seen that before with something else but cannot say it is common.


Moved the files to Win 10, installed drivers for ODBC and reset the connection in Base. Result is the same:

Screenshot 2021-09-19 185000

No issues. Open, close re-run - OK.

1 Like

Let’s call it user error. The code runs in sqlite3 returning expected output. Some observations about interface intuitiveness:

  1. Run SQL code directly displays no output until it is run and query design View, Preview is selected. This is output not preview and the window can’t be displayed until after output is generated.
  2. Run SQL code directly returns ‘The data content could not be loaded.’. Regardless, the table is created.
  3. Base View, Refresh Tables is required to list table (shown here, same table list after closing window)
  4. Run SQL code directly using WITH RECURSIVE clause works but the output is not visible
  5. Query design View, Preview displays output

Why doesn’t Tools, SQL display the WITH RECURSIVE clause select statement output?

Don’t agree with most of this.

Not even sure what you mean in #1.

#2 run the Create table in Tools->SQL and not in Query.

#3 was not necessary for me.

#4 I agree here and should report as bug.

#5 should read Query->SQL with Run SQL command directly turned on.

This last item is very common in non-HSQLDB v1.8 databases as that is what it is verifying. Running the command directly bypasses much verification.

Fair enough, I’ll reframe it this way: The code runs through sqlite3 without issues and Base has “direct access to the database”. Where does a user paste the code to run it?
You’ve demonstrated the first part of the sql goes in Tools->SQL and the second part goes to Run SQL code directly^. How is this intuitive when it runs through sqlite3 in one piece?
Your point 5 - nice. How is Run Query forcing the preview setting on to display output (my point 1) intuitive?
I’m still right-clicking to paste but there’s no context-sensitive menu.
Being familiar with interface functionality is different to intuitiveness, suffice to say, intuitive is before familiarity. Suddenly a SQL button with a green tick changes to a blue tick but they are the same. There’s no win with terminology, different terms are used to describe the same functionality in menus, toolbars, task lists, windows, and the manual.

^ Alternatively, just paste all the code into Run SQL code directly, it fails after creating the table then run the second part again. Makes sense, not.

Was not realizing that this was about being intuitive. Base is not and neither are most other software items unless you have some similar background.

Of this office suite, Base is the least improved upon in many years past. The documentation is scattered and weak in many areas. Most of what I have gathered over the last years is through reading & trial and error.

Unfortunately terminology keeps many of your comments as unclear to me. Items such as preview setting elude me let alone forcing it. Not even certain what to associate this with.


Let me attempt to reply to some of the last comments.

The SQL noted is two items; the Creation of a table; a query. The table creation is run through Tools->SQL an the query you create a new query using Create Query in SQL View... and turning on switch for Run SQL command directly. Save giving it a name. Then you can run any time by double clicking on the query name you gave it. You can also use this query in a form.

As just noted, it is two pieces enter back-to-back. Through Tools->SQL the table is first created then the Query is run. Query output as previously noted is a bug.

Don’t know what this is as I stated:

with my point 1 above.

Another bug.

Don’t agree with this.

Already explained this - it is two items.

This is true throughout everyday life.

Here another hint: Tools → SQL doesn’t work the same way as direct SQL in a query. See Bug 143656, which shows a little bit of the difference. Could be the code will work also in Tools → SQL with LO 7.3 and the fix of bug 143656.

1 Like

Had tested with 7.3 before and did not work. Downloaded current and still no good.

Version: / LibreOffice Community
Build ID: e0c7d1e4cf7e6cf73211e69a91f458e6ffe303cb
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-09-21_10:25:01
Calc: threaded

1 Like

Bug 144631 - small UI improvement fixed

Bug 144674 - reported, confirmed, fixed.