Libreoffice Base Mysql

Posted onby admin

This page was last edited 13:11:56, 2011-05-20 by LibreOffice Help user WikiSysop. Content is available under the GNU Lesser General Public License (LGPLv3), unless otherwise specified, originally based on OpenOffice.org help. The MySQL Native Connector installs a native MySQL database driver. It is faster, seamless integrated, easier to administrate and there is no need to install and setup a JDBC or ODBC driver separately. It was never easier to use MySQL databases in LibreOffice Base. This is for Windows operating systems and LibreOffice versions 4.0.0 and later.

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

I think LibreOffice Base has so much underutilized potential as a rapid application development platform, business intelligence platform and just a general reporting platform. Not to mention the fact that registered data objects can be used in all of the other LibreOffice applications to make really amazing documents and improve the work-flow of any office. Anyone who has actually used MS Access knows how powerful it can be used to be for these types of purposes. The most recent version of Access seems to have lost a lot of the features that made it useful. This is okay since most power-users are still using Access 2003. LibreOffice Base is not nearly as powerful as Access, specifically from a usability perspective. My biggest frustration with getting started with LibreOffice Base is the obscure and somewhat cryptic documentation around the platform that makes way too many assumptions about what someone new to LibreOffice actually knows. My hope is to provide some practical tutorials with real world use cases. So let’s get started by connecting Base to an existing MySQL database. In my opinion, the built in HSQL engine has a somewhat weird syntax and is generally not worth learning unless you are not planning on ever actually writing any SQL and only using the built in wizards. I would prefer to work with MySQL databases because they are ubiquitous, use a “standard” syntax and very powerful. In addition most practical office use cases will involve a central database and not a local database.

Libreoffice base mysql connector linux

Preparing Your MySQL Server

This is the part of the documentation that I find most obscure and confusing, so here is how to do it using LibreOffice 4.3 running on Ubuntu 14.04 LTS. The steps here will be slightly different depending on if you are developing on a local database or a remote database. If you are using a local MySQL database please feel free to skip this section. I do most of my database development inside of Linux Containers which essentially makes my databases “remote”. In order to allow remote connections we need to make a few changes to the default MySQL configuration. Please note that if you are doing these steps on a live production system you will need to be extra careful with users, permissions, ports that are opened, etc.. This falls outside of the scope of this tutorial but the rule of thumb is that if your database accepts connections from the outside world you should white list each IP address that will be connecting to it and block all others. The easiest way to do this in my opinion is with your firewall. By default MySQL only runs on the local host and is not accessible from remote hosts. To change this setting you need to edit the my.cnf file. 1) Open up my.cnf which is found in /etc/mysql/my.cnf 2) Find the bind-address and change if from the local host to the IP address of the server.

3) Restart MySQL

Libreoffice Base Mysql Connector

Install the MySQL JDBC Driver

On Ubuntu 14.04 this is very easy and can be done by running the following command:

Configure the Class Path in LibreOffice

Open up any LibreOffice App and go to Tools -> Options On the right hand side navigate to LibreOffice -> Advanced Select on the Class Path…button and load the new driver that was installed in the previous step. In order to do this you will need to select Add Archive… and select /usr/share/java/mysql.jar Once this has been loaded restart LibreOffice

Connect to your Database

Now comes the fun part. Now that we have taken care of all of the previous steps the rest is easy. To connect to your database open up LibreOffice Base.

Libreoffice Base Vs Mysql

  1. In the Database Wizard select Connect an existing database and chose the MySQL option from the dropdown menu.
  2. Select Connect using JDBC and hit next
  3. Enter the database name, server IP, port number and select next
  4. Enter the username for an existing user in your database and select next
  5. If you wish to use this database in other LibreOffice applications you should select the Yes, register the database for me radio button.
  6. Select Finish

Libreoffice 6 Base Mysql Connector

Congratulations! Now you can rock some custom queries, fancy forms, and TSP reports using Base. We will go through how to do all of that an more in future posts.