Setting up LibreOffice to access MySQL database with JDBC driver

By: (plus.google.com) +David Herron; Date: 2016-08-24 13:21

Tags: Databases » MySQL » JDBC » LibreOffice

I wanted to try using LibreOffice Base on Mac OS X to play with a MySQL database. I thought it would be easy, just connect to the database and start doing queries, maybe even pull data into a LibreOffice spreadsheet for advanced data munging. But doing the obvious thing got me messages like "No Java Installation found" and "the driver class com.mysql.jdbc.Driver could not be found". Further, yahoogling with some obvious search phrases turned up nothing. Finally, going to LibreOffice.org and looking at the official LibreOffice Base documentation showed no instructions on setting up JDBC MySQL with LibreOffice.

LibreOffice Base is supposed to be an excellent thingymajob for doing database whatever. I don't know, I've never used it. I'm looking forward to using it in the LibreOffice Spreadsheet tool instead.

This was very frustrating, but it turns out to be easily fixed.

Install Java JDK for your operating system

Java does come pre-installed on Mac OS X but it's just a JRE. Turns out that LibreOffice requires a JDK (Java Development Kit) to be installed, rather than a JRE (Java Runtime Environment). I don't grok why that would be the case, but I found multiple sources saying the same thing. I looked at some issues in the issue queue, and it seems there's some fugly history between Apple's Java and Oracle's and ... uh ... that's not important because we just install Oracle's Java and get on with things.

The first step is to go to (www.oracle.com) http://www.oracle.com/technetwork/java/javase/downloads/index.html for the official Oracle Java download page. What you want is the JDK, so click on the JDK Download button, then on the next page select the download archive matching your operating system. Make sure to agree to the license.

This gives you a JDK installer, for your operating system, that you run to install the JDK on your system.

Get the MySQL JDBC driver

You don't install the JDBC driver so much as unpack it on your system, somewhere.

Go here: (dev.mysql.com) https://dev.mysql.com/downloads/connector/j/

Then download the archive they provide, and unpack it somewhere on your computer. It doesn't matter where.

Install LibreOffice and MySQL

You already have this installed somehow, I'm assuming. Right?

I install LibreOffice using the installer from (libreoffice.org) http://libreoffice.org

I install MySQL using MacPorts.

LibreOffice Java Preferences

This took awhile to find but it turned out to be the critical piece.

For the Mac OS X LibreOffice, you open Preferences and navigate as shown below. For other OS's, it appears you use the Tools menu to get to the Preferences doohickeythingamajob.

Once you have the JDK installed, some choices appear here. Tick the "Use a Java runtime environment" then tick the radio button in the box below. You'll need to restart LibreOffice at this point for the change to take effect.

Once I'd done this the error message while setting up a MySQL JDBC database changed from "No Java installation found" to "the driver class com.mysql.jdbc.Driver could not be found". This will be your clue that you're on the right path.

Adding MySQL JDBC driver to LibreOffice

Again, you don't "install" the JDBC driver so much as unpack it somewhere on your system. The trick is to get the driver onto the Classpath of LibreOffice. It doesn't work to simply set your login CLASSPATH variable. Of course not, this is Java and nothing is easy.

You'll notice there's a Classpath button in the window. Click on that.

What you do is click the Add Archive button, and add the mysql-connector-java .jar file you downloaded above. You'll again need to restart LibreOffice.

Setting up MySQL JDBC database in LibreOffice Base

Now we can do the thing we set out to do, setup a MySQL database in LibreOffice Base.

Select MySQL as the type of database.

We'll be connecting with JDBC.

If all goes well, you'll get this message with the Test Class button.

Enter the "Database name" and "Server" in the boxes above.

Next step is verifying you can access the database. Enter the login name and password credentials, and click Test Connection, and if all went well you'll get the above message.

This last step finalizes the database in LibreOffice.

After this you'll be in LibreOffice Base and can do database things there. I don't know enough to recommend anything you can do. I did try a query, and it came up quickly in a nice table.

