Use SQLite3 with LibreOffice Base in Fedora 38

LibreOffice Base requires the ODBC SQLite3 driver to be installed, but luckily it is in the repositories on Fedora.

This post is so far only helpful in some of the setup for using Base with SQLite. None of the usual options for creating primary keys are showing for the SQLite databases. Yet tables can be saved to the databases. Not sure if this is a problem with the specific driver, or a user malfunction, etc.

Why ODBC?

(Open Data-b-ase Connectivity)

SQLite3 developers might have programmed compliance specifically for the aging MS Access and/or LibreOffice Base applications. However, they chose general compliance with ODBC.

ODBC is a recognized, industry standard catch-all for compatibility between many relational databases. There is no such thing as a native ODBC relational database, as it is a connectivity API.

LibreOffice Base has compatibility with ODBC, as long as the system has the right ODBC drivers.

Install the packages

 dnf install sqliteodbc 

 dnf install sqlite-devel 

 dnf install unixODBC unixODBC-devel

Note: This will facilitate SQLite3 capabilities in the Base application.

Confirm the system ini file

Note: In Fedora 38, the ODBC driver configuration file should be ready to go after installing the above packages.

In a terminal, you can type:

 cat /etc/odbcinst.ini 

You should get the following:

-------------------------------------------------------

[MySQL]
Description=ODBC for MySQL 8
Driver=/usr/lib/unixODBC/libmyodbc8.so
Driver64=/usr/lib64/unixODBC/libmyodbc8.so
FileUsage=1

[MySQL-5]
Description=ODBC for MySQL 5
Driver=/usr/lib/libmyodbc5.so
Driver64=/usr/lib64/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[FreeTDS]
Description=Free Sybase & MS SQL Driver
Driver=/usr/lib/libtdsodbc.so
Setup=/usr/lib/libtdsS.so
Driver64=/usr/lib64/libtdsodbc.so
Setup64=/usr/lib64/libtdsS.so
Port=1433

[MariaDB]
Description=ODBC for MariaDB
Driver=/usr/lib/libmaodbc.so
Driver64=/usr/lib64/libmaodbc.so
FileUsage=1

[SQLITE]
Description=SQLite ODBC 2.X
Driver=/usr/lib64/libsqliteodbc.so
Setup=/usr/lib64/libsqliteodbc.so
Threading=2
FileUsage=1
UsageCount=1

[SQLITE3]
Description=SQLite ODBC 3.X
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2
FileUsage=1

UsageCount=1

-------------------------------------------------------

Note: The spelling of the SQLite3 entry: "SQLITE3". This will be needed in the next steps.

Database Preparation

Unfortunately, it is required to enter every SQLite database file's name and location in a special .ini file in the home directory before Base will be able to acknowledge those files' existence.

Filename: /home/<username>/.odbc.ini

Note: The file is probably empty. 

Note: Like the other .ini file, Fedora 38 should have also created this .ini file, because you already installed the ODBC packages from the repos.

For a new database:

You have to:

1. Create a blank database file first
2. Add a section to the .odbc.ini file containing:
1. A custom tag/title in brackets
2. The SQLITE3 driver designation from the /etc/odbcinst.ini file
3. The sqlite3 file's name and location (as per the example below)

Example entry for the file: /home/<username>/.odbc.ini

-------------------------------------------------------

[customtitle]

Description=Something descriptive about the database
Driver=SQLITE3
Database=/home/<username>/Downloads/exampledb.sqlite

[customtitle2]
Description=Something descriptive about the database
Driver=SQLITE3

Database=/home/<username>/Documents/exampledb2.sqlite 

-------------------------------------------------------

 

For a pre-existing SQLite database file:

You repeat step 2 as above, adding an entry in the .odbc.ini file.

Steps in Base:

1. Open Base after performing the above steps

2. Select the radial button: "Connect to an existing database"

3. Select the option: "ODBC"

4. Click the button: "Next"

5. Click the button: "Browse"

6. Select the option for your custom database entry, and then click the button: "OK"

7. Click the button: "Next"

8. Base will prompt you to name and save yet another file. This file contains any of the LibreOffice Base forms and views, etc. that correspond to the database. However this file does not contain the actual database itself.

Still Doesn't Work !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

• Does not offer options to create primary keys, like in native Base db format.
• This is probably some kind of user malfunction, or LibreOffice does not like SQLite
• Either way, I have not been able to get the right-click menu on a table's new fields in order to get to create a primary key (or anything else in the menu). And thus I have not been able to fill any new tables with data either. (Just a mild hiccup for using a database.)
• However, the tables themselves do save into the database when created in Base. And they show up in the sqlite3 cli program afterwards.