David Smeringe weblog

by Josef from Eden Foundation.

Having an old MS Access database full of important data can be a real nuisance for cross-platform strategies. Lately, I’ve been evaluating what approach to use for making a Java desktop app run on Linux. The tricky thing is that the database it interfaces with is in the Microsoft Access format, developed at the time when that product was new and cutting edge (yes, that was in version 1.1). Now Access’ engine library ADO won’t run natively on Linux, so this far, my program has been limited to Windows by making use of the jdbc:odbc-bridge. Today, with Linux desktops quickly gaining importance, this needs to change. Migrating the database to another system like MySQL is a bit of a job, so I searched for less draconian alternatives. I found a promising library, Stels-MDB, that claims to handle Access’ MDB files through native Java code. I downloaded the trial version and gave it a run. Here is a summary of my experiences:

Switching the database driver from jdbc:odbc to Stels-MDB was very easy. It was just to adjust the two connection strings for the driver name and parameters, and voilà. I could run my app. But Stels does not behave identically to Microsoft’s ODBC driver, so there were some issues to look at.

Case sensitivity

Stels-MDB was case sensitive. This meant that a search criteria of “DALLI” used in a query would not detect records containing the value “Dalli”… A major issue in my eyes. Fortunately, support at J-Stels Software seems to be very good. Within a day or two, they had recompiled a new version of the driver with an option that removes case-sensitivity. I think SQL searches should be case-insensitive by default, but anyway, their solution was satisfactory as it only required me to make a trivial modification to the JDBC connection string. Great guys!

Different SQL syntax

Stels-MDB does not use the same SQL syntax as MS Access. Access, in fact, deviates from the Ansi SQL92 standard, and that’s a pity, because Access’ unique traits (such as using brackets for quoting fields) adds nothing of value compared to the standard syntax. So Stels could choose two smart ways here, either run the ugly MS syntax for compatibility with legacy Access-based apps, or they could follow the ANSI standard so that the Stels engine can be seamlessly plugged in and out of vendor-independent database apps that don’t care which underlying database you are using, only that you have a valid JDBC driver. Unfortunately, Stels have opted for a syntax that has some quirks of its own, probably to simplify coding and to keep the style with their other query engines (for CSV files and more). The most basic things work well; quoting fields can be done both through double quotes and backticks. But other ordinary stuff has to be done through special functions. A date value for instance, has to be wrapped into the function “to_date()” to be recognised. So as a developer, you easily risk embedding “Stels-only” sql into your program code. Or, you’ll have to create your statements through an sql-generating library[link], where you add a subclass to cover Stel’s peculiarities, which is what I did.

Syntax of AnsiSQLFormatter:
SELECT "Order ID", "Product ID", "Order Date" FROM "Orders" WHERE "Order Date"='25-Nov-2008 21:24:15'
Syntax of MsaccessSQLFormatter:
SELECT [Order ID], [Product ID], [Order Date] FROM [Orders] WHERE [Order Date]=#25-Nov-2008 21:24:15#
Syntax of StelsSQLFormatter:
SELECT `Order ID`, `Product ID`, `Order Date` FROM `Orders` WHERE `Order Date`=to_date('2008-11-25 21:24:15','yyyy-MM-dd HH:mm:ss')

Limited SQL features

Another problem I ran into with my queries, was that they were too complex for the subset of features currently supported by Stels. Subqueries, in particular, were not popular, they provided me with messages like this:

java.sql.SQLException: [StelsMDB JDBC driver] Can't parse SQL query: [SQL Engine API] Incorrect syntax near the word 'FROM' (position: 39). See realized SQL specification in the driver documentation. SQL query was: 'SELECT MAX(`Time`) AS `MaxTime` FROM (`Microplots` INNER JOIN `MicroplotInfo` ON `Microplots`.`Plot`=`MicroplotInfo`.`Plot` AND `Microplots`.`Bar`=`MicroplotInfo`.`Bar`) WHERE `Microplots`.`Year`=2008'

I also missed some common functions like Day(), Month() and Year(). But I could probably easily have added these to Stels myself, because it provides an extension mechanism for custom-made functions.

Jackcess under the hood

Although Stels-MDB is closed-source, it is based on the open-source library Jackcess. From what I understand, it basically wraps Jackcess’ non sql-based api into a query engine so you can use it in more normal ways, and, well, run queries on your data. Unfortunately, just as Stels’ query engine is not fully mature, Jackcess’ table handling doesn’t seem to be yet either. My tables were certainly of the more demanding type – developed over the years through basically every past version of Access, with lots of indexes and foreign keys (referential integrity in MS Access terms). They’re actually a headache in relationships. Knowing that the mdb format is cryptic and undocumented (I haven’t seen MS make any effort to help the developer community access this format, despite the product’s name…), I was not surprised that Jackcess had some hiccups. I couldn’t figure out what the root causes were, or didn’t spend enough time searching. Some problems were solved by compacting my database and removing obsolete indexes, whilst others wouldn’t go away. Here’s one error that stuck:

[StelsMDB JDBC driver] Error executing sql: [StelsMDB JDBC driver] Can't write 'Seeds' on the disk. Error was: Can't insert a record in the table 'Seeds' [Jackcess] FIXME cannot write indexes of this type yet.

I couldn’t reproduce this problem in a simpler, test database that I built from scratch, but then again, I did not invest the necessary time to make a full inquiry into the matter. But it’s clear from the FIXME that some features have not been implemented in Jackcess yet.

Performance

A little use of a stopwatch when running my code through Stels gave me a rough comparison of its speed to that of the ADO engine running through the jdbc:odbc layer. Stels is slow. The operations I timed took 6 – 10 times longer with Stels than with Access’ engine. This would have been ok if I was plundering mdb files for data in a series of one-time jobs. But it was too slow for running an ambitious desktop app with high demands on its database back-end.

Conclusion

All in all, Stels-MDB is a brave effort that really had my hopes up. But it isn’t mature enough for more high-end needs. I am very glad to see that there are people trying to provide solutions to us Java programmers sitting around with important mdb-files and wanting to take full advantage of the potentials of platform independence. I hope that both J-Stel Software and the Jackcess project make steady progress until that dream comes true. Figuring out the mdb format without official documentation from its software house is a formidable challenge… I also hope that Stels-MDB moves towards standard ANSI sql syntax, and still wonder why MS Access did not do so more than a decade ago. For now, we’ll have to look for something else here at Eden Foundation. Perhaps outright migration to MySQL? We’ll see…

This entry was posted on Friday, March 6th, 2009 at 23:37 and is filed under Java. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.