Connecting Base to Sqlite in Windows

flywire · September 16, 2021, 7:01am

LibreOffice 6.1: Release Notes - The Document Foundation Wiki described changes to the database drivers (finalised in V6.2). There’s a bit of background in the developer’s blog.

The LibreOffice Base Guide 6.2 Ch2 has a brief discussion of Accessing external databases.

What is the preferred method or considerations for connecting Base to Sqlite in Win10 since this release? Is there a simple demonstration file, or a case of using Chinook or Northwind database.

btw, Why does Documentation/Third Party Resources - The Document Foundation Wiki link advertise the Devat ODBC Driver for SQLite?

flywire · September 16, 2021, 7:15am

I don’ know how current Documentation/How Tos/Using SQLite With OpenOffice.org - Apache OpenOffice Wiki is.

I’ve extracted https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip (sqlite3) which tests ok with SQLite Viewer

After installing http://ch-werner.de/sqliteodbc/sqliteodbc_w64.exe, Base, Database Wizard Connect to existing database ODBC, ODBC data source SQLite3 Datasource, Test Connection OK, Save and proceed Yes register the database Open database for editing Finish, Save as New Database.odb, Opens empty database.

Should Connect to existing database - chinook.db

RobertG · September 16, 2021, 2:11pm

Release notes LO 6.1: There was planned to remove HSQLDB and switch to Firebird. Since LO 6.4.3 Firebird has been set back to experimental mode, because there are bugs together with the GUI. HSQLDB is still the default internal database and you have to set experimental mode “on” to get a new Firebird database. I don’t see anything about SQlite in release notes LO 6.1.

For connecting to SQlite I could only write, what I have written in Base Guide (“Base Handbuch” in Germany). ODBC is the only possibility to connect at this moment with Linux. If there is anybody, who could test something with Windows and SQlite (and all this special ODBC-drivers): Write it down here and I will take it for the next “Base Handbuch”. The “Base Guide” must be updated by someone else.

flywire · September 16, 2021, 2:15pm

Yes, I agree.

Villeroy · September 16, 2021, 2:18pm

Under Linux, I installed unixodbc and libsqliteodbc together with unixodbc-bin (a GUI), added the driver to /etc/odbcinst.ini

[SQLite3]
Description=SQLite3 ODBC Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so
Setup=libsqlite3odbc.so
UsageCount=1

and the database to my personal ~/.odbc.ini with the database

[chinook]
Description=SQLite3
Driver=SQLite3
Database=/home/villeroy/Documents/LibreOffice/sqlite/chinook.db
Timeout=100000
StepAPI=No
ShortNames=No
FKSupport=No
SyncPragma=NORMAL
JournalMode=DELETE
BigInt=No

connected a Base document with connection type “ODBC” to “chinook” and got this:

Villeroy · September 16, 2021, 7:44pm

I could do the same trick on Windows XP or earlier. Just tried with Windows 10 but they removed the ODBC manager from the system panel and I have no clue how to do anything on that system without the help of 3rd party tools.
This looks promising: How to Install Sqlite3 on Windows 10 but lacks the ODBC part

Ratslinger · September 16, 2021, 8:58pm

From Run (Windows key + R), enter odbcad32. That will bring up the ODBC Data Source Administrator.

Villeroy · September 16, 2021, 9:41pm

So they have hidden that feature from the “ordinary dull user”. If you installed 64-bit drivers … no, it’s not “odbcad64”.nor “odbcad” Hit the start button and type “odbc”. This way you can choose between 32-bit ODBC manager and 64-bit ODBC manager. First you have to install some ODBC driver for SQLite on Windows.
I keep my hands off that rotten system for now.

Wanderer · September 16, 2021, 10:29pm

To my knowledge it is MacOS where they removed the odbc-manager, so you may need 3rd party tools now. Checked this 2 or 3 years ago, but didn’t test…

Villeroy · September 17, 2021, 9:51am

One more note on the line FKSupport=No in the above mentioned configuration file ~/.odbc.ini. SQLite supports foreign keys and they are implemented in the “chinook” example database as documented in https://www.sqlitetutorial.net/wp-content/uploads/2018/03/sqlite-sample-database-diagram.pdf
If you set FKSupport=Yes, referencial integrity is enforced although you can see any relations diagram in Base’s relation manager.

Wanderer · September 16, 2021, 10:19pm

My method is using the following site to download the odbc-driver: SQLite ODBC Driver

After installing the driver I type ODBC in the search box of Win10-Menu and wait what is coming up. In the odbc-connection manager I can define named conections to the sqlite-databases. Just browse for the file and adapt the name afterwards.

One hint: If you are in an account without admin-privileges you can only create local connection/for the current user only.

Now you open LibreOffice, and select New → Database and then connect to an existing database. From the options scroll down to sqlite3 and select this type. In the next window you select the odbc-connection you defined earlier (or can create further connections).

Test the conection (button).
Sometimes you don’t see the available tables directly. Click on forms / report, then back to tables an the tables should be there (obviously only if you connected to a populated table).

I usually created my sqlite-databases by script, on the command-line, so i don’t experienced any obstacles on table creation in LibreOffice.

(Just written from memory, and as a I use OpenShell my Win10 is Visualy altered…
Tested with LO 6.4 and also 7.1. 32 bit, so also 32bit driver needed, Win 10 20H2 and Win7)

flywire · September 17, 2021, 5:25am

YMMV

Installed http://ch-werner.de/sqliteodbc/sqliteodbc_w64.exe
Extracted https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip (sqlite3)
Tests ok with SQLite Viewer
I installed a few probably irrelevant things (including How To Download & Install SQLite Tools) and ended up with: C:\Program Files\LibreOffice\program\odbcconfig.exe

Note: http://ch-werner.de/sqliteodbc/

If you’re using 32 bit software on Win64 you should install the Win32 driver, too.

Run: C:\Program Files\LibreOffice\program\odbcconfig.exe

ODBC Data Source Administrator (64-bit)
Create New Data Source
SQLite3 ODBC DSN Configuration

LibreOffice Base, File, New - starts Database Wizard

Note the last data source:

Example of wrong outcome - no odbc datasource:

flywire · September 17, 2021, 7:17am

It would be good if someone could test this in Win10 and confirm or otherwise, particularly Point 4.
.
Be warned, shortcut labels are used to fix filenames that aren’t what they appear (maybe the shortcuts were named the wrong way around but what about the 64 bit driver):
.