It is only natural given the diverse challenges of FOSS that the interfaces between LibreOffice and MariaDB (or MySQL) applications have attained official support status. Instead of threads in community sites and the occasional acknowledgment of dependent drivers, there is now a well worn path to improve office productivity. You can use a JDBC driver class to connect to a JDBC database from LibreOffice. The driver class is provided by the database manufacturer. Two examples of JDBC databases are Oracle and MySQL. The driver classes must be added to LibreOffice in Tools - Options - LibreOffice - Java. Anyone who wants to get up to speed quickly with LibreOffice Base will find this book valuable. You may wish to first read Chapter 8, Getting Started with Base, in the Getting Started guide. MySQL, Oracle, and PostgreSQL. Base includes additional functionality that allows you to create full data-driven applications. Hi, Getting MySQL database information into Calc requires the use of Base. Base is used to make the actual connection to the database. See Chapter 2 Creating a Database in the Base handbook for connection information - click here.
Base – the database editing program of LibreOffice – offers several features when it comes to external database connection. One interesting feature is that Base lets you connect to an external database not only with ODBC and JDBC but with native connectors too. Using the native connector instead of a standard like JDBC sometimes has a positive impact on performance.
Considering that MySQL is one of the most used database management systems worldwide it is clear that the support of connecting to a MySQL database is an unavoidable part of Base. Currently there are three ways supported to connect to a MySQL database: with ODBC, JDBC and using a native connector. However, the last option was only available as an extension, and it is the part of the core project only since November 2018.
Why was it only an extension at the first place? The native connection was implemented by using the C++ connector of MySQL, which is licensed under GPL. Because of that it could not be put into the core project. So what is the solution then?
Because of the above mentioned problem we, at Collabora decided to use the connector of MariaDB instead. MariaDB is a fork of MySQL which has the same database structure and indexes as MySQL. It has a C API which can be used to connect to MySQL and MariaDB databases too. What is more, the connector is LGPL licensed, which means that it can be used in the core project too. So the next task was to use this API to implement the sdbc driver.
Some parts of the implementation is pretty straightforward. It is pretty easy to create a connection and pass options through the function “mysql_options”. It was also clear to me how to pass things like user and password to the DBMS.
There were a few things though which were a bit more challenging. The XDatabaseMetadata interface for example – which is responsible for providing information about the current state and capabilities of the database – could not be implemented with only a few function calls. In order to implement them, I had to construct SQL queries for most of the methods. Database information are available in the schema called “INFORAMTION_SCHEMA”. That task took some time since the interface has more than a hundred methods which had to be implemented.
Another challenge was a bug about parallel execution of result sets. The C API does not support the usage of two result sets simultaneously, but the sdbc standard supports it. The solution was to store the result of a query and free the mysql resources afterwards. That way a new result set can be fetched with the C API while the previously fetched data is stored in memory.
Having the native MySQL connector in the core project has several advantages. First, it is easier for the user to install it. It is bundled with the core project, there is nothing to do there. Also, it improves maintainability, because it does not have to be maintained separately as an extension. For example the automatically triggered clang plugins do not run on extensions. It can easily happen With an extension that after a few releases it is not usable anymore, because of the lack of maintenance.
Besides that, in order to improve quality and maintainability I created a test suite to test the implementation of the sdbc driver of a MySQL database. This test suite does not run automatically with each build though. In order to test it, a running MySQL or MariaDB server is needed, so only manual execution of the test suite is supported. The test suite can be triggered with the same method as the other tests, but you have to declare an environment variable as well (CONNECTIVITY_TEST_MYSQL_DRIVER). The variable should store the URL of an available MySQL or MariaDB server. It should contain a user, a password, and a schema, which can be used to test the functionality of the driver.
Although a lot of things work now, there might be some bugs hidden. Feel free to test the driver if you like, file a bug and please CC me in Bugzilla if you found something interesting.
You can also take a look at my talk on FOSDEM about the MySQL connector and other improvements of Base.
Libreoffice Mysql Jdbc
In the about page on the Libreoffice web site for Base it mentions 'native-support drivers for some of the most-widely employed multi-user database engines: MySQL/MariaDB' but the only thing I can find is ODBC/JDBC drivers. Some of the databases I am connecting to are quite large and ODBC/JDBC crashes and is VERY slow. I usually write SQL directly using HeidiSQL, but would like to be able to use a gui for others on my team. Is there a native connection directly to MySQL/MariaDB?
Libreoffice Mysql Extension
Libreoffice 18.104.22.168OS- Windows 8.1 - Linux Mint 17.3MariaDB 10.0.11
Libreoffice Mysql Odbc
Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-12 22:31:33.018155
Libreoffice Mysql Driver
Does 'Windows 8.1 - Linux Mint 17.3' mean you have a dual boot system, or is this all on windows OS? I'm also interested if you got this working and if you will please visit this question: https://ask.libreoffice.org/en/questi...