1,423
edits
Changes
From Studiosg
Jump to navigationJump to searchConnect libreoffice to mariadb (view source)
Revision as of 10:10, 29 April 2021
, 10:10, 29 April 2021Added page about connecting LibreOffice to MariaDB
{{header_en|title=Connect LibreOffice Base to a MariaDB or MySQL RDBMS| keyword={{Template:keyword_en_libreoffice}}| description=Let's connect LibreOffice to a Database Server via JDBC | link_page=libreoffice_collegare_a_mariadb}}
== '''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, '''[https://it.libreoffice.org LibreOffice] Base''' includes a proper Relational Database Management System based on [http://www.firebirdsql.org '''FireBird SQL''']: a lightweight and feature rich relational database engine with a decades long development history. Previous versions, 6 for example, relied on [http://hsqldb.org '''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 '''R'''elational '''D'''ata'''B'''ase '''M'''anagement '''S'''ystem 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 '''J'''ava '''AR'''chive, 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: [https://mariadb.com/kb/en/mariadb-connector-j-releases Mariadb Connector J Releases]. Installation packages can be downloaded clicking on a link of the [https://downloads.mariadb.org/connector-java/+releases 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 [https://dev.mysql.com/downloads/connector/j 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.
[[File:Libreoffice option advance.jpeg||Figure 1: LibreOffice Advanced Options.]]
* 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'''".
[[File:libreoffice_option_class.jpeg||Figure 2: Path.]]
* Press the "'''Add Archive'''" button.
* Find and select the driver *.jar file.
[[File:libreoffice_option_class_select.jpeg||Figure 3: Find the 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''
<syntaxhighlight lang="sql">
CREATE DATABASE `presenze`;
CREATE USER `presenze` IDENTIFIED BY '<password>';
GRANT ALL ON `presenze`.`*` TO 'presenze';
</syntaxhighlight>
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.
[[File:libreoffice_database_select.jpeg||Figure 4: Select a Database.]]
* Select the third option setting MySQL even if the target RDBMS is a MariaDB server, then press the "'''Next'''" button.
[[File:libreoffice_database_connect_001.jpeg||Figure 5: Connecting - Step 1.]]
* Set JDBC drivers as the main connector selecting the second entry of the list.
[[File:libreoffice_database_connect_002.jpeg||Figure 6: Connecting - Step 2.]]
* 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.
[[File:libreoffice_database_connect_003.jpeg||Figure 7: Connecting - Step 3.]]
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.
[[File:libreoffice_database_connect_004.jpeg||Figure 8: Connecting - Step 4.]]
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:
[[File:libreoffice_database_auth.jpeg||Figura 9: Authenticating.]]
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 you''ll 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''
----
* [http://www.firebirdsql.org FireBird SQL home page]
* [http://hsqldb.org Hyper SQL home page]
* [https://www.libreoffice.org LibreOffice home page]
* [https://www.wikipedia.org/wiki/JDBC Wikipedia JDBC page]
* [https://mariadb.org/ MariaDB home page]
* [https://www.mysql.com MySQL home page]
----
{{footer_en | link_page=libreoffice_collegare_a_mariadb}}
== '''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, '''[https://it.libreoffice.org LibreOffice] Base''' includes a proper Relational Database Management System based on [http://www.firebirdsql.org '''FireBird SQL''']: a lightweight and feature rich relational database engine with a decades long development history. Previous versions, 6 for example, relied on [http://hsqldb.org '''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 '''R'''elational '''D'''ata'''B'''ase '''M'''anagement '''S'''ystem 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 '''J'''ava '''AR'''chive, 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: [https://mariadb.com/kb/en/mariadb-connector-j-releases Mariadb Connector J Releases]. Installation packages can be downloaded clicking on a link of the [https://downloads.mariadb.org/connector-java/+releases 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 [https://dev.mysql.com/downloads/connector/j 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.
[[File:Libreoffice option advance.jpeg||Figure 1: LibreOffice Advanced Options.]]
* 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'''".
[[File:libreoffice_option_class.jpeg||Figure 2: Path.]]
* Press the "'''Add Archive'''" button.
* Find and select the driver *.jar file.
[[File:libreoffice_option_class_select.jpeg||Figure 3: Find the 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''
<syntaxhighlight lang="sql">
CREATE DATABASE `presenze`;
CREATE USER `presenze` IDENTIFIED BY '<password>';
GRANT ALL ON `presenze`.`*` TO 'presenze';
</syntaxhighlight>
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.
[[File:libreoffice_database_select.jpeg||Figure 4: Select a Database.]]
* Select the third option setting MySQL even if the target RDBMS is a MariaDB server, then press the "'''Next'''" button.
[[File:libreoffice_database_connect_001.jpeg||Figure 5: Connecting - Step 1.]]
* Set JDBC drivers as the main connector selecting the second entry of the list.
[[File:libreoffice_database_connect_002.jpeg||Figure 6: Connecting - Step 2.]]
* 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.
[[File:libreoffice_database_connect_003.jpeg||Figure 7: Connecting - Step 3.]]
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.
[[File:libreoffice_database_connect_004.jpeg||Figure 8: Connecting - Step 4.]]
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:
[[File:libreoffice_database_auth.jpeg||Figura 9: Authenticating.]]
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 you''ll 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''
----
* [http://www.firebirdsql.org FireBird SQL home page]
* [http://hsqldb.org Hyper SQL home page]
* [https://www.libreoffice.org LibreOffice home page]
* [https://www.wikipedia.org/wiki/JDBC Wikipedia JDBC page]
* [https://mariadb.org/ MariaDB home page]
* [https://www.mysql.com MySQL home page]
----
{{footer_en | link_page=libreoffice_collegare_a_mariadb}}
