Connect libreoffice to mariadb

Welcome to Simone Giustetti's wiki pages.

Languages: English - Italiano

CONNECT LIBREOFFICE BASE TO A MARIADB / MYSQL RDBMS

LibreOffice is an office suite of programs including a word processor (Write), a spreadsheet (Calc), a presentation program (Impress) and more. Base, one of the included applications, can store user data inside a relational database along with saved queries, forms for data management and reports. All that is required to build a data aware application. Moreover you can connect it to a server to query data shared through a computer network. In the following article I'll discuss how to connect Base to a Database Server using JDBC drivers.

Introduction

Since version 7, LibreOffice Base includes a proper Relational Database Management System based on FireBird SQL: a lightweight and feature rich relational database engine with a decades long development history. Previous versions, 6 for example, relied on Hyper SQL and, even earlier, Adabas D. The software has been including since its beginnings the necessary means to connect to other RDBMSes, both natively or recurring to integration with the Java language and its libraries. An external database server proves to be very useful when many users or programs are required to query data concurrently. When a single user application is not enough, a shared access database server demonstrates all of its potential and versatility.

Connecting LibreOffice to a Relational DataBase Management System is useful to:

Develop applications for a small organization.

LibreOffice allows the generation of data aware forms useful to consult saved data, to insert new rows or to update and delete existing ones. Moreover, thanks to the use of high level programming languages, it allows developing automated procedures, checks and whatever else business software requires. The resulting product will probably never be on par with a professional one, but could result in a good prototype or a software to make up the shortcomings of the institutional one for a brief period.

Manage a remote database.

Every professional RDBMS offers a selection of administration tools that can be installed locally or accessed via web. LibreOffice is not meant to replace such tools, but rather to integrate them by providing a simple interface to perform common operations as: create or modify tables, import data from tabular formatted files such as Calc ods or csv, correct small errors and other simple management tasks.

Tie data to a Write or Calc document.

It is often useful produce serial documents based on never changing templates, filling in as little specific information as needed. When this need comes up, you can use the "Serial Document (Mail Merge)" LibreOffice functionality to link documents to a data source originating from a spreadsheet or other sources, such as a database. You can use the same functionality to make e-mail distribution lists too.

Prerequisites

There are two ways to connect LibreOffice to a database server:

Using specific drivers built and distributed in software libraries and installed on the very same machine hosting LibreOffice.
Using the JDBC extensions of the Java programming language: a common interface designed to access data stored in relational databases.

The latter is advisable for some good reasons:

First of all the ease of use: no administrator task nor high privileges nor operating system knowledge is required. You only need to download and save one or more files on a local file system then inform LibreOffice of their location. Everyone can do it.
It is general. Every platform or operating system having an available Java Virtual Machine can use JDBC. Given the widespread diffusion of the Java language and the incredible number of compatible devices, this is probably the most universal solution available in IT.
It provides an excellent abstraction layer thus permitting, supposing a future swap of the underlying data storing RDBMS technology, to maintain the same level of functionality by getting a new driver, migrating data and configuring a connection to the new database.

Suppose you want to connect to a MariaDB or MySQL RDBMS, please check for a recent Java VM and LibreOffice release to be installed before you go on. Moreover you'll need some credentials to authenticate to the database you'll query. Please keep in mind that Base cannot create new databases, but only connect to an existing one. The target database can be empty, but it has to exist and so does an user granted access to it. Once you have all of the above, you'll have to search for the proper JDBC driver for the RDBMS of choice.

Download and Install JDBC Drivers

JDBC drivers are usually released inside archive files with the jar extension, short for Java ARchive, a distribution format often used for classes and programs written in Java. Unlike other formats, Java archives do not require "installing", but simply to be copied to a directory readable by the program that will use them.

Drivers for MariaDB and MySQL can be downloaded freely from the respective web pages. Given their common origin, in the past drivers for a database could be used to connect to the other one and vice versa. Nowadays, after MySQL 8 and MariaDB 10.5 took diverging paths, the previous statement could not prove true anymore. Drivers for one software should work for the other one, but there is no guarantee that all functionality will be fully supported.

MariaDB JDBC Drivers

The MariaDB JDBC drivers are in constant development and new releases come out with a frequency of one every couple of months. A full list of current and past releases is available in the following page: Mariadb Connector J Releases. Installation packages can be downloaded clicking on a link of the download page. You need a version compatible with the installed Java VM. Release 7 of the JVM and earlier ones require a driver from the 1.x development branch, while release 8 and later work well with the 2.x branch. You are strongly suggested to always choose the most recent release as it includes all of the latest addictions and bug fixes. The last available release should be able to support any RDBMS release. If this is not the case and you suffer from strange issues, unwanted behavior or unexpected slow downs, you should probably try a previous driver release. More versions of one driver can coexist on the very same machine. No installation is required, more than one archive can be saved in the same directory or spread across different ones. The user or the system administrator should check for LibreOffice to select the preferred driver when configuring a connection to a database.

