How To Connect MySQL with Open Office on Linux

To be able to combine the power of MySQL with the convenience of Open Office, opening a multitude of possibilities in Linux, you must connect MySQL with Open Office Base, the database application of Open Office.
There are several ways to do this, and I will illustrate one way that works effectively.

Connecting MySQL with  Open Office Base  – Existing Options

  • You can connect Open Office Base with MySQL  with JConnector, Java connector
  • With a native extension of the Open Office, MySQL Connector for OpenOffice.org, but that only works with version 3.1 and has not been updated yet
  • The ODBC connector, which was the best of the three.

Actually, I had no success with the JConnector  and MySQL Connector extension  is outdated and did not work with the current version of Open Office (3.2)
So we’ll see how to connect OpenOffice / MySQL with the ODBC connector.

ODBC connector

ODBC (acronym for Open Data Base Connectivity) is a standard software API specification for using database management systems (DBMS). ODBC is independent of programming language, database system and operating system.
ODBC was created by the SQL Access Group and first released in September, 1992. ODBC is based on the Call Level Interface (CLI) specifications from SQL, X/Open (now part of The Open Group), and the ISO/IEC.
The ODBC API is a library of ODBC functions that let ODBC-enabled applications connect to any database for which an ODBC driver is available, execute SQL statements, and retrieve results.
The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC achieves this by inserting a middle layer called a database driver between an application and the DBMS. This layer translates the application’s data queries into commands that the DBMS understands.

ODBC Connector Installation on Linux

You need to install two packages for working with the ODBC connector on Linux, unixODBC package ( rpm version and deb version) and  mysql-connector-odbc . Of course, if these packages are already in the repositories of your favorite distribution, the best thing to do is apt-get install package or yum / urpmi-i package. If not (as has happened with me) the solution is to download from the links provided above.

   1. After the packages are installed, to test if they work ok, type $  odbcinst -j, which should list the current configuration options of the ODBC connector. Something like:
      unixODBC 2.2.14
      DRIVERS …………: / etc / odbcinst.ini
      SYSTEM DATA SOURCES: / etc / odbc.ini
      USER DATA SOURCES ..: / home / user / .odbc.ini
   2. Now comes the important part: Set up the two files that will inform the Open Office Base how to communicate with MySQL. The files are odbcinst.ini and odbc.ini and are located in /etc
      odbcinst.ini – This file will point out where are the drivers that will make the connection with the Open Office Base. This is the configuration that works in my distribution and installation. The names of the libraries and their locations may be different, but nothing that a whereis and locate can’t solve.
      [MySQL]
      Description = ODBC for MySQL
      Driver = /usr/lib/libmyodbc5.so
      Setup = /usr/lib/libodbcmyS.so.1
      FileUsage = 1
   3. odbc.ini
      [MySQL-test] – Could be any name, this one was used only to illustrate

      Description = MySQL BD_Teste

      Driver = MySQL

      Server = localhost – Here is the IP address of the MySQL server, in this example it is running on the local machine.

      Socket = /var/lib/mysql/mysql.sock – The location of the MySQL socket must be declared, it is the one that will make the connection between the Open Office Base and MySQL

      User = username – name of the registered user in MySQL, which will be used by Open Office Base to log into  MySQL

      Password = password – registered user password in MySQL, to allow Open Office Base logging into MySQL

      Port = 3306 – This is the default port of MySQL

      Database = test – PLEASE NOTE: Here’s the name of DATABASE in MySQL  which we want to WORK with in Open Office Base . This database was previously created with any MySQL tool (Xampp, etc…)

      Option  = 3

      ReadOnly = No – To allow  Open Office Base to enter and modify  data in the database
   4. These were the steps in the Linux operating system. Now, the steps in Open Office Base
   5. Database File> New
   6. Click the Connect to a MySQL database and select the pulldown menu. Click Next.
   7. In the next window, you should accept the default choice to connect using ODBC, and click Next.
   8. Click Browse and select created connection ([MySQL test] should be listed in our case) and then click next.
   9. then type go, do not register the database. Save the file. And from now on you can start working with your MySQL database within the Open Office Base.

Final Thoughts
The steps presented here work perfectly locally. To work in a network (a remote MySQL server) there are some changes, which we will see  in the future. The odbc.ini file can be located in the default /home user, if one wants to change the configuration of the databases being accessed very often. In this case, the odbc.ini file that is in/etc should be blank, to avoid conflicts.
It should be noted that the names of the two libraries libmyodbc libodbcmyS may change, from a Linux distro to another, but they do the same thing, being necessary only to adapt the name to work in your preferred distro.

Leave a Reply

Your email address will not be published. Required fields are marked *