Connect OpenOffice.org to MySQL

One of the good things about OpenOffice.org is its ability to use different database engines. Just give it the right driver, and OpenOffice.org can connect to virtually any database system, including MySQL. However, deciding what database driver to use and configuring a connection between MySQL and OpenOffice.org can be a bit tricky. Let’s walk through the process.

First of all, you have to choose what driver (also called a connector) to use. MySQL offers two connectors that allow you to move data between OpenOffice.org and MySQL: ODBC Driver for MySQL (Connector/ODBC) and JDBC Driver for MySQL (Connector/J). The latter is easier to install and configure, and can be used on Linux, Windows, and Mac OS X (it even works with NeoOffice) in the exactly same way. However, as the name suggests, Connector/J requires that Java be installed on your machine. While the question of Java’s openness is no longer an issue, you still have to make sure that the Java Runtime Environment is installed and added to OpenOffice.org. Another, more serious, problem with Connector/J is its somewhat limited functionality when used with the OpenOffice.org Base database. For example, using Base’s GUI, you can create a primary key, but you can’t assign the auto_increment property to it. To work around this limitation, you can build the database by executing the appropriate SQL commands using the built-in SQL editor (Tools -> SQL). For example: `ID` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY. Alternatively, you can use third-party tools like phpMyAdmin or HeidiSQL.

Connector/ODBC does not have these limitations, but installing and configuring it is a more complicated process. Also, on Linux, the connector works best when the MySQL database is installed on the same machine. This is because the driver expects to find the mysql.sock file in a specific directory, and if it doesn’t find it there (which happens with a remote database) it fails to establish a connection. You can, of course, make the driver connect to a remote database, but since this requires some wizardry, it’s often not worth it — especially if you plan to deploy the driver on multiple machines.

As a rule of thumb, try Connector/J first if you are running Linux or Mac OS X and you are comfortable using SQL or can edit the database using a third-party tool. If you are using a Windows or Linux machine with MySQL installed on it, then you can opt for Connector/ODBC.

Using Connector/J

To get started with the JDBC Driver for MySQL (Connector/J), download it from MySQL’s Web site, unpack the downloaded file, and move the resulting mysql-connector-java-x.x.x-bin.jar file into the desired location (e.g. your home directory). In OpenOffice.org, choose Tools -> Options -> Java. Make sure that the appropriate Java Runtime Environment is selected and click the Class Path button. Next, click the Add Archive button and select mysql-connector-java-x.x.x-bin.jar. Click OK to save the settings and close the window, then restart OpenOffice.org.

Now you are ready to connect OpenOffice.org to the MySQL database. In OpenOffice.org, choose New -> Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and click Next. Select the Connect using JDBC (Java Database Connectivity) option and click Next. Specify the name of the database and the server address using the appropriate fields. To make sure that the MySQL JDBC driver works properly, click the Test Class button. Once the driver has been loaded successfully, click Next. Enter the database user name into the User name field. If the entered user name has a password, tick the Password required check box. To see whether everything works as it’s supposed to, click the Test Connection button. Click the Next button, select the Yes, register the database for me option, and click Finish. Give the database a name and save it.

Using Connector/ODBC on Ubuntu

If Connector/J works for you, you’re in business. If not, try Connector/ODBC. The process for getting it working is slightly different depending on whether you’re running Linux or Windows. We’ll try Linux first — specifically, Ubuntu.

First of all, install the required packages. Launch Synaptic and mark the following packages for installation: unixodbc, libmyodbc, and unixodbc-bin. The latter package contains the GUI tools for setting up an ODBC connection to a MySQL database, which you can use instead of fiddling with configuration files.

Once the packages have been installed, launch the ODBC configuration tool by executing the sudo ODBCConfig command in a terminal window. Click the System DNS tab and click the Add button. Click Add again to create a new ODBC driver. This opens the Driver Properties window. Give the new driver a name and description using the appropriate fields. Assuming you’re running the 32-bit version of Ubuntu, specify the path to the libmyodbc.so file in the Driver field (/usr/lib/odbc/libmyodbc.so) and enter the path to the libodbcmyS.so file in the Setup field (/usr/lib/odbc/libodbcmyS.so). The final result should look like the figure. Click the Save and Exit button to save the settings, then click OK to open the Data Source Properties windows. Give the new data source a name, enter its description, then specify the MySQL server address, the database name, and port. Click OK, and you are ready to go.

Connecting OpenOffice.org to the MySQL database using the created connection is a matter of choosing the correct options in the Database Wizard. In OpenOffice.org, choose New -> Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and click Next. Select the Connect using ODBC (Open Database Connectivity) option and click Next. Point the Wizard to the created ODBC connection using the Browse button. Click Next and enter the database user name into the User name field. If the entered user name has a password, tick the Password required check box. To see whether everything works as it’s supposed to, click the Test Connection button. Click the Next button, select Yes, register the database for me option, and click Finish. Give the database a name and save it.

Using Connector/ODBC on Windows

If you’re running OOo on Windows, download the ODBC Driver for MySQL (Connector/ODBC), unpack the downloaded file, and run the setup program. Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC). Click on the User DSN tab and click the Add button. From the list of available drivers, select the MySQL ODBC Driver, and click OK. This opens the Connector/ODBC configuration window. In the Login section, enter the required information into the appropriate fields. Click Test to see whether the created connection works properly, then click OK to save the settings and close the window. In OpenOffice.org, create a new database as described previously.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, German, and Danish computer magazines.