I decided it would be fun to write down a few simple tips on how to use MySQL in Office applications. Yup, you heard me: using MySQL as "Desktop Database", quite like MS Access is being used on many, many windows desktops.
Of course, I do not want to suggest that MySQL "...is a Desktop Database", because it's not. Rather, MySQL is a general-purpose database, so there's no reason at all to dismiss it for typical "Desktop Database" purposes.
Even though MySQL is a server product, MySQL resembles a typical Desktop Database in a number of ways. For example, MySQL is not extravagantly resource-hungry, and does not require much maintenance - (not for this purpose anyway). So, there's not much against using MySQL here.
In fact, it's probably a good idea to back your Desktop Database applications with MySQL. A lot of the typical problems (resource contention due to locking, file corruption due to crashing application programs, scale limits, and much much more) that you encounter as these applications grow larger, are either not-existent or easily solved using a server solution such as MySQL.
So - let's dive in, and see what it takes to use MySQL in the daily office. Oh yeah - I once did a write-up on connecting MS Access and MySQL. This time, we won't be using MS Office at all. We'll use OpenOffice.org instead.
All products described here are available for most popular architectures and operating system. Both MS Windows and Linux users should be able to get this to work.
The client computer should be able to directly access a local or remote MySQL Server instance. You need to know the hostname (or IP address) of the MySQL Server host. If the MySQL Server is configured to use a non-default port, you must know the portnumber too. You also need the name of the database you want to access. Finally, you also need to know the user name and password of an existing user that has privileges to acces the database.
I'll be using the sakila sample database in the following examples. It's not absolutely necessary to install it if you already have another database to play with. If you want it, you can download it here.
Before you start, a few things need to be in place on the client computer. It may already have these items, or you might have to download and install them:
- A Java runtime environment (jre) or Java Development Kit (JDK, if you also want to develop Java programs). In most cases, your operating system ships with some pre-installed implementation. However, I found out that a pre-installed implementation might not be exactly what you need. Personally, I'm quite happy with Sun's own implementation.
- The OpenOffice.org suite. You can download it here or look for a package tailored for your operating system yourself.
- The MySQL JDBC driver, Connector/J. You can download it here.
As for the MySQL JDBC driver: there's not much to install. Just unpack the download in a location you're comfortable with. You will find a
.jar(Java archive) in the unpacked directory, and you will need the full path and filenename of the archive.
I like to keep several versions around of JDBC drivers, and to make life easier, I created a symbolic link named
mysql-connector-java.jarto the latest version of the .jar file. That way, I can easily upgrade or downgrade as needed by flipping the symbolic link to another location. You don't need to do it this way - the symbolic link is purely for my convenience.
(So what do you do with your JDBC drivers? Where do you store them? How do you upgrade?)
Configure OpenOffice.org to run JavaFirst, you need to enable OpenOffice.org to use Java. To do that, follow these instructions:
- Open one of the OpenOffice.org applications, such as Calc or Writer.
- Use Menu \ Tools \ Options to configure OpenOffice.org
- In the Options dialog, find the "Java" Category. Check the "Use a Java runtime environment" checkbox. You might see a list there of the Java runtime environments available on you system. It seems to automatically pick up all the Java runtime environments I installed in
/usr/lib/jvmbut I don't dare say it will be the same on your system. However, if you don't see any Java runtime environments there, and you are sure you installed one, you can use the "Add" button and browse to the corresponding directory:
Choose your preferred Java Runtime environment by checking the radiobutton.
(Note that if you add or change a Java Runtime environment, you will be prompted to restart open office:
Configure OpenOffice.org to use MySQL Connector/J
Although OpenOffice.org is now able to run Java, it is still unaware of the MySQL JDBC driver. Follow these instructions to enable OpenOffice.org to use the MySQL JDBC driver:
- Open the OpenOffice.org options dialog, and select the "Java" category as described in the previous section. If you followed the instructions in the previous section, the dialog should still be open.
- Use the "Class Path..." button to open the Class Path dialog.
- In the Class Path dialog, add a reference to the MySQL Connector/J .jar file using the "Add Archive..." button:
- Close the OpenOffice.org options dialog, close the application if you are prompted to restart.
Ok, now we're all set. Everything discussed so far was just one-time preparation, and you won't have to go through it again (except perhaps after upgrading java or OpenOffice.org).
Registering your database with OpenOffice.org Base
OpenOffice.org 2 includes Base, a MS Access like "Desktop Database" application. OpenOffice.org Base acts as a front end for a variety of datasources commonly found in office environments. Typical examples include text documents, spreadsheets, and LDAP addressbooks.
Base can also be used to design and develop simple relational database applications using the HSQLDB database embedded in OpenOffice.org. Base also provides the means to connect to remote rdbms servers through an ODBC or JDBC connection. Right now, we will explore this last usage of Base.
Follow these instructions to register your MySQL database in OpenOffice.org Base:
- Start OpenOffice.org Base to enter the "Database Wizard".
Check the "Connect to an existing database" radiobutton, and choose "MySQL" from the dropdown listbox. Click the "Next" button.
- In the "Set up a MySQL Connection", select the "Connect using JDBC" radiobutton:
As you can see, OpenOffice.org Base also supports ODBC connectivity (not discussed in this article), which might be more natural to Windows users. Click the "Next" button.
- A dialog appeart to specify the connection details for the MySQL server to which you want to connect:
The name of the MySQL JDBC driver class and the port number are already provided by the wizard.
- Before filling out the remaining details, we should test if we set up the MySQL JDBC driver properly. Hit the "Test class" button to do that. If the driver can be loaded, you should see the following messagebox:
If we are convinced that the driver can be loaded, we can fill out the remaining details to establish a connection. Confirm the messagebox and return to the Database Wizard dialog.
- In the "Name of the database" field, type the name of the schema (database) you want use. It is required to specify a database: you cannot access other databases that might be managed by the Server simultaneously from one "Base" database file.
In the "Server URL" field, type the hostname or IP address of the MySQL Server host. You can use the "port number" field to specify a non-default port if necessary, but if you did not configure the MySQL Server to use another port than the default one, it should remain
Hit the "Next" button
- Type the name of a user that can access the database. Check the "Password required" checkbox of the user is required to identify itself with a password.
- Hit the "Test connection" button to verify that the connection can be established.
Close the message box to return to the Database Wizard.
- Hit the "next" button to finish off:
Select the radiobutton in order to register the dabase file in OpenOffice.org. That enables you to access this database in all you OpenOffice.org documents. Check the checkbox to continue working with the database.
- Finally, we can hit the "Finish" button. You are prompted for a name and a location to save the "Base" database file.
If you followed all the instructions, the "Base" main screen will open, and you'll see something like this:
On the left side, we have a sidebar that shows the four different kinds of things you kan work with in "Base":
- Tables - here, you'll find the base tables and views defined in your database.
- Queries - here, you can define database queries, and save them for repeated use.
- Forms - you can use forms to create simple database applications.
- Reports allow you to layout data for presentation purposes
The right, main area of the window shows a task pane (top) and a content pane (bottom). When you select an item in the sidebar, the task pane shows all the tasks you can perform with these types of objects, and the content pane shows all instances available for this type of object.
Great! Now what?
Working with Tables
One of the great strengths of "Desktop Database" applications is ease of use - even for non-technical users. For example, once your database is registered in Base, end-users can directly open a table and work with the data it contains. Let's try that right away.
In the sidebar, single click the "Tables" icon to select it. The "Tasks" pane shows a few taks to create new tables, and the content pane shows all tables in the "Sakila" database. In the content pane, double click the "actor" table. You should see something like this:
Ok, nice. This little grid gives us direct access to the table data. Navigation is quit straightforward: use the mouse to point and click an arbitrary cell in the grid. Use the "tab" key to move to the next cell. Use shift+tab to navigate to the previous cell. The "down" arrow key takes you to the next row, and the "up" arrow key to the previous row.
Changing data is easy too: just navigate to cell and type to edit the data. If you're not happy with the edits, use the "Undo" toolbar button to undo them. Navigating out of the row will permanently store the edit. If you are afraid to accidentally change the data and uncheck the "Edit Data" checkbox on the "Edit" menu.
How about adding a new row? Scrolldown to the last record in the grid, and find the empty row at the end. Navigate into it, and edit. A new empty row is automatically appended. Delete is also easy: click on the left before the first field of the row to select it, and then hit the "delete" key.
The grid also some functionality that might help the end user to work with the data in a more effective manner.
- Filtering - only those records are retrieved from the underlying database that match a condition. Use the toolbar buttons with the funnel icon to filter data.
- Searching - the local set of rows in the grid is searched for records that match a particular condition. Use the toolbar button with the looking glass to search for particular records.
- Sorting - the local set of rows in the grid is reordered according to a sorting key (and order).
My impression of the "Base" data grid is that it is a very useful tool to quickly inspect or enter data. It is far from perfect, and it is also unsuitable for normal application development, but it sufficient for maintaining and entering address lists and the like. Most end-users that are used to maintaining such lists in a spreadsheet application will be able to effectively use this interface instead. Also, besides registering the database in OpenOffice.org, it requires no effort at all to obtain this interface: no coding required.
Yeah sure, nifty grid. Now what?
The "Base" application offers two more items that can be interesting for end-users in an office environment: Forms and Reports.
Forms can be developed to build small end-user applications. For a number of reasons, the data grid just is not suitable to build a genuine end-user application. A lot of the limitations you have with grids can be lifted by creating an suitable form. I will discuss this topic on another occasion more thoroughly, and demonstrate how this can be done.
Reports are useful if you want to present data to the end user in a particular layout or grouping. Reports are also useful if you want to export your data to a pdf format, or if you want to print it. Personally, I think that "Base" reports are less suitable for typical analytical BI applications.
Databases registered in "Base" can be used throughout OpenOffice.org. So, although "Base" offers some functionality which might be used in an office environment, it is really only the start. For example, if you want to perform some kind of analysis using the OpenOffice.org spreadsheet application "Calc", you can draw that from one of the registered databases.
Another typical example for the OpenOffice.org wordprocessor "Write" would be to combine data from the database with letters, or address labels to automate postal mail communication.
Right now, I won't discuss these applications in any detail, but I'll try and write up a tutorial on those subjects soon. Of course, if anybody else wants to share their story on integrating their database in open office, please do not hesitate and add a comment to this post.