No functions in Query builder

I have a sqlite database connected. I can see tables and views.

I would like to create queries for this database, but the functions list is empty.
Also SQL mode to build queries only seems to support the most basic statements.

Is this a limitation of the data source?

Have connected to a SQLITE-database file by JDBC. Tried to edit a query an clicked on “Functions” and it seems to be the same list of functions like in internal HSQLDB is available.

Which connection to the SQLITE database do you use?

I use Ubuntu and I installed the ODBC drivers using

$ sudo apt-get install libsqliteodbc unixodbc

Then: Try the JDBC-connection.
If installed an connected there may appear an error: SQLite only supports TYPE_FORWARD_ONLY cursors. Then go to Edit → Database → Advanced Settings → Special Settings and chose “Respect the result set type of the database driver”.

I just had a go at this.

I’m not sure I need to install any drivers specifically for sqlite? I found this GitHub - xerial/sqlite-jdbc: SQLite JDBC Driver but I’m not sure if it’s necessary or how to integrate it with LibreOffice…

Then in the connection window I fill out the connection string with sqlite:/home/user/path/to/database/mydatabase.sqlite, but I don’t know what to add in username (there is none) and in JDBC driver class…

This documentation page Chapter 2 Creating a Database only offers instructions to connect via ODBC…

You only need GitHub - xerial/sqlite-jdbc: SQLite JDBC Driver
Then open LO → Tools → Options → LibreOffice → Advanced → Java Options → Class Path → Add Archive
Look for the *.jar-file you have saved.

When creating the query there will be
JDBC URL: jdbc:sqlite:/home/…/mysqlite.db
or in Windows something like: jdbc:sqlite:/c:/…/mysqlite.db
Driver: org.sqlite.JDBC

When opening the database you will get an error
SQLite only supports TYPE_FORWARD_ONLY cursors.

Go to Edit → Database → Advanced Settings → Special Settings and set Respect the result set type form the database driver.

Now you could connect to the database and create queries with functions.

1 Like

It’s working!

I had to add both files indicated in the Gihub page though.

Thank you very much. I wish this would be in a guide somewhere Google would be able to find.

Navigating these menus doesn’t show in any search result hits and the driver I did find in one SO question, but yout reply is more complete than anything else I was able to find.

I have published this in the German “Base Handbuch”. It’s part of this chapter: Chapter 2 - Create database. Hope it will be content of English Base Guide in the future.