At the time of writing, the last available release is 2.7.2 and the related file is mariadb-java-client-2.7.2.jar. Download the driver and save the file in a local directory then grant read access to the users who will need it. When the personal computer is used by a single person, you should create a jdbc named folder in her/his home one and move the mariadb-java-client-2.7.2.jar archive there. When a computer is shared between many users and more than one require connecting to the database, you are suggested to create the jdbc folder inside a system directory. Obviously, when dealing with the latter scenario, the task requires a system administrator who should create the directory and assign the correct permissions to it and to the archive.

Let's suppose to use a system directory and an operating system of the UNIX family like Linux or MacOsX, the /opt directory, meant for optional software and tools, seems the natural candidate for the drivers. Please do not use /tmp which is meant for temporary data and is kept in RAM memory only and never written to disk by some operating systems. The commands required are:

  mkdir /opt/jdbc
  mv mariadb-java-client-2.7.2.jar /opt/jdbc/
  chmod 0755 /opt/jdbc/
  chmod 0644 /opt/jdbc/mariadb-java-client-2.7.2.jar

which prove to be enough to make drivers available.

MySQL JDBC Drivers

The same statements made for MariaDB JDBC drivers in the previous paragraph apply here. The MySQL drivers can be downloaded clicking on any link in the download page. At the time of writing the last available release is 8.0.23. The jar archive is distributed in many file formats to facilitate users preferring to recur to the operating system installation program: packages for many Linux distros, a program for the Windows family of operating systems and some compressed multi-platform archives. Suppose you go for the latter, you'll have to choose between a zip archive, common for the Windows environment, or a tar.gz one, widespread in UNIX.

Let's pretend to work in a UNIX environment and to choose the /opt/jdbc directory as the location for the drivers, the resulting "installation" commands are:

  mkdir /opt/jdbc
  tar -zxf mysql-connector-java-8.0.23.tar.gz
  mv ./mysql-connector-java-8.0.23/mysql-connector-java-8.0.23.jar /opt/jdbc/
  chmod 0755 /opt/jdbc/
  chmod 0644 /opt/jdbc/mysql-connector-java-8.0.23.jar

execute them and drivers will be readily available. If you choose a package or the installation program, drivers will be copied to a default directory. Please consult the documentation of the drivers and your operating system to learn their location that you'll later use to configure LibreOffice.

Register Drivers into LibreOffice

Once drivers are saved in a local user directory or inside a system one, you'll have to inform LibreOffice of their presence and location. To do it:

Start the LibreOffice main program, not Draw, Calc, Base nor any other subprogram.
Select the Tools → Options menù entry.
Select option Advanced to make the "Java Options" appear in the parameters window on the right.

Check for the "Use a Java runtime environemet" checkbox to be flagged and that at least one virtual machine is listed in the text box below it. If not, you'll have to manually inform LibreOffice of the presence of an installed Java environment clicking the "Add" button then searching for its exact location.
Click on the third button from the top right: "Class Path".

Press the "Add Archive" button.
Find and select the driver *.jar file.

Press the "OK" button.
Press the "OK" button of the options window.
Restart LibreOffice.

After a successful restart LibreOffice will be able to use the registered driver to connect to a RDBMS.

The configuration should be repeated for any user as files storing configuration are personal.

Create a Database

As previously mentioned LibreOffice cannot create new databases, but only connect to an existing one. Meaning that a data source must always be created with other means:

PhpMyAdmin: A RDBMS administration interface working via web.
mysql: A command line interface.
One of the thousands existing graphical applications written to manage a database server both locally and remotely.

Let's create a database named "presenze" ("attendance") and a user with the same name to manage it, the commands to execute via CLI will look like:

  mysql -u root -p

   CREATE DATABASE `presenze`;
   CREATE USER `presenze` IDENTIFIED BY '<password>';
   GRANT ALL ON `presenze`.`*` TO 'presenze';

which will create a new empty database and the administrator user that we will later use to connect from LibreOffice. Please use a secure password in place of <password>.

Configure and Test the Connection

Both database and credentials are available and we can now proceed with configuring the actual connection of the Base program to the server. The only difference between MariaDB and MySQL is the connection string, as I'll point out later on. The steps to follow are:

Start the LibreOffice Base application using the system menù entry or double-clicking on its purple icon.
The start window provides three choices: create a new database, open an existing database file or connect to an external data source.

Select the third option setting MySQL even if the target RDBMS is a MariaDB server, then press the "Next" button.

Set JDBC drivers as the main connector selecting the second entry of the list.

