Connect MySQL and Base

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

Using JDBC on Linux

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs Java Runtime Environment to be able to use JDBC. Verify that you have JRE on your OOo by going to Tools - Options - OpenOffice.org General - Java.

Next step is to download the mysql-connector-J which you can download from the MySQL website. MySQL Connector/J adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu Tools - Options - OpenOffice.org General Java. Click on the button Add Archive and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on Open, and the file should then appear in the list of Assigned folders and Archives. You can then click on OK and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like

• libmysql-java for debian and ubuntu

When you restart OpenOffice.org, you should now be able to create a new database document that can connect to your existing mysql database, i.e. you need to have created your database first on your mysql server, for example with the CREATE DATABASE command from the mysql command line client or from within PHPMyAdmin if you are using that graphical user interface to administer your mysql server.

Now do the following : 1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using JDBC, and then click on Next.

4) In the following dialog, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling. In the address field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. Click on Test Class just to make sure that you have correctly set up the access to the JDBC connector as detailed above. If the connector has been set up correctly, you will get a message that says The JDBC driver was loaded correctly. Now click on Next.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on Test Connection, you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message The connection was established successfully. Click on Next.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. Yes, register the database for me and Open the database for editing. Click on Finish.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on Save, the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

Using ODBC on Linux

OpenOffice.org also support ODBC connectivity to MySQL, you will need Unix-ODBC in order to make a connection through it.

Recommendations settings

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

MySQL settings

• Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment skip-networking

OpenOffice.org settings

• OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
• Declare the mysql-connector-j.bin at the classpath at Tools - Options - OpenOFfice.org General - Java