MYSQL & ODBC Connections, Revisited

So we thought we had our databsae problem licked. (See MYSQL & ODBC Connections)

After setting the primary key we tested the database and were able to connect, access the tables and edit the data. Perfect.
I installed the connector on each of the six workstations. Four of these stations were read only and worked properly. Two of the workstations required write access and this is where we came into trouble again. Everything in the Grant tables and privileges was set correctly. We could edit through the web interface and at the command line. After many hours of frustration and Googling we decided that something had to be wrong with the ODBC connector. Our solution came in the form of the JDBC connector.

If you are having problems with MYSQL and Openoffice Base where you cannot edit the tables, maybe this will help.

• Download the latest version of JDBC. As of this date the latest version is 5.0. We used the Source and Binaries (zip) file.
• Unzip the jar file onto the C: drive. If you are going to be using a program other than Openoffice you will have to set the CLASSPATH in the Windows environment variables. To avoid compatability issues, I chose to install Openoffice with JRE included. I highly recommend going this route and the next step assumes that you did also.
• Go to one of the Openoffice programs such as calc or writer. Select Tools -> Options. When the Options window opens click on Java. After a few seconds you will see all of the JRE’s that are installed on your system.
• Make sure that the checkbox for Use a Java Run Time Environment is checked.
• Click on the button for Class Path. When the Class Path window opens click on the Add Archive button. Browse to your jar file on C:, click on it and hit Open.

With that accomplished you can exit out of whatever Openoffice program you are in and open up Base. This will get you to the Database Wizard which will walk you through connecting to the database. Here we go:

• Click on Connect to an Existing Database. From the drop down menu select MYSQL. Click Next.
• Choose Connect Using JDBC. Click Next.
• Fill in the information for Database Name and Server URL. Click Test Class. If all goes well a box will pop-up telling you that the JDBC driver was loaded successfully. Click OK and then Next.
• The next screen will ask for the User Name for the databse. Check the Password Required box if a password is required. Click Test Connection. Again, if all goes well, a box will open up telling you the connection was successful. Click OK and Finish. You will now be asked to save the database. Once it is saved Base will open your database.
• Click on Tables. If a password is required a box will open asking you to enter it. In the Tables window you will see the available databse tables. Double click on the name and it will open.

More on using the Openoffice Base when I figure that out.