Configure the connection string parameters consisting of the database name, the IP address or the server domain name and the service related port. In the example I used "presenze" as database name, localhost, the alias of my local machine, as server address and port 3306 that is usually associated with a MariaDB / MySQL service. You should use values better suited to your network and setup.

In the "MySQL JDBC driver class" textbox at the bottom right set:

org.mariadb.jdbc.Driver for MariaDB.
com.mysql.jdbc.Driver for MySQL.

please mind the case of the string.

Set the authentication credentials. Fill "presenze" in the "User Name" textbox and flag the "Password required" checkbox. Test the connection pressing the test button. You'll be required to provide a password for user "presenze" used in the example. When everything is set right a success message is shown, otherwise an error one.

The most likely causes of errors are wrong passwords or misspelled parameter values. Repeat all of the configuration steps, checking every filled in value, is the faster way to spot the error. Reading the error message could provide some useful hints too.

Save the configuration. Base needs a name for a new file with extension odb where to store the connection string and all of the created objects like queries, forms, etc. Once saved, the file can be used whenever you want to connect to the database.

Connect to the Database

To connect thorough the newly created file, open it with a double click of your mouse. Starting up, Base will show its main window consisting of a left vertical bar, containing the categories of objects saved into the file, and two vertically divided wider windows to the right. The upper window contains links for creating new objects while the lower one contains the full list of the previously created ones. Select an object or a category and you'll be required to provide a password to connect to the RDBMS:

You'll be able to access existing objects or start a wizard only after a successful authentication.

A Brief Description of the Included Objects

An odb file can store 4 types of objects:

Tables: Data sources. For a remote database, tables do not contain the actual data, just a definition and a link to access and read it.
Queries: Saved database searches. When a query is used very often it is useful to save it in order to spare time and the effort to write it again and again.
Forms: Data aware forms that can be easily created recurring to the proper wizard and customized using a programming language.
Reports: Documents filled in with data extracted from tables.

Select a class of objects and links to the wizards will appear. When you create a new table remember to always assign a primary key to it otherwise Base will not handle data correctly. A primary key is a unique identifier for a table row.

Other Options

Do valid alternatives to JDBC exist ? A proper answer would fill in several articles. We can briefly state that many factors play a part in the answer among them the environment the application is developed for, the programming language, the architecture, the operating system and much more.

The first option that comes to mind is libraries provided by the RDBMS software house. Such libraries are usually written in the programming language chosen to develop the application and RDBMS software houses seldom support every existing language. Moreover libraries require installing. Again RDBMS developers do not support every existing operating system nor platform, but only the ones they have a reason for. Finally libraries are often proprietary software and do not come cheap. When they exist, they have the obvious advantage to support the vast majority if not every functionality of the target database and usually grant better performances. All in spite of the general solution JDBC provide.

A second option, available mainly on Windows, is ODBC drivers which are in practice very similar to JDBC. Not every RDBMS software house provides ODBC drivers for their product and, when they do, they usually offer support for Windows only. ODBC drivers require installing and configuration on the target host, a task only an administrator can perform. The 32 bit version and the 64 bit one can differ and very often the whole tool chain must support one or the other in order to avoid conflicts. They share the same shortcomings of libraries with no one of the gains in performances and supported functionality.

The last option, mentioned to be through only, consists of the Borland DB Engine: an "alternative" to ODBC developed by one of MicroSoft historical rivals. The technology was declared dead in 2014, but you can still find some applications using it. Sadly it shares all of the ODBC problems it proposed to replace. BDE is anyways not supported by LibreOffice and as such has no use for the scope of this article.

Back-up

A brief notice concerning data safety. A database saved into a file can easily be copied: a copy of an odb file is a real back-up useful to perform a full recovery were the original one deleted or corrupted. When using a remote database you save locally objects and data definition only, never the real data. A solid back-up consists of both the odb file and a data dump read from the database server. Please refer to the documentation of your RDBMS for better information about saving a consistent copy of data that youll be able to use in the eventuality to perform a full recovery.

CONCLUSIONS

In this article I discussed how to connect the LibreOffice Base module to a remote RDBMS through JDBC drivers. Some examples were provided recurring to MariaDB and/or MySQL, two feature rich and popular relational databases, but the procedure is a general one and works with pretty much every other RDBMS available: Firebird, Ms SQL Server, Oracle, PostgreSQL, SyBase, ecc. It works with every operating system of the Linux, MacOsX or Windows family and is architecture neutral meaning that 32 or 64 bit make no significant difference. Connecting LibreOffice to a remote RDBMS grants the power and flexibility of a relational database shared through a network with a familiar and easy to use graphical interface.

For any feedback, questions, errors and such, please e-mail me at studiosg [at] giustetti [dot] net

Languages: English - Italiano