From MS Access to MySQL

Introduction

For some years I've tended to store "valuable" data in MS Access databases, partly to stay alert to what's happening outside of the sometimes-claustrophobic worlds of APL and J, partly because MS Access is an application that's easy to use, and partly because I can get at an Access database from any language that lets me use ODBC (so I can switch between APL and J without worrying about data compatibility).

But, there's lockin to Microsoft to consider - I've upgraded through a few versions of Access but enough is enough and I started thinking about finding something which would give me more independance.  Star Office seemed an attractive alternative to MS Office, but the database (Adabas) is most kindly considered an afterthought and seems all but unusable (even as a standalone - I never got so far as setting up a database, let alone interfacing with APL or J).

Then I stumbled across MySQL, choked over the twee name, and gave it a try.  Now, before anyone gets too excited, I'll emphasise that my relational database usage is fairly trivial - these are personal projects running on a single-user system - a few thousand rows in each main table; something that I could probably run just as easily with some sensible-designed component files and utilities (except that I can't read APL component files from J, or vice-versa).

The Nature of MS Access

Access is a "complete solution", it combines back-end relational database functionality with a complete design/run GUI.  You never need to step outside of Access, and it's quite plausible that the applications I've built with APL and J could be built just as well within Access itself (if I could be bothered to learn, which I can't) - but the data manipulation tools of Access are nowhere near as strong or easy to use as APL or J (the question of getting a bunch of data in one place and doing something analytic with it).

Access does things like referential integrity.

Access has fancy things like hyperlink fields.

Access does not run under Unix.

There are ODBC drivers for Access, which means that programming languages can get at Access databases.

Access subtly locks the user in to Microsoft (for example, COM objects are a way to get at the data in an Access database).

Access has to be paid for.

The Nature of MySQL

MySQL is strictly a database backend; it's driven by command lines (SQL syntax).  So far as I can see there's no GUI for MySQL other than things people have written for their own use (this, like everything else, may change over time).

MySQL does not (at the time of writing) do things like referential integrity (which surprises me - so I may be wrong).

MySQL is (reputedly) strong in handling large quantities of data (which I don't have), offers industrial-style field type, and does not seem to have fancy stuff like hyperlink fields.

MySQL runs under several operating systems (not sure about Mac though).

There's a MyODBC interface.

MySQL has nothing to do with Microsoft (aside from the link to ODBC).

MySQL is free; you can pay for training (but rudimentary SQL is enough to get by), you can go into a bookstore and choose between several big fat books (although they don't seem to contain a lot that isn't in the big fat help file).

Data Transfer from Access to MySQL

Installation goes fine, and so does a run through some tutorial pages.

MS Access decides that it doesn't want to export my data to .csv files (either my installation has corroded over the years or there's a smart "defector detector" at work).  But it's quite happy to export to Excel, and Excel to save as .csv so the day is saved.

Since I mostly have quite straightforward field types there's no real problem, although belatedly I find that a hyperlink field type got mangled (but nothing that a bit of APL won't straighten out again).

Data loading through the MySQL command line works fine, if a bit tedious (several dozen tables spread over a half-dozen databases).

Using a MySQL Database from Dyalog APL

The SQAPL interface supplied as part of APL/W is quite happy with MyODBC data sources; I haven't done any performance comparisons because performance isn't critical for these applications (and obviously it's good enough that I don't care).  Switching a simple application involves nothing more than changing the ODBC source name.

One area that's a bit of a mess is date formats (aren't they always?).  I had to do a bit of formatting in Excel to turn the Access-derived dates into a style that MySQL was happy with (simple, but tedious) and on the whole I think I'd rather switch to IDN and be done with "native" dates.  But that's almost not a database question, more an overall philosophy.

Because there's no GUI within MySQL I'm using APL/W functionality to build an overall database viewer, which has plugin capabilities for each individual database.  The data review/entry forms are (on the whole) easier to use that those I had with MS Access - probably because I really couldn't be bothered to learn enough Access syntax to do the things that forms ought to do (for example, adding a new row to a combobox list when the user types something new).  The viewer is a work-in-progress, and I may write more about it later (the main problem I have is the tedium of creating so many forms, and I may do something to automate this).

Again, loss of referential integrity is a mixed blessing.  On the one hand, databases really ought to do this - on the other (within my environment) it's easy enough to do in APL and I can build a better user interface to report issues.

Conclusions

I'm happy enough - MySQL was the missing piece that got me off the MS Office treadmill (Star Office works well enough for everything except the database, Outlook got replaced by Mozilla Thunderbird, IE by Mozilla Firebird).

Having to recreate the GUI side of my applications is tedious, but I think the result (built with APL/W) is an improvement.

Using simple datatypes seems more sensible that using the MS fancy stuff (in fact I'm even thinking of paring it down to just the classic APL distinction of characters and numbers).

In my environment, performance is not an issue.

I have not (yet) explored getting at my MySQL databases from J (I'm an old-J user and haven't sprung for the JUL that lets me use J5).  I still haven't got round to putting Linux on this box and can't say whether I'll be successful in moving my applications over there - maybe one day...


Copyright Dogon Research 2003-2006; Latest Update: 07 December 2003 11:46