This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature

Connect OpenOffice.org to MySQL

By Dmitri Popov on February 23, 2007 (8:00:00 AM)

Share    Print    Comments   

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.

Figure 1
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.

Figure 2
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.

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

Share    Print    Comments   

Comments

on Connect OpenOffice.org to MySQL

Note: Comments are owned by the poster. We are not responsible for their content.

Cool

Posted by: Anonymous Coward on February 24, 2007 03:50 PM
Oh, pretty cool!<nobr> <wbr></nobr>:)
Makes it a lot easier to handle, administrate and use the database than using phpMyAdmin or something.

#

Doesn't work in some cases

Posted by: Anonymous Coward on February 24, 2007 09:03 PM
Been there, done that. People have to make sure the connection with MySQL has to be done in default ISO-8859-1 encoding. With any other encoding, be prepared for OpenOffice retrieving garbled junk symbols. -- Abel Cheung

#

Connect OpenOffice.org to MySQL

Posted by: alle on July 10, 2007 03:21 PM
I use Ubuntu Feisty. I Installed Apache, Mysql, PHPADMIN and everything works properly.
I Found your help some time ago, thanks a lot, I used it to work with my OpenOffice Base 2.1 togheter with Base, but I had some problem because, You know from my english, I'm italian.
The problem was this:
Base works very well with text, date and decimal numbers, when I read from the forms, but when I go to change the Decimal number by type in a forms from base the numbers disappeared in Base and in Mysql. I used the format Decimal 0.00 and not 0,00 like in Italy....

I found somewhere that's a problem of mysql-connector (UBUNTU?), and I tried to compile a new unixODBC and mysql-connector-odbc-3.51.16r494 (source) but the mission is not so easy and the problem is grow-up.
I'm looking everywhere some help to my problem but just your address speak about this problem, I Think you are a great Expert, and so may you give me an help.
Thanks a lot . alex

#

Thanks

Posted by: Anonymous [ip: 82.171.123.180] on August 02, 2007 10:54 PM
Thanks for this guide, it really helped me on Kubuntu

#

Connect OpenOffice.org to MySQL

Posted by: Anonymous [ip: 203.200.178.52] on November 08, 2007 12:01 PM
How the JDBC ODBC coneection are written in a program in Linux configuration?

#

Connect OpenOffice.org to MySQL

Posted by: Anonymous [ip: 220.224.20.249] on December 04, 2007 10:34 AM
Thanks. It works fine with JDBC driver.

#

Connection of flash Action Script to ODBC with out using server side scripting.

Posted by: Anonymous [ip: 219.64.124.12] on December 21, 2007 09:12 AM
How can i establish a direct connection between flash action script to ODBC without using a server side scripting. or it can be achieved by some other third party driver. If yes please tell. send to this mail id krisha1000@mail.com
Thanks!

#

Connect OpenOffice.org to MySQL

Posted by: Anonymous [ip: 88.103.67.221] on January 27, 2008 06:19 PM
I wonder how to, pls, do this? "In OpenOffice.org, create a new database as described previously"

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya