Kexi is touted as an open source competitor for Access. It comes as part of KOffice, but can also be downloaded as a standalone application. Installing Kexi is fairly simple, even if you’ve never installed a Linux application before. On the other hand, OpenOffice.org Version 2 is still in beta, and takes quite a bit more work to install (depending on your Linux distribution). Once OpenOffice.org 2 has been released, it should be as easy to install as the stable version of OpenOffice.org, version 1.1.4.
Database basics – tables
When you open up a new blank database in either Kexi or OpenOffice.org Base you can do pretty much the same things you can with Access. The most fundamental operation is the creation of a new table. Each application gives you a design screen in which to add fields to a table. There are minor differences in the way the information is presented in each application. For example, Base allows you to create a field of datatype Memo (LongVarchar), whereas in Kexi you have to define the same field as Text with a sub-type of Long Text. The end result is, however, exactly the same.
Base does provide a couple of extras. You will find a table creation wizard which may be of some use if you are a novice, as well as a view creation form that can be useful if you are using PostgreSQL or MySQL 5.
Moving on – queries
Just as with Access you can create queries in Kexi and Base. The key difference is that they both use standard SQL, rather than Access’s pseudo-SQL.
Query design is easy in either application. Both give you a form to design a query that’s similar to the one used with Access. However, there is a major difference between Base and Kexi when it comes to SQL. Base allows you to create and edit query definitions in either text mode (in which you can enter the raw SQL) or via a design GUI. In this respect it is identical to Access. With Kexi you can create and edit queries only using the design form. There is a SQL screen, but it’s used purely for viewing or testing the SQL statements.
Using forms to view the data
Designing a new form is particularly simple with Kexi. That’s not to say that it’s difficult with Base — it’s not — it’s simply that Base gives you a daunting number of toolboxes and objects at first glance. Even so, you’ll quickly have a form up and running within a short time.
You can even use OpenOffice.org’s Basic code to create a form that does whatever you need it to. Kexi lacks this option. You can create a script, but there is no documentation on the scripting language used with Kexi. In fact the Kexi FAQ states that scripting is not supported yet. Instead Kexi contains some built-in actions that can be assigned to buttons on the form.
Making the data pretty – reports
Base’s Report Wizard is very effective. If you’ve designed reports using Access, you’re going to feel at home here. By contrast, Kexi, as included with KOffice 1.4.1 does not include reports, though reports are in development for Kexi 1.0. Instead you are expected to use KOffice’s Kugar application. There is nothing wrong with that, except that if Kexi is actually the Access competitor KOffice claims it to be then it should be able to do the same jobs.
Data in other databases
Both Base and Kexi surpass Access when it comes to using other databases, such as MySQL. They don’t do this the way Access does, by creating links to individual tables. Instead they connect directly to the database in question. Kexi can even create a new database from scratch, though not through its GUI. The following example would create a MySQL database called “kexi_demo” on the local host.
kexi --createdb --drv mysql --host localhost --user root kexi_demo
A key difference between Kexi and Base is that Kexi can work with only the external databases that it has created. Base can’t create new external databases, but it can work with external databases that have already been created, so long as it has support for that database engine.
There is also a difference in the way that the two applications connect to a new database. Kexi has no GUI for doing this, so you must connect from the command line:
kexi --drv mysql --host localhost --user root kexi_demo
| Setting up the MySQL drivers in unixODBC |
|---|
UnixODBC is easy to use, provided that you know which files to use when adding a new driver. There can be a certain amount of trial and error here. To save a lot of time you can try these settings for MySQL:Driver = /usr/lib/libmyodbc3.so
You may also find that you are missing the file libiodbc.so.2. You can find it at the iODBC Downloads site. |
OpenOffice.org needs one of the standard Open DataBase Connectivity (ODBC) applications — e.g. ODBC on Windows, unixODBC or JDBC (part of Java) on Linux. You may also need the appropriate driver, such as the MySQL connector (also known as MyODBC).
Once you’ve connected to the database you can go on to create tables and queries with either Kexi and Base.
Migrating from Access?
If you’ve already done a lot of your development in Access and don’t want to start again from scratch, don’t despair — both applications can use the databases that you’ve created. Base can access them via ODBC, although you will also need Microsoft Data Access Components installed. If you’re using Kexi then you can use its Import Database function. This also requires some extra software — Kexi’s MDBDriver.
Performance
You may be tempted to think that Base in OpenOffice.org 2 is the obvious choice as an Access replacement. However, there is a trade-off to its functionality, and that’s its speed. It is very slow. I don’t know if this is because it’s still in beta mode or if it’s because it didn’t like the Slackware 10.0 system that I use. What I do know is that Kexi is lightning fast in comparison.
I don’t think that Kexi’s claim to be the “long awaited open source competitor for Microsoft Access” is really true. What is true is that it has the potential to be the open source competitor to Access. And right alongside it will be Base.
