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.

How to Reset the Root Password in MySQL

For those who have had the annoyance of losing their root password in MySQL, or  will loose it yet, here’s a tip which can save the day.

Stop or kill the MySQL service

# killall mysqld    or    # service mysqld stop

Start MySQL in safe mode:

# mysqld_safe –skip-grant-tables &

log in using the mysql client:

# mysql

Access the MySQL database

> use mysql;

Setting new MySQL root password:

> update user set password = password (‘type your new password here’) where user = ‘root’ and host = ‘localhost’;

Reload privileges by typing:

> flush privileges;

Exit MySQL:

> quit

Restart MySQL  in normal mode:

# service mysqld stop

And finally, let’s start with the new MySQL root password:

# service mysqld start

And voila, your new root password and access granted to the database again.

Quickly convert MS Excel files into MySQL tables

Xls2mysql is a small tool for converting MS Excel files to MySQL database tables that is ready for use with PHP.”Upload your file and we’ll send you PHP code to generate the table and insert your data for your automatically!”

SO those of you web developers out there that need an easy way to convert files to database tables or those of you that work with databases, this tool should come in handy. Give it a try.

MySQL vs PostgreSQL- An depth comparison

If you are like me, you probably have wondered which is better- MySQL or PostgreSQL. Well both have their strengths and weaknesses. Your choice of either will depend on your task at hand.
“MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle.
“MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.” 
The full comparison of the two database management systems can be found on this wiki page. So if you are undecided about which of the two to use, then you surely should find some insight in the above linked comparison page.