Documentation/HowTo/Base/Connect to SQLite

From The Document Foundation Wiki
Jump to navigation Jump to search

Connect Base to Sqlite

SQLite is a self-contained, serverless, zero-configuration, SQL database engine contained in a single file. It is arguably the most widely used database engine used today, and Base can connect to SQLite using ODBC.

External databases must be prepared before the Database Wizard can access them.

Preparing for the database wizard requires:

  1. A SQLite database
  2. Installed ODBC driver
  3. Configure database ODBC connection

SQLite database

Base can only connect to existing external databases. A sample SQLite database can be downloaded and extracted from: https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip. A SQLite database can be tested at https://inloop.github.io/sqlite-viewer/.

A new SQLite database can be created:

  • Try the simple way, just create an empty file with the database name, for example, in a shell for Windows type nul > test.db or linux > test.db
  • The proper way is install SQLite3 from https://sqlite.org/download.html and run SQLite3 test.db ".databases"

Install ODBC driver

Install ODBC driver from http://ch-werner.de/sqliteodbc/ (not for macOS)

For example in Windows, install http://ch-werner.de/sqliteodbc/sqliteodbc_w64.exe

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

Configure database ODBC connection

Follow the instructions for your operating system. For example, in Windows:

  1. Run (Windows key + R) C:\Program Files\LibreOffice\program\odbcconfig.exe to open the ODBC Data Source Administrator
  2. On the User DSN tab click the Add button to add a User Data Source Name
  3. Select the SQLite3 ODBC Driver (if it's not displayed it needs to be installed) and click the Finish button
  4. When the SQLite ODBC DSN Configuration window opens enter the Data Source Name: libretest-SQLite, Browse to the database test.db, and click the OK button. The ODBC DNS can be reconfigured later if required.

Database Wizard

Start Base and it will run through the Database Wizard:

  1. Select database - Connect to an existing database: ODBC
  2. Set up ODBC connection - Browse and Choose a data source eg chinook
  3. Set up user authentication - Set up the user authentication if any and Test connection
  4. Save and proceed to create database - Name Base database

Reference