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.
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.