Connecting Open Office to MySQL

by Ruben Safir

One of the most important tools in office automation is the joining of common spreadsheet and word processing with powerful database capabilities. The MS Access/MS Office integration has been responsible for billions of dollars worth of product development and value-added retailing over the last few years. It's an area of revenue which has not been adequately exploited using free tools.

This article is the first of a run in the NYLXS Journal which will hopefully help you fill in the gap with Free Software between you and your clients needs in regards to office automation. We will look first at the simple connectivity between Open Office and MySQL, and then show how we can view our databases with Office tools.

Open Office's integration with any database is not as simple as it should be. This is largely due to political reasons. Within the ranks of the Open Office Database development Team are people who are working to put this together but disagree on how to proceed. Much of the database team seems more interested in fighting about the pros and cons of Postgres versus MySQL then integrating them, and are now mulling over the possibility of adding another database tool to the package. They are also stuck on making sure that whatever they package they provide, that it works on the Windows platform. Over all, their statergy is badly lacking common sense from the prespective of the Free Software user, and the result of this is that users of Open Office are suffering under a number of bad programming and design problems.

Another impedance to Database Connectivity is that Open Office inherits much of Sun's Star Office bloodline. Star Office was developed with a 'Java First' design. The Open Office developers have tried very hard to remove as much of the Java Dependency from Open Office as possible. But the first choice for the system's database connectivity is still JDBC. In fact, the Open Office Team has even considered using a Java based database for it's packaged database design. Need I say more about this problem.

On the positive side of the ledger, nearly every free software distribution comes with a rigorous database server built right into the distribution. Either MySQL and Postgres or both are usually installed with the core of the distribution. MySQL is a GPL'ed program which is a rapidly maturing as a product. And is light years faster and better than MS Access, Foxpro or any of the other commonly used desktop database solutions for office automation. MySQL is designed with true client, server capability. It has a simple learning curve, huge support, multi-threaded capability, and has a complete ANSI 92 SQL set. MySQL is a very good choice for your offices database needs. It's very scalable, so as your office product grows in usage and deployment, a system administrator can add features and optimizations to MySQL to keep it humming with minimal trouble. It can be used to the largest scales. It now even comes with Commits and Roll Backs, previously one of it's largest feature holes. And depending on how you set it up, it will create roll back logs, and perform replication.

In addition to MySQL, Postgres is also distributed very widely on with the major distributions. Postgres has many of the advanced features which one normally would expect to see with Oracle or Sybase. For those who need row level locking and such, Postgres is a fine option. Both databases give a level of multi-user data protection unseen on the MS Windows platform.

This paper will focus on the MySQL implementation. Much of it can also apply to Postgres.

The steps to giving MySQL connectivity to your Open Office environment are:

1. Install Open Office
2. Install MySQL
3. Install IODBC
4. Install MYODBC
5. Write a /etc/odbc.ini file
6. Test

Because of the limitations of hooks for the Open Office Suite, ODBC middleware needs to be used. ODBC has two parts. The server level, and the ODBC client. For this reason we need the generic ODBC libraries, iODBC, and the database specific database client, MyODBC.

PREPARATION

In the fashion of Star Wars, there is also a prequel to these steps. One should know their distribution or working environment. While installing Open Office and MySQL by hand by tarballs is possible, one would be better off not installing Open Office from source because of it's complex dependencies. It has completely functional RPM's available. It's a complex application.

In addition, it's a good idea to know where things are stored on your system,especially system libraries and such. Here are a few things you might want to look at before preparing to make this work. Keep in mind that if you have a relatively new distribution, you probably already have all your ducks in order. But take a look anyway.

First, look to see if you have ODBC libraries already installed on your box. Do a 'locate' command at the prompt. IODBC is probably installed on your system, and I will not be discussing it's installation.

You'll should see libodc or libiodbc already on your system under the /usr/lib directory. (see Illustration 1). If not, you need to download an ODBC package like iodbc from http://www.iodbc.org/software.htm Or better, install it from your distribution disk or from your distribution's download site.

In addition, now might be the time to look at your MySQL libraries and configurations (illustration 2).

Illustration 1. locate lib|grep mysql.

Finally, we can download the MySQL ODBC client from http://www.mysql.com The client is called MyODBC-2.50.39.tar.gz and as of this writing is available from

http://www.mysql.com/downloads/download.php?file=Downloads/MyODBC/MyODBC-2.50.39.tar.gz

