LibreOffice Base

From Egbert's Wiki

Introduction

vbs-20141025 UPDATE: Use the native MySQL connector. Download .ocx file and install.

LibreOffice Base has an internal database engine. To access a remote MySQL database one should use the ODBC or JDBC connector. This page is about the installation of JBDC. Most information can be found in the LibreOffice 4.x Base documentation.

MySQL databases

There was an extension for MySQL, the MySQL Native Connector, which should allow Base to connect directly to a MySQL database. The extension was in the LibreOffice Extensions center under the Base Category but is not being developed at present.

Install the Java Archive

General access to MySQL for versions of LibreOffice from 3.5 onwads is via JDBC or ODBC. To be able to use JDBC, it is necessary to install mysql-connector-java.jar. This Java Archive file is best copied into the same folder where the current java version used in LibreOffice is located. This is likely to be a subfolder like ...javapath.../lib/ext for a Linux installation.

For Windows installations the guidelines can be found here. The version test gave me 1.6.0 but Tools->Options->LibreOffice->Advanced, gave met 1.7.0 with the path C:\Program Files (x86)\Java\jre7. The before mentioned website is somewhat outdated, they speak of 5.1.19 but the current version is 5.1.33 (Sep. 2014).

My system settings had no CLASSPATH; I've added it in the systembox. Apart from that I've also added the archive to LibreOffice Tools->Options->LibreOffice->Advanced as described in the website. Fron here on follow the instructions in the LO 4.x Base manual.

Problem

While testing with the speldorado test database on server.vandenbussche.nl, one of the tables, shop_posts, refuse to open because of a NULL date string. This site gave the solution in the last posting: The connect string must be jdbc:mysql://host:3306/db_name?zeroDateTimeBehavior=convertToNull. I used te last part speldorado?zeroDateTimeBehavior=convertToNull as database in the Modify->Database dialog. This is also the location where the character set can be defined (change system to UTF-8).