Ask Your Question
0

How do I set up a JDBC connector for MariaDB (or MySQL) in Base?

asked 2016-10-08 06:13:06 +0100

EasyTrieve gravatar image

updated 2016-10-08 06:16:50 +0100

I want to try out JDBC, and use it to connect to my localhost MariaDB* server.

But I can't seem to find LO documentation that clearly explains what I enter for Datasource URL and JDBC driver class, and also what else I need to do to get this working. Here is where I am stuck at the moment:

image description

I've looked at the LO JDBC example documentation at this page. I think the Datasource URL should be set to either mysql://localhost:3306/ or mysql://localhost:3306/mysql.

Are Datasource URL and JDBC driver class two alternative ways to set up JDBC? Or do you need each?

If you need each, then what exactly is a JDBC driver class and what do I use for this?

As I searched for an answer to this I found a reference to setting up this screen, which I still don't have a clue about so perhaps you could help explain that to:

image description

Are Archive or Folder alternatives?

Thanks.


I'm using LO 5.2.2.2 on Linux, Debian 8.6 Jessie stable. Also I already have a MySQL native connector working. But because I can't seem to get unixODBC to connect at the moment without crashing Base I was hoping to try the JDBC connector. (BTW, my unixODBC does work and I can test it w/ isql.)

For those unfamiliar with MariaDB, it's a drop in free replacement for MySQL.

edit retag flag offensive close merge delete

Comments

First uninstall Java 7 and install Development Kit 8u102, which you'll need.

