Connect MySQL and OpenOffice BASE- A Tutorial for Connecting OpenOffice's BASE to a MySQL Server

Article provides a quick tutorial on how to connect MySQL and BASE from OpenOffice.org on a MS Windows computer with the MySQL Connector/J for Java Database Connectivity.

BASE is a database management system which is distributed with OpenOffice.org version 2.0 or newer that integrates with other OpenOffice.org applications. BASE offers a full HSQL relational database engine for single users plus built-in support for flat dBase files.

BASE also has native support for many multi-user database engines like MySQL, Adabas D, MS Access and PostgreSQL.

Using JDBC on Windows

BASE can use the MySQL Connector/J driver which is a native Java driver that converts JDBC (Java Database Connectivity) calls for use by the MySQL protocols. Using JDBC drivers in Windows allows quick connection from BASE running on Windows to a MySQL database server with very little effort.

The MySQL server can be local, on the network, or on the web somewhere. As long as the server accepts remote connections, BASE can connect and manipulate the database.

Installing JDBC on Windows

The first step is to download and install the MySQL Connector/J driver from MySQL's website. Once downloaded, unzip the Java archive to a convenient location on the local computer where BASE is used, then open OpenOffice.org tell it how to find the MySQL Connector/J driver .

1. Start OpenOffice.org
2. Click Tools menu then Options.
3. Choose OpenOffice.org.
4. Select Java in the list to the left.
5. Click on Class Path... then Add Archive...
6. Navigate to where the mysql-connector file is located (the file you extracted).
7. Click on the mysql-connector-x.x.x-java-bin.jar file once then click Open.

The mysql-connector-x.x.x-java-bin.jar file should now appear in the list of Assigned folders and Archives. Click OK to close the dialog and OpenOffice.org will state that the software must restart for the changes to take effect. Restart OpenOffice.org completely, including the quick-start located in the taskbar.

Connecting Base and MySQL

With the JDBC class installed it takes two minutes to connect BASE and MySQL. Needed are the database name, the username and password with database access rights, and the host address. With that data in hand, you are ready make the connection.

1. Load OpenOffice.org BASE.
2. Click Connect to an Existing Database.
3. Choose MySQL from the drop down.
4. Click Next to set up the connection to a MySQL Database.
5. Choose Connect using JDBC (Java Database Connectivity) then Next.
6. Enter the database name and the host server. (This could be localhost, an IP Address, or a full domain name.)
7. Enter the MySQL server port – Default is 3306
8. Enter the username to access the database and check Password Required; assuming the database requires a password.
9. Click Test Connection, enter the password, and check Remember password until the end of session.
10. Click OK. If everything worked a box opens saying The connection was established successfully.
11. Click OK then Next.

The next screen asks if OpenOffice.org should register the database or not. Registering the database means it is displayed in the View → Data Sources window. Databases must be registered to insert fields in documents, or to use with mail merge.

If this is not the purpose choose No, do not register the database which keeps the information in the created database file.

The last step is to decide to open the database for editing, to create tables using the table wizard, or both. Selecting Open the database for editing displays the database file for editing the structure or content. Create tables using the table wizard will load the Table Wizard to create new tables.

Click finish to save the settings.

Trouble Shooting:

If the database is not on the localhost and refuses a connection in step 10 of Connecting Base and MySQL the likely two reasons are firewalls and MySQL user privileges. Ensure that firewall port forward settings configured, and that the MySQL user has privileges from your IP address.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---