Configure ODBC and JDBC in LibreOffice Base

LibreOffice Base allows users to connect to external relational database management systems (RDBMS) using Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC). This guide details the essential driver requirements, system prerequisites, and step-by-step configuration procedures needed to establish stable ODBC and JDBC connections within LibreOffice Base.

System Prerequisites

Before configuring either driver, ensure the following conditions are met:

• Architecture Match: The bit architecture of LibreOffice, the Java Runtime Environment (JRE), and the external database drivers must match. If you are using 64-bit LibreOffice, you must install 64-bit ODBC/JDBC drivers and a 64-bit JRE.
• Java Runtime Environment (for JDBC): Open LibreOffice, navigate to Tools > Options > LibreOffice > Advanced (or LibreOffice > Preferences on macOS), ensure Use a Java runtime environment is checked, and select an installed JRE.

JDBC Driver Configuration

JDBC connects to databases via Java archives (.jar files) provided by the database vendor (e.g., MySQL Connector/J, PostgreSQL JDBC Driver).

Step 1: Add the JDBC Driver to LibreOffice ClassPath

1. Download the appropriate .jar driver file from your database provider.
2. In LibreOffice, go to Tools > Options > LibreOffice > Advanced.
3. Click the Class Path… button on the right.
4. Click Add Archive…, browse to the location of your downloaded .jar file, select it, and click Open.
5. Click OK, then click OK again. Restart LibreOffice to apply changes.

Step 2: Connect LibreOffice Base via JDBC

1. Open LibreOffice Base to launch the Database Wizard.
2. Select Connect to an existing database, choose JDBC from the dropdown menu, and click Next.
3. Enter the Datasource URL and the JDBC driver class:
   • MySQL / MariaDB:
     • URL: mysql://<hostname>:<port>/<database_name>
     • Driver Class: com.mysql.cj.jdbc.Driver
   • PostgreSQL:
     • URL: postgresql://<hostname>:<port>/<database_name>
     • Driver Class: org.postgresql.Driver
4. Click Test class to verify that the driver class loads successfully.
5. Click Next, enter your database username, check Password required if applicable, and click Test Connection to confirm connectivity.
6. Click Finish and save the .odb file.

ODBC Driver Configuration

ODBC connects via drivers installed at the operating system level, identified by a Data Source Name (DSN).

Step 1: Install and Configure the ODBC Driver on the OS

1. Install the appropriate ODBC driver package for your database (e.g., MySQL ODBC Connector, PostgreSQL psqlODBC).
2. Configure a Data Source Name (DSN):
   • Windows: Open ODBC Data Source Administrator (64-bit), go to the System DSN or User DSN tab, click Add, select your driver, configure host/database/credentials, and name the DSN.
   • Linux / macOS: Define the DSN in /etc/odbc.ini or ~/.odbc.ini using the unixODBC or iODBC driver manager.

Step 2: Connect LibreOffice Base via ODBC

1. Open LibreOffice Base to launch the Database Wizard.
2. Select Connect to an existing database, choose ODBC from the dropdown menu, and click Next.
3. Click Browse… to view the list of configured ODBC data sources and select your DSN.
4. Click Next, enter the required username, and click Test Connection to verify the link.
5. Click Finish and save the .odb file to complete the setup.