rautamiekka gravatar imagerautamiekka ( 2016-10-08 14:34:59 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2016-10-11 07:21:59 +0100

EasyTrieve gravatar image

updated 2017-05-30 23:22:47 +0100

  • [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:

image description


1) If you don't already have it installed, install MariaDB from here. (I have the Debian Linux MariaDB 10.1 Series installed.)

Test MariaDB:

  • In linux:
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 datadir parameter.

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 ...

(more)
edit flag offensive delete link more

Comments

1

I'm not sure what you could mean by "...I was really looking for a more comprehensive answer..." because the link I provided contained all of what you stated & more (including how to find out "...how the JDBC Driver Class of "com.mysql.jdbc.Driver" is arrived at.."). If you wanted "pictures" I would have pointed you here - Creating a DB but it doesn't have the links for the connectors.

Ratslinger gravatar imageRatslinger ( 2016-10-12 08:09:08 +0100 )edit

Before you re-write more of the existing documentation, you may want to peruse what is in existence - click here.

Ratslinger gravatar imageRatslinger ( 2016-10-12 08:10:42 +0100 )edit

@EasyTrieve - sometimes good to have details but if you look at my edited answer things can change.

Ratslinger gravatar imageRatslinger ( 2017-02-05 02:36:41 +0100 )edit

@EasyTrieve Just found this today while testing different DB performance on different. You may know this already but just in case I'll pass it on. Native is the best way to connect - many times faster through my testing. While testing MariaDB the current MySQL native connection (pre-installed as SDBC driver for Apache Office 1.2.0) would not connect. Did find this one which did work -click here.

Ratslinger gravatar imageRatslinger ( 2017-03-10 23:25:20 +0100 )edit

The problem is that it replaces the installed one and I can't seem to find where to get the original again. Even reloading LO didn't work. To me that is a problem because the one which works for MariaDB in v5.2.3.3 doesn't work for MySQL in that LO version.

Again, you may already know of this. If not it may be worthwhile. It's quite a bit faster.

Ratslinger gravatar imageRatslinger ( 2017-03-10 23:28:48 +0100 )edit

@EasyTrieve Just a correction. Retested 1.2.0 connector and it worked without a problem on Debian with MariaDB. Seems the first time I tried this it failed because there actually was NO Native connector present. I also found my copy of the .otx so I have it if needed.

Ratslinger gravatar imageRatslinger ( 2017-03-11 21:46:26 +0100 )edit

@EasyTrieve The aforementioned Native connector works in connecting to AOO, and again, it is much faster than JDBC connection. Also, using mysql-connector-java-5.1.35-bin.jar I had no problem with JDBC connection leaving com.mysql.jdbc.Driver as is.

Ratslinger gravatar imageRatslinger ( 2017-05-31 03:04:34 +0100 )edit

@Ratslinger, I hear you but I'm confused. If you install this does it mess up LO, "The problem is that it replaces the installed one and I can't seem to find where to get the original again. "? Also it only says works with LO 4.2 & 4.3. ?? My memory is failing, but I thought there used to be a native option and it was removed for some reason. Now I don't see Native under Connect to an existing database. Also looks like you are running w/ mysql (oracle) driver, not maria ?

EasyTrieve gravatar imageEasyTrieve ( 2017-06-04 15:14:26 +0100 )edit

@EasyTrieve Sorry for the confusion. The v1.2 is what works for both LO & AOO. The location is here. As for the JDBC, the Oracle works with AOO & MariaDB but the Maria connector does not.

Ratslinger gravatar imageRatslinger ( 2017-06-04 15:43:47 +0100 )edit

End of step 4: your uncertainty about ODF

ODF is a generic term not an actual file extension. It stands for Open Document Foundation or Open Document Format depending on the context. So an ODF spreadsheet has an extension .ods for example, an ODF database. odb, and so on. LO and OO both use ODF formats by default

Hope that clarifies

trueriver gravatar imagetrueriver ( 2018-07-19 12:57:09 +0100 )edit
2

answered 2016-10-08 17:36:03 +0100

Ratslinger gravatar image

updated 2017-05-31 03:10:53 +0100

You don't need JDK - JRE is fine but I would think about upgrading to version 8.

I have been using the MySQL JDBC connector(with MySQL) for quite some time now and have had no problems. This page (click here) contains instructions for connection of Base and MySQL and a link to the JDBC connector. The Class Path is discussed but is nothing more than pointing to (using Add Archive) the connector .jar file.

Once installed you create a new Base file selecting Connect to an existing database and select MySQL. This is where you enter the connection properties:

image description

And

image description

If all entered OK, you're up and running.

Edit: 2/4/17

Although subtle, there seems to be some differences between MariaDB and MySQL. I attempted to perform an installation based upon the instructions presented by @EasyTrieve with the following results:

Start with almost newly installed OS of Debian 8.6 using Cinnamon v2.2.16 (almost because a few months back I installed LO v5.2.3.3). Using Synaptic Package Manager, installed MariaDB-server and all recommended affliates. During the installation part, you will be asked for a password (for root account).

Signing on was a bit of a pain - mentioned instructions were overly stated. You just need mysql -u root -p for first time sign-in. Then prompted for password from the installation step. Signed in so listed databases:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Not even the "test" database stated in the instructions here. Luckily the next page of instructions provided the creation of a DB & some tables here. show tables; and show databases; proved they were created.

LO , as mentioned, was installed. Moved over JRE 8u102 from Mint 18 and pointed to it in LO. Downloaded mariadb-java-client-1.5.7.jar - it would not work no matter what was done. Tried to get v1.5.3 but they wanted a sign in. Signed up but even when asking to v1.5.3 only 1.5.7 was offered.

Copied MySQL connector from Mint 18 - Connector/J 5.1.36, localhost:3306 & my "test" DB and connection worked immediately.

Then tested with JDBC directly and confirmed "Relationship" problem.

edit flag offensive delete link more

Comments

It's been said here many times JDK is necessary nowadays, JRE ain't enough.

rautamiekka gravatar imagerautamiekka ( 2016-10-08 17:38:22 +0100 )edit
1

Please read closely. JDK only necessary for MAC OSX 10. JRE works fine in Linux and Windows. I use JRE and LO 5.2.x without problem for HSQL (embedded & Server), MySQL, PostgreSQL, and SQLite. If you look, a lot of those answers are from me. See this post and also the link from there.

Ratslinger gravatar imageRatslinger ( 2016-10-08 17:45:57 +0100 )edit

While that may be true, there are other softwares which either require or benefit from JDK, it usually won't hurt to have it instead. Also, at least on Ubuntu, there's no way to install just JRE the canon way. I think they came to the same conclusion: shouldn't hurt having JDK.

rautamiekka gravatar imagerautamiekka ( 2016-10-08 18:07:50 +0100 )edit

Other software doesn't necessarily have anything to do with LO. While installing JDK instead of JRE is not a problem (JRE is in JDK) the download is three times larger and occupies more resources. The point is, JDK is not usually needed for LO. I'm on Xubuntu and Mint. I install from Oracle and LO. Seen a number of problems using distro installs.

Ratslinger gravatar imageRatslinger ( 2016-10-08 18:17:37 +0100 )edit

Interesting. Humm? Glad to hear that the Relationship issue is confirmed. Thank you. Also noticed Edit | Database Properties has same basic info, but looks a bit different depending on JDBC, or MYSQL|JDBC setup. Possibly missing or different connection parameters?? For what it's worth, did you try org.mariadb.jdbc.Driver for the driver path, when used w/ classpath: /usr/share/java/mariadb-java-client-1.5.7.jar?

EasyTrieve gravatar imageEasyTrieve ( 2017-02-05 08:35:04 +0100 )edit

Yes tried everything I could imagine with the maria db connector. Nothing worked. The worst was the web site. Sign in, ask for 1.5.3 and still get 1.5.7.

Ratslinger gravatar imageRatslinger ( 2017-02-05 15:45:32 +0100 )edit

@Ratslinger, My bad. Revised my answer, and my comment to you above last night, but comment revisions didn't seem to take. --> I have moved from 1.5.3.jar up to 1.5.7.jar. Also, sorry, but I had a mistake showing above. Was showing wrong driver path. Again, corrected to: use with /usr/share/java/mariadb-java-client-1.5.7.jar with org.mariadb.jdbc.Driver. Appreciate you checking my work.

EasyTrieve gravatar imageEasyTrieve ( 2017-02-05 17:13:35 +0100 )edit

MariaDB connector was my fault. I was trying to use com.mariadb.jdbc.Driver. org works fine. Also confirmed my suspicions - mariadb-java-client-1.5.7.jar can be located wherever wanted. I keep it in my home directory. Also confirmed MariaDB works with SQL Workbench/J (I use this more than any other). Good news as there is a lack of good tools (my opinion) for it in Linux.

Ratslinger gravatar imageRatslinger ( 2017-02-05 22:33:07 +0100 )edit

@Ratslinger yes that is right. I made this mistake too, at first

Everywhere you see com.mysql.jdbc... you need to change to org.mariadb.jdbc...

As well as changing the name when they forked, they mover from com to org top level just to rub in the point that they are not-for-profit ;)

trueriver gravatar imagetrueriver ( 2018-07-19 18:35:29 +0100 )edit

@trueriver Of course it is right as I stated so in my comment. Don't see the purpose of the comment. I don't even use MariaDB. Only did here for testing purposes to answer question. Actually moving away from MySQL also. Have been running parallel testing with both PostgreSQL & Firebird server.

Ratslinger gravatar imageRatslinger ( 2018-07-19 19:28:32 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-08 06:13:06 +0100

Seen: 13,745 times

Last updated: May 31 '17