Difference between revisions of "Connect MySQL and Base"

Revision as of 10:18, 8 August 2009

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

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

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

Operation

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 in the field Name of the 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 Datasource URL would be: 192.168.1.1, or 96.115.86.166, or something like that.

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.

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. 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).