Libreoffice Base Connect To Mysql

Posted onby admin

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.

Open the LibreOffice suite and then open Base. You must select MySQL from the Connect To An Existing Database drop-down (Figure A).

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.

How to connect to mysql databaseConnect
  • MySQL/MariaDB databases Base can connect to MySQL and MariaDB databases by three methods. The simplest and fastest way is direct connection with the MySQL connector. The other two are connection using ODBC or JDBC. Create a user and a database After MySQL or MariaDB has been installed, do the following steps in the sequence described.
  • LibreOffice Base is an open source RDBMS (relational database management system) front-end tool to create and manage various databases. Preparing the ODBC Connection. First, make sure to prepare MariaDB Connector/ODBC as explained in MariaDB Connector/ODBC. That includes: Download the latest MariaDB Connector/ODBC; Copy the shared library libmaodbc.so to /usr/lib/multi-arch.
  • In the Welcome frame of the Wizard: Ensure Select database is selected (default) Click Connect to an existing database button For the Connect to an existing database, select MySQL from the drop-down list.

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?

Mysql

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.

C++ Connect To Mysql Database

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.

Connect To Mysql Server

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.

How To Connect To Mysql Database

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.

Connect to mysql php

Libreoffice Base Mysql

You can also take a look at my talk on FOSDEM about the MySQL connector and other improvements of Base.

Tutorials‎ > ‎

LibreOffice Base: Connect to any kind of database

Although LibreOffice Base works with hsqldb (*.odf) files by default, you can, in fact, use it as a common GUI tool to connect to any kind of database, all you need is the right JDBC driver to do the job. Here is how you do it:
Open LibreOffice main app (with white icon), go to Tools->Options->Libre Office->Advanced and on the right-hand side, click the Class Path button on the right-side of the dialog:

In the dialog that comes up, add the path to your JDBC driver jar files (for example, sqlite-jdbc-3.7.2.jar) for whatever databases you want to connect. In a section below, I'll list down where to get these JDBC drivers for all kinds of databases.
Once this is done, click OK and exit from this configuration dialog, and then click File->New->Database. Select 'Connect to an existing database' radio button, and then select JDBC from the dropdown. Click Next and then enter the connection parameters and JDBC Driver class as shown below:

Click the 'Test Class' button to ensure that everything is good, and in the dialog that comes next, also test the database connection. After that, just file this new database which will be just a link to your actual database, and you are good to go! You can thus use LibreOffice Base as a proper GUI client tool (such as Toad or SQLDeveloper) to connect to just about any kind of database. The only condition is that the Java JDBC driver is available for that database (and it usually is, since Java is a widely used programming language).
Here is where you can obtain JDBC drivers for popular databases:
SQLite:
https://bitbucket.org/xerial/sqlite-jdbc
http://www.ch-werner.de/javasqlite/
MySQL:
https://dev.mysql.com/downloads/connector/j/
MariaDB:
https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/
MongoDB:
https://docs.mongodb.com/ecosystem/drivers/java/

Microsoft SQL-Server:
https://msdn.microsoft.com/en-us/library/mt484311(v=sql.110).aspx
Microsoft Access:
http://ucanaccess.sourceforge.net/site.html (Also refer to this)
Oracle:
http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html


References:
http://stackoverflow.com/questions/14640907/where-to-download-sqlitejdbc
http://askubuntu.com/questions/10635/how-can-i-install-sqlite-to-be-used-with-openoffice
http://askubuntu.com/questions/276725/how-do-i-get-libreoffice-base-to-access-ms-access-2003-data