« The Doctor, Harriet Jones, Donald J Trump, and Hillary Clinton InfluxDB CLI and Configuration Options »
2016 Election Acer C720 Ad block AkashaCMS Amazon Amazon Kindle Amazon Web Services America Amiga and Jon Pertwee Android Anti-Fascism AntiVirus Software Apple Apple Hardware History Apple iPhone Apple iPhone Hardware April 1st Arduino ARM Compilation Artificial Intelligence Astronomy Astrophotography Asynchronous Programming Authoritarianism Automated Social Posting AWS DynamoDB AWS Lambda Ayo.JS Bells Law Big Brother Big Data Big Finish Big Science Bitcoin Mining Black Holes Blade Runner Blockchain Blogger Blogging Books Botnets Cassette Tapes Cellphones China China Manufacturing Christopher Eccleston Chrome Chrome Apps Chromebook Chromebox ChromeOS CIA CitiCards Citizen Journalism Civil Liberties Clinton Cluster Computing Command Line Tools Comment Systems Computer Accessories Computer Hardware Computer Repair Computers Conservatives Cross Compilation Crouton Cryptocurrency Curiosity Rover Currencies Cyber Security Cybermen Cybersecurity Daleks Darth Vader Data backup Data Formats Data Storage Database Database Backup Databases David Tenant DDoS Botnet Department of Defense Department of Justice Detect Adblocker Developers Editors Digital Photography Diskless Booting Disqus DIY DIY Repair DNP3 Do it yourself Docker Docker MAMP Docker Swarm Doctor Who Doctor Who Paradox Doctor Who Review Drobo Drupal Drupal Themes DVD E-Books E-Readers Early Computers Election Hacks Electric Bicycles Electric Vehicles Electron Eliminating Jobs for Human Emdebian Encabulators Energy Efficiency Enterprise Node EPUB ESP8266 Ethical Curation Eurovision Event Driven Asynchronous Express Face Recognition Facebook Fake News Fedora VirtualBox Fifth Doctor File transfer without iTunes FireFly Flash Flickr Fraud Freedom of Speech Front-end Development G Suite Gallifrey git Github GitKraken Gitlab GMAIL Google Google Chrome Google Gnome Google+ Government Spying Great Britain Green Transportation Hate Speech Heat Loss Hibernate Hoax Science Home Automation HTTP Security HTTPS Human ID I2C Protocol Image Analysis Image Conversion Image Processing ImageMagick In-memory Computing InfluxDB Infrared Thermometers Insulation Internet Internet Advertising Internet Law Internet of Things Internet Policy Internet Privacy iOS Devices iPad iPhone iPhone hacking Iron Man iShowU Audio Capture iTunes Janet Fielding Java JavaFX JavaScript JavaScript Injection JDBC John Simms Journalism Joyent Kaspersky Labs Kext Kindle Kindle Marketplace Large Hadron Collider Lets Encrypt LibreOffice Linux Linux Hints Linux Single Board Computers Logging Mac Mini Mac OS Mac OS X Machine Learning Machine Readable ID macOS macOS High Sierra macOS Kext MacOS X setup Make Money Online March For Our Lives MariaDB Mars Mass Violence Matt Lucas MEADS Anti-Missile Mercurial MERN Stack Michele Gomez Micro Apartments Microsoft Military AI Military Hardware Minification Minimized CSS Minimized HTML Minimized JavaScript Missy Mobile Applications Mobile Computers MODBUS Mondas Monetary System MongoDB Mongoose Monty Python MQTT Music Player Music Streaming MySQL NanoPi Nardole NASA Net Neutrality Network Attached Storage Node Web Development Node.js Node.js Database Node.js Performance Node.js Testing Node.JS Web Development Node.x North Korea npm NVIDIA NY Times Online advertising Online Community Online Fraud Online Journalism Online Photography Online Video Open Media Vault Open Source Open Source and Patents Open Source Governance Open Source Licenses Open Source Software OpenAPI OpenJDK OpenVPN Palmtop PDA Patrick Troughton Paywalls Personal Flight Peter Capaldi Peter Davison Phishing Photography PHP Plex Plex Media Server Political Protest Politics Postal Service Power Control President Trump Privacy Production use Public Violence Raspberry Pi Raspberry Pi 3 Raspberry Pi Zero ReactJS Recaptcha Recycling Refurbished Computers Remote Desktop Removable Storage Republicans Retro Computing Retro-Technology Reviews RFID Rich Internet Applications Right to Repair River Song Robotics Robots Rocket Ships RSS News Readers rsync Russia Russia Troll Factory Russian Hacking Rust SCADA Scheme Science Fiction SD Cards Search Engine Ranking Season 1 Season 10 Season 11 Security Security Cameras Server-side JavaScript Serverless Framework Servers Shell Scripts Silence Simsimi Skype SmugMug Social Media Social Media Networks Social Media Warfare Social Network Management Social Networks Software Development Software Patents Space Flight Space Ship Reuse Space Ships SpaceX Spear Phishing Spring Spring Boot Spy Satellites SQLite3 SSD Drives SSD upgrade SSH SSH Key SSL Stand For Truth Strange Parts Swagger Synchronizing Files Tegan Jovanka Telescopes Terrorism The Cybermen The Daleks The Master Time-Series Database Tom Baker Torchwood Total Information Awareness Trump Trump Administration Trump Campaign Twitter Ubuntu Udemy UDOO US Department of Defense Virtual Private Networks VirtualBox VLC VNC VOIP Vue.js Walmart Weapons Systems Web Applications Web Developer Resources Web Development Web Development Tools Web Marketing Webpack Website Advertising Weeping Angels WhatsApp William Hartnell Window Insulation Windows Windows Alternatives Wordpress World Wide Web Yahoo YouTube YouTube Monetization