Connect MySQL and Base

This is a tutorial on connecting to a MySQL database server with OpenOffice.org Base using the JDBC connector.

Since this tutorial was written, a "native" connector for Open Office has been created. (Connector has exisited since at least Jan-11) Some would say that a "direct", "native" connection was more likely to run well that one that introduces "extra" elements to the situation. There is a "getting started" tutorial for the native connector.

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.

Using JDBC on Windows

Installation

First, download and unzip the java archive. Obtain the mysql-connector-J from the MySQL website. Follow the instructions on the MySQL websiteto find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select Tools - Options - OpenOffice.org - Java. Click on the button Class Path...; then click on the button Add Archive...; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button Open. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of Assigned folders and Archives. Click on OK to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org completely, that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

Operation

See Operation below under Linux.

Using ODBC on Windows

Obtain mysql-connector-odbc from the MySQL website and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: localhost (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

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 ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

Using JDBC on Linux

Installation

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 Class Path..., then choose 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

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

su root

yum install mysql-server mysql

cd /sbin

./chkconfig mysqld on

/etc/init.d/mysqld start

Operation

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

One method to do this requires using the mysql command line client;

mysqladmin -u root password NEWPASSWORD

mysql -u root -p

CREATE DATABASE newdatabasename;

CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';

GRANT ALL ON *.* TO 'newusername'@'localhost';

Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

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) The next step varies depending upon the exact version of Apache OpenOffice installed.

If the fields Name of the database and Server URL are present, perform the following;

In the Name of the database field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.

In the Server URL 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. You can adjust the connection port or leave default port 3306 in the field Port number. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

If only the field Data Source URL is present perform the following;

In the Data Source URL field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: localhost (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

For the field MySQL JDBC driver class: enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is com.mysql.jdbc.Driver for version 5.1.6. Click on Test Class just to make sure that you have correctly set up the access to the 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 Connection test: The connection was established successfully. Click on Next. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

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.

Debian/Ubuntu

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain mysql-connector-odbc from the MySQL website.

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and/etc/odbc.ini should have (or create ~/.odbc.ini so that you can modify it as normal user):

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

In above configuration we assumed that database named "test" exists on local server. Then, Establishing connection by Creating New Base file:

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 ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

Using JDBC on OS X

Installation

1) Download MySQL Connector/J from MySQL Downloads

Choose Platform independent Choose Zip File or Tar File (either will do)

2) Double click file to extract

3) Make a directory under your user name (I use a combination of connectorj and the version number.)

4) Copy the contents of the zip file into this new directory.

OpenOffice.org settings

Set up OpenOffice.org for use via JDBC (Warning! This will require a restart of OpenOffice.org!):

OpenOffice.org > Perferences > Java

Click "CLASSPATH"

Click "Add Archive"

Browse to the jar file from the zip/tar file (mysql-connector-java-x.x.x-bin.jar where x.x.x is the version number).

Click OK. OpenOffice.org will warn a restart of the application is required.

Setting up a database for use

These instructions assume the use of an existing database.

Start OpenOffice.org and choose a file type of "Database."

Click the radio button "Connect to an existing database" with the drop down set to JDBC.

Click Next.

In the URL following jdbc: fill in with the proper settings in the format mysql://servername:port/databasename (if default port, you can ignore the port portion.)

Fill in the Driver Class with com.mysql.jdbc.Driver and click "Test Class." You should see a message "The JDBC driver was loaded successfully."

Click Next.

Enter the user name to connect to the DB with. If a password is required, check the "Password required" box.

Click "Test Connection" and it should prompt with the user name and a password prompt. You should see a pop-up saying "The connection was established successfully."

Click Next.

Best to leave the defaults on this next screen set.

Click Finish.

You will be prompted on where to put the database configurations for use with OpenOffice.org. (I usually make a small directory in my Documents folder.)

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. NOTE: In Ubuntu my.cnf is located in /etc/mysql

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
• MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).