MySQL meets OpenOffice.org

A lot of MySQL software development articles focus on the server-side. This is true even end-user applications: most MySQL applications I read about, are web applications.

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.

Some Prerequisites

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.

Client Software

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.

I won't discuss the installation procedure for OpenOffice.org or the Java runtime environment. As much as I'd like to, there's just too many variety to elaborate on this in a useful way. However, I trust few if any problems will arise obtaintaining and installing these items.

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.jar to 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 Java

First, you need to enable OpenOffice.org to use Java. To do that, follow these instructions:

1. Open one of the OpenOffice.org applications, such as Calc or Writer.


2. Use Menu \ Tools \ Options to configure OpenOffice.org


3. 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/jvm but 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.

By completing these steps, OpenOffice.org is able to run Java applets and programs. This is a prerequisite to enable the OpenOffice.org Base application to connect to MySQL.

(Note that if you add or change a Java Runtime environment, you will be prompted to restart open office:
Don't restart just now - we still need to configure OpenOffice.org to be aware of the MySQL JDBC driver.

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:

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


2. Use the "Class Path..." button to open the Class Path dialog.


3. In the Class Path dialog, add a reference to the MySQL Connector/J .jar file using the "Add Archive..." button:
   


4. 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:

1. 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.
2. 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.


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


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


5. 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 3306.Hit the "Next" button


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


7. Hit the "Test connection" button to verify that the connection can be established.Close the message box to return to the Database Wizard.


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


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

'Doing' MS Access

I've just come home visiting one of my Inter Access co-workers.

Barbara is a very capable Oracle Developer, Information Analyst and BI Consultant. She enjoys the craftmanship so much that she still finds time to help out friends managing their little IT solutions.

For example, one of her friends owns a sports clothing store. She built them an order entry system in that popular little Database by Microsoft known as Access.

As you might expect, the system has grown over time, and has had too little administrative attention. Now, it's about 30Mb of data. This may not seem much, until you try to run a query. Size really is a relative measure.

What's worse, the number of users has grown too. Again, it may not seem much, but five users that are simultaneously working with one Access database is really about the limit. At least, it seems to be the case here.

She decided that it might be a good idea to try porting the application's backend to MySQL, and she asked me if I had any experience in porting such an application. 'Yeah..' I said, lying through my teeth of course, '..sure I have. I do that stuff all the time.'. 'Lyer..', she said, '..but do you think you can give me a hand?'.

Of course, I said yes. I really never did this, although I was aware of the builtin functionalities of the migration toolkit to do the legwork. And I'm always eager to goahead and try these kinds of things, especially since this is sort of a real, mission-critical application. (Just because we're doing this in our spare time, and that this concerns only a small sportsclothes shop does not change the fact that this application is mission-critical and that the migration should be planned and executed carefully).

Luckily, the application was already built in two separate chunks. The front end is an Access application: It contains some stored Queries ('Views'), some Forms and some VB code. The backend is also an access database. This one is worth 35 Mb of base tables. The front end communicates with the backend through a so-called 'linked table'. (For the MySQL techies: this offers the same functionality as the FEDERATED storage engine in MySQL)

In our first endeavours, we used the MySQL Migration Toolkit (download: here) to reverse engineer the database schema and to load the data into MySQL. This really went remarkebly well. We used default options in most of the cases, and I can only conclude that this is a very good way to import an access database into MySQL.

Then, we used the MySQL ODBC driver to link the existing Ms Access front end to the MySQL backend database. The immediate first impression regarding this solution is that query performance is much better than in the old situation, so that's a thumbs up for MySQL. And we didn't even do any tuning sofar.

A bit of a bummer is that we did encounter problems inserting data into tables with socalled AutoNumber columns (such a column is very much like an auto_increment in mysql).
In the fist place, the migration toolkit did NOT automatically map those to mysql auto_increment columns. This was fixed soon enough though. We just added the auto_increment attribute ourselves.

A more serious problem was that Access did not synchronize well upon insertion of a new row. The row would be put into the database, but immediately after insertion, the access front end would show the row as #deleted#. Refreshing the view on the data from inside Access would show the row, proving the insert did indeed take place, and proving access is capable of retrieving the newly inserted row.

We did some searching on the MySQL Access Migration forum but alas, to no avail. Lucky for us, a bit of browsing around brought us to the -unbeatable - reference manual. The "deleted" problem is described there exactly, and a workaround is ginven there too.

(By the way, the manual has an entire section devoted to using the MySQL ODBC driver in combination with Microsoft Access)

We had to change just two things. First, we had to enable the Return matching rows option in the MyODBC configuration dialog. This is available from the ODBC Driver manager. Then, we had to add a timestamp column to the tables:

    alter table table1
    add dummy timstamp
    default current_timestamp
    on update current_timestamp

...and that fixed the problem.

A point worth mentioning is that it really doesn't matter how you call that column. You don't even need to use it in your application. It just needs to be there, that's all.

Unfortunately, we are both at a loss as to explain why (and how) this works. We even did some ODBC tracing to see what happened. We couldn't find out how it works, but we could see something else that's interesting. After the INSERT, Access performs a SELECT, presumably to synchronize it's view on the data. Judging by the trace, that SELECT statement does NOT use the timestamp column in the WHEREclause.

Now, there still have to be some tests, but at least, we're very confident that this port to MySQL will be quite successful and without siginificant effort (thanks to the Migration toolkit)

Barbara promised she'd let me know if there's any progress or any troubles, so I will report them back in this blog, '...for the world to knwow...'