- [Update: Tried to also get this to work in OpenOffice but failed. Some notes in 1st paragraph below. May 30, 17]
- [Update: Only one of the two possible methods of connecting with JDBC allows relationships to work. See below under step 4. - Feb 3, 2017 ]
- [Update: Includes information for both Windows and Linux. - Jan 23, 2016]
STEP BY STEP
This is tested to work for Mariadb 10.1 running on a dual boot Linux Debian 8.6/Windows 10. (Also below are some tips for possibly using MySQL). EDIT: I tried to get this to work on OpenOffice 4.1.3, but can’t. First it wanted me to select the java class path archive in the home space rather than the root. I was able to manually type in the real address for it from the root. But then I got stopped when I tried to open the database below after I had entered the correct info in, especially “org.mariadb.jdbc.Driver”, but it comes back with the wrong driver class:
1) If you don’t already have it installed, install MariaDB from here. (I have the Debian Linux MariaDB 10.1 Series installed.)
mysql -u [your username, normally 'root'] --password='[your password]'
You should get a prompt, and enter something like:
MariaDB [(none)]> show databases;
| Database |
| bigbase |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
MariaDB [(none)]> quit
In Windows 10, I don’t know of a similar command line test, however you can use HeidiSQL.
Also in Linux HeidiSQL works nicely under Wine. (I think I like it better than phpMyAdmin.)
Data base settings are in
/etc/mysql/my.cfg (in Linux), and in
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini (in Windows). You’ll want to have the server stopped while editing these, especially if moving the path to the database files by changing the
You can stop, start or re-start the MariaDB server as follows:
a) In Linux:
sudo service mysql stop
sudo vi /etc/mysql/my.cfg
sudo service mysql start
b) In Windows:
Start | Find `Services` | Scroll down to `MySQL56` and right click on it for options to stop, restart, etc.
2) Download and install the MariaDB JDBC connector jar file from here. I have the
MariaDB Connector/J 1.5 Series (v 1.5.7) installed. You’re looking for the “Universal” OS version (it’s Java after all and will run on any OS), not the source, but you can also get the source if you like to peek inside to investigate the file hierarchy naming which will be used below. The important thing is you need the
mariadb-java-client-1.5.7.jar file (v1.5.3 ok too). (It’s a little confusing because it’s sometimes called a connector, and sometimes a client, and it’s both.)
On Linux Debian copy this jar file into /usr/share/java/. (I had a hard time finding where to copy it to. Another post suggested this location. Perhaps you could search your system for jar files. Or perhaps someone else can suggest how to determine where to put it.
On Windows copy this jar file into C:\Java\ (This is the suggested location, and because it is processor independent, it makes more sense than putting it into either of the two normal program file locations, i.e.
C:\Program Files (x86)\ (which holds 32 bit programs) or
C:\Program Files\ (which holds 64 bit programs).
3) Open LibreOffice (not LibreOffice Base, just LibreOffice) and go to
Set Classpath..., and click
Add Archive (to add a specific Jar file, rather than a directory of them), then enter:
4) Setup your database for use. Go to
Connect to an existing database.
Tip: From here, there are two different ways to proceed: You can simply select a)
JDBC, or b) you can firsts select
MySQL and then
JDBC. Method "a: allows you to view, open and edit your tables, but has a bug and does not allow table-to-table relationships to be viewed, edited, or work properly in subforms. If you choose method a you will get this error message when you select
4a) Method a: Select
JDBC (Direct method. Disables relationships!):
4b) Method b: Select
JDBC (This method makes relationships work properly):
This does the same thing as in 4a above, it just asks a few different questions to get there.
Note, in the following screen the “Connect directly” option is now gone. Not sure why. (Think it might have to do with using the MariaDB Driver rather than the MySQL Driver.)
The Database name, Server, and Port number combine into what’s called the Datasource URL like this:
mysql://hostname:port/database_name. (Notes: Perhaps someday this will use
mariadb://.... But for now enter
mysql://... even for MariaDB. Note, the URL seems to work even when you don’t have a web server setup. I’m guessing because we’re talking to localhost. You can also use
127.0.0.1 instead of
The JDBC Driver Class needs: “org.mariadb.jdbc.Driver” for the MariaDB JDBC driver, or (“com.mysql.jdbc.Driver” when using the MySQL JDBC driver). (You can open up the jar file and drill down into the file hierarchy to see that the folder
jdbc contains the
Also note that the free MariaDB database stuff is at their
.org site, and commercial MariaDB stuff is at their
.com web site.
Then save your new *.odb database file in an appropriate location and give it a better name. I suggest you name it
JDBC-<some name> so you know that this is a JDBC connected database. You can use this file to later open this database with the JDBC connnector. (I don’t know why it says,
ODF Database at the lower right of the screen, as it will be an
.odb file you create.)
5) The results:
If all goes well you should get a screen like this:
6) Inspecting your databases properties. Finally, you can inspect how your database is setup as follows:
Database properties... gives: