This blog post is organized into two major sections and several sub-sections. The major sections focus on the tasks to be performed at the MySQL server and the *Office client while the sub-sections talk about the steps to be performed in detail.

To show the examples in this exercise, we will be creating a new MySQL database user with user ID SOUSER. This new user will be granted read-only access to couple of tables in a MySQL database called ISVe. The database can be accessed from any host in the network. ben10.sfbay is the hostname of the MySQL server.

Tasks to be Performed at the MySQL Server

This section is intended only for the MySQL Server Administrators. If you are an end-user, skip ahead to Tasks to be Performed at the Client side.

Create a new MySQL user and grant required privileges.

eg.,

% mysql -u root -p
Enter password: *****
Server version: 5.1.25-rc-standard Source distribution
..

mysql> CREATE USER SOUSER IDENTIFIED BY 'SOUSER';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE ISVe;
Database changed

mysql> show tables;
+--------------------------+
| Tables_in_ISVe           |
+--------------------------+
| CustomReport             | 
| CustomSQL                | 
| ISVeOldProjects          | 
| ISVeOrg                  | 
| ISVeProject              | 
| ISVeProjectExecution     | 
| ISVeProjectGoalAlignment | 
| ISVeProjectMiscInfo      | 
| ISVeProjectScoping       | 
| ISVeProjectStatus        | 
| ISVeProjects             | 
| ISVeProjectsVW           | 
| ISVeSearchLog            | 
| LastRefreshed            | 
+--------------------------+
14 rows in set (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeOldProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
| GRANT SELECT ON `ISVe`.`ISVeOldProjects` TO 'SOUSER'@'%'                                              | 
| GRANT SELECT ON `ISVe`.`ISVeProjects` TO 'SOUSER'@'%'                                                 | 
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

Check the database connectivity and the accessibility from a remote location.

% mysql -h ben10.sfbay -D ISVe -u SOUSER -pSOUSER
Server version: 5.1.25-rc-standard Source distribution

mysql> show tables;
+-----------------+
| Tables_in_ISVe  |
+-----------------+
| ISVeOldProjects |
| ISVeProjects    |
+-----------------+
2 rows in set (0.03 sec)

mysql> select count(*) from ISVeOldProjects;
+----------+
| count(*) |
+----------+
|     2880 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from ISVeProjects;
+----------+
| count(*) |
+----------+
|     4967 |
+----------+
1 row in set (0.33 sec)

mysql> delete from ISVeOldProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeOldProjects'

mysql> delete from ISVeProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeProjects'

mysql> quit
Bye

---

Tasks to be Performed at the Client side (End-User's Workstation)

---

StarOffice and OpenOffice suite of applications can access the MySQL Server using JDBC or native drivers.

MySQL Connector/J is a platform independent JDBC Type 4 driver that is developed specifically to connect to a MySQL database. Using Connector/J, it is possible to connect to almost any version of MySQL Server from any version of StarOffice or OpenOffice.org

Sun|MySQL recently developed a native MySQL driver to facilitate connecting from StarOffice / OpenOffice.org suite of applications to a MySQL database. The new native driver is called MySQL Connector/OpenOffice.org. However the current version of the MySQL Connector for OO.o is compatible only with OpenOffice 3.1, StarOffice 9.1 or newer and it can connect only to MySQL Server 5.1 or later versions. This native connector is supposed to be faster in comparison with the Java connector.

We will explore both MySQL connectors in this section.

Note:

As an end user, you need not be concerned about the internal workings of these MySQL connectors. You just need to worry about installing and configuring the drivers so the *Office applications can connect to the MySQL database in a seamless fashion.

I. Connector/J approach

1. Installation steps for MySQL Connector/J
   
   Using the following navigation, find the location of the JRE that is being used by StarOffice / OpenOffice.org
   
   
   
   
   • Launch StarOffice / OpenOffice.org
     
   • Tools Menu -> Options
     
   • In the 'Options' window, StarOffice / OpenOffice.org -> Java
     
   
   
   Here is a sample screen capture from a Mac running StarOffice 9.
   
   
   
   
   
   In the above example, /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home is the location of the JRE. Here onwards, this location will be referred as JRE_HOME.
   
   
   
   
   1. Download the connector from the following web page:
              http://dev.mysql.com/downloads/connector/j/
      
      As of this writing, 5.1.10 is the current version for Connector/J
      
      
   2. Extract the driver and the rest of the files from the compressed [downloaded] archive
      
      eg.,
      

      
      % gunzip -c mysql-connector-java-5.1.10.tar.gz | tar -xvf -
      
      

      
      
      
   3. Locate the jar file that contains the driver --- mysql-connector-java-5.1.10-bin.jar, and copy it into the <JRE_HOME>/lib/ext directory with 'root' privileges.
      
      eg.,
      

      
      % sudo cp mysql-connector-java-5.1.10-bin.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext
      
      % ls -l /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/*connector*jar
      /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/mysql-connector-java-5.1.10-bin.jar
      
      

      
      
      
   4. Restart StarOffice / OpenOffice.org
      
   
   
   This concludes the installation of MySQL Connector/J.
   
   
   2. Configuration steps for Connector/J
   
   
   
   
   1. Launch StarOffice / OpenOffice.org
      
      
   2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.
      
      
   3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.
      
      
      
      
      
      Click on "Next >>" button
      
      
   4. In the next screen, select JDBC by clicking on "Connect using JDBC (Java Database Connectivity)" radio button
      
      
      
      
      
      Click on "Next >>" button
      
      
   5. In "Set up connection to a MySQL database using JDBC" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections.
      
      MySQL JDBC driver class text field will be automatically filled with the string com.mysql.jdbc.Driver. Leave that string intact, and click on "Test Class" button to make sure that the relevant class can be loaded with no issues. Unless the driver class is loaded successfully, you will not be able to connect to the MySQL database. In case of unsuccessful class loading, double check the installation steps for MySQL Connector/J.
      
      
      
      
      
      Click on "Next >>" button
      
      Note:
      
      In the above screenshot, notice that the "Name of the database" was filled with ISVe?zeroDateTimeBehavior=convertToNull (It is not completely visible in the above screen capture, but you just have to believe me). In this example, ISVe is the database name and zeroDateTimeBehavior is the configuration property which was set to a value of convertToNull. Without this configuration property, Connector/J throws an exception when it encounters date values such as 0000-00-00. In such cases, the error message will be something similar to java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date.
      
      Configuration properties define how Connector/J will make a connection to a MySQL server. The list of Connector/J configuration properties are documented in the following web page:
      
              http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
      
      If you have more than one configuration property, you can define all of those properties in the "Name of the database" field. The syntax would be:
          <MySQL_DB_Name>?<Config_Property1=Value>&<Config_Property2=Value>&..&<Config_Propertyn=Value>
      
      
   6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.
      
      
      
      
      
      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.
      
      Click on "Next >>" button
      
      
   7. In the final screen, simply accept the default values and click on 'Finish' button.
      
      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.
      
      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.
      
      
      
      
      
      
   8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.
      
      
      
      
      
      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.
      
      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.
      
   
   

---

II Connector/OpenOffice.org approach

MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice suite of applications. Even though it appears to be a native driver, MySQL Connector/OpenOffice.org has no implementation for the MySQL Client Server protocol. It is in reality a proxy on the top of MySQL Connector for C++ aka MySQL Connector/C++.

Unlike MySQL Connector/J, Connector/OpenOffice.org has no dependency on JRE, and it can easily be installed using the OpenOffice.org Extension Manager. Due to the underlying native code, Connector/OpenOffice.org may outperform Connector/J in performance.

1. Installation steps for MySQL Connector/OpenOffice.org

Before installing the connector, make sure that you have OpenOffice.org 3.1 [or later] -OR- StarOffice 9.1 [or later] suite installed, and the version of the MySQL server on which the database is hosted is at least 5.1. If any of these requirements are not met, skip this entire section and check the I. Connector/J approach for the instructions that may work with your current versions of StarOffice / OpenOffice and MySQL server.

1. Download the connector for your platform from the following location:
           http://extensions.services.openoffice.org/project/mysql_connector
   
   
2. Launch StarOffice / OpenOffice.org
   
   
3. Bring up the "Extension Manager" by clicking on Tools Menu -> Extension Manager ...
   
   
4. Click on "Add" button, then locate the OpenOffice connector that you downloaded in step #1 (see two steps above). Click on "Open" button. The name of the connector will be something similar to mysql-connector-ooo-....oxt.
   
   
5. Choose appropriate response to the question "For whom do you want to install the extension?". In this example, I chose the option "Only for me".
   
   
6. Read the "Extension Software License Agreement" and accept the agreement to install the Connector/OpenOffice.org as an extension to StarOffice / OpenOffice.org
   
   
   
   
   
   
7. Restart StarOffice / OpenOffice.org to complete the installation.
   

2. Configuration steps for MySQL Connector/OpenOffice.org

1. Launch StarOffice / OpenOffice.org
   
   
2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.
   
   
3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.
   
   
   
   
   
   Click on "Next >>" button
   
   
4. In the next screen, select "Connect native" radio button
   
   
   
   
   
   Click on "Next >>" button
   
   
5. In "Set up connection to a MySQL database" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections. If the MySQL Server is running on the same machine as that of the StarOffice / OpenOffice.org application, you can provide the location of the socket under "Socket" field. If not, leave it blank.
   
   
   
   
   
   Click on "Next >>" button
   
   
6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.
   
   Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.
   
   
   
   
   
   Click on "Next >>" button
   
   
7. In the final screen, simply accept the default values and click on 'Finish' button.
   
   "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.
   
   When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.
   
   
   
   
   
   
8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.
   
   
   
   
   
   Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.
   
   You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.
   

That is all there is to it in installing and configuring the MySQL connectors for *Office suite of applications. Now enjoy the flexibility of fetching the data from your favorite office productivity software.

(Original blog post is at the following location:
http://blogs.sun.com/mandalika/entry/setting_up_staroffice_openoffice_org)