Drop the file MyODBC-2.50.39.tar.gz into your home directory and untar it as a regular user (not root).

ruben$: tar -xzvf MyODBC-2.50.39.tar.gz

This should create a local sub-directory ./MyODBC-2.50.39. Move into the sub-directory

cd ../MyODBC-2.50.39.tar.gz

Now look at what you got:

Illustration 2. The untarred MyODBC directory.

Read the INSTALL file, where the instructions are. Installation from source is fairly straight forward. You'll need to create an ODBC.INI file. Create a system wide one under /etc. We will be creating it later after installing MyOBDC.

Change directory to the newly created subdirectory created by your tarballs.

cd ./MyODBC-2.50.39

./configure --prefix=/usr with-iodbc=/usr

make

make install

If any of the needed libraries are not found by default, then you can specify their location on your system with the proper configure options. This is why we noted the location of the mysql libraries and the ODBC libraries before starting our compile. See the INSTALL file for more specific information.

Also try:

./configure --help

to display your options. This will output a huge number of options, but you should only need to configure those options where the default autoconf can't find the libraries on your system.

--with-iodbc=DIR

--with-iodbc-includes=INCDIR

--with-iodbc-libs=LIBDIR

--with-mysql-dirs=DIR

--with-mysql-includes=DIR

are the only ones which you normally need to be concerned with.

After we do a make, we now need to do a make install as the root user.

-ruben$: su

-ruben$: passwd:

-ruben$: make install

Finally we need to create an ODBC.ini file under the /etc. Open up the mysql client and log into your mysql database. Run 'show databases' on the client's command line. The NYLXS server has the following databases:

DATABASE

addmysite

apache

calendar

cart

ezpages

fairuse

jobs

lccb

lost+found

messages

mike_cart

mysql

newschool

nylxs

studentgrades

students

test

test_www

wireless

As root, use vi to create your /etc/odbc.ini file. It should look something like this:

[NYLXS]
DSN = NYLXS Driver = /usr/local/lib/libmyodbc-2.50.39.so
Database = nylxs
Server = www2.mrbrklyn.com
Port = 3306

[EZPAGES]
DSN = EZPAGES
Driver = /usr/local/lib/libmyodbc-2.50.39.so
Database = ezpages
Server = www2.mrbrklyn.com
Port = 3306

The strings in the square brackets can be anything as long as they are unique, The DSN can also be anything unique.

If everything is well, then we are now ready to register the MySQL ODBC driver with Open Office.

Click on your Open Office icon on your desktop. And for the fun of it, create a new spreadsheet by clicking on File->New->Spreadsheet.

Illustration 3. Adding the ODBC Data Connection.

Now we can add the ODBC Driver connection to Open Office.

Illustration 4. Tools-->Datasource

Select tools->datasource (see illustration 4) and open the data source GUI (see illustration 5).

Click the 'New Data Source' box on the upper right to get the data source registration GUI. Give your data source a name. This can be any string. Select the data type as ODBC. Click on the three dots and choose an ODBC data source from your ODBC.ini file. Now Click on the 'ODBC' tab on the top. Enter your user name and check the 'password required' check box. Now choose to click on either 'Tables', 'Queries' or 'Links'. An alert will pop-up asking if you want to save the settings. Say yes and continue. To keep this simple, we will use a table (and clicked on the 'Table' tab).

We are now prompted for our password. Enter your mysql password and check the box asking to cache the password until the end of the session. You should now see all your available tables for this database.

Illustration 5. Datasource Administration.

These tables are the ones you can make available to Open Office Applications. Now click OK and you are set to put live data from your database into your Open Office Applications. One word of warning: ODBC is fat and slow. You might want to lower the priority of Open Office when using it with large databases.

Adding Database information automatically into a Document

Let's now see how we can put data into an application. Open a spreadsheet, or use the one we have opened. Click on Data->Datapilot:

Illustration 6. Data Pilot.

Click on Start. Select 'Data Source registered in Open Office', which should be the one we just created. Click OK and choose the data source:

Illustration 7. Select Data Source.

Click 'OK' again after choosing a table listed under 'Data source'. Select 'sheet' to enter our data into a new sheet within our spreadsheet. If this is a large table, this might take a few seconds. Drag and Drop your database columns. And click OK. Now your spreadsheet is talking to the database. Wait a moment while the datapilot does it's work.

Illustration 8.

Voila. You have your database connected to your spreadsheet.!

Note that the Open Office product is referred to by the developers as OpenOffice.org