Open Office Navigation

OOBase Database | OOCalc Spreadsheet | OODraw Vector Graphics | OOImpress Presentation | OOWriter Word Processor Using Mail Merge

Latest news

26 Mar 2008: OpenOffice 2.4 released with some improvements in functionality for Base e.g. improvements to Query Designer.

04 Mar 2008: Updated to cater for when you install a new version of the Java runtime libraries.

Links:

• OpenOffice

Open Office Base

This is the new database component of the Open Office Suite that I have been checking out all through the development stages to the final release. As I specialise in MySQL for all database work this section concentrates on how well the interface to MySQL databases is implemented.

After using Open Office for over a year now, I took the plunge and removed Microsoft Office from my PC, which was admittedly quite an old version, but nevertheless did the tasks I required. I now use Open Office for all my personal tasks and having converted my data files to the new file formats, I would not consider moving back to Excel or Word. This is not to say that Open Office is perfect, far from it, but it does mean I have sufficient confidence in it for letter writing and maintaining my spreadsheet finances. On the other hand when you start looking at some of the other modules such as the drawing package, you will quickly realise there are better open source alternatives. The database is much improved within Open Office, although again I prefer to use mySQL and use Open Office to make the connections.

Databases Supported

The default database is dBase, which in my opinion has had its day, but this should not deter you from looking further as it supports connections to Microsoft Access, MySQL, Adabas and others. In line with the 'open' source philosophy I was particularly pleased to see MySQL in the supported list as well as the Address Book data held in Thunderbird. You have a choice of database connectivity tools either using Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC.)

Connecting to MySQL

I chose to use JDBC to connect to MySQL because it seemed very straightforward to download the connection from the MySQL site. In fact I downloaded all the source files as well, but only the mysql-java-connector-3.1.7-bin.jar is actually required. You will also need a Java Runtime library installed but this does seem to be a pre-requisite for successful installation of OpenOffice. Setting up the JDBC connectivity did take me some time, mainly because the documentation for version 2.0 beta was aimed at version 1.1. I eventually went into Tools, Options, OpenOffice.org, Java and made sure that the use Java Run Time option was ticked. Then using the classpath checkbox I browsed until I located the .jar file. Once this is achieved all that remains to do is to check the connection by going back to the top menu and selecting File, New, database. Choose 'Connect to an Existing Database'and for Database Type choose 'MySQL'. Press 'Next' and you can choose ODBC or JDBC connection - select JDBC. On the next screen enter the name of your existing MySQL database, 'localhost' as server URL when running on local machine. On the next screen you will be prompted for your username and password where appropriate. You can then proceed to register the database in OpenOffice and create a .odb file, so that next time you want to connect to this database all you have to do is File, Open, .odb file. Very straightforward once you have cut through the misleading help information.

Installing New Version of Java

New versions of the Java Runtime libraries appear reasonably frequently, so I will normally install the latest release only to find that when I try to connect to the existing defined .odb database, I get the message that "OpenOffice requires a Java Runtime Environment and the selected JRE is defective". I think I found the problem by going to Tools, Options, OpenOffice.org Base, Connections and then I tick "Connection Pooling enabled and then select the MySQL driver and tick "Enable pooling for this driver". This does sort the problem, but I am not sure if this is a bug or something that's supposed to happen.

Installing New Version of MySQL Java NET Connector

From time to time new versions of the Java NET connector will be released on the MySQL web site. You just download the zip file and all you really need to do is place the .jar file which looks something like "mysql-connector-java-5.1.6-bin.jar" in a folder which you then have to set as the classpath for the JRE using Tools/Options/OpenOffice.org/Java. All the other files in the zip are not really necessary and if you are only using this to connect to OpenOffice, can be deleted. When I recently installed a new version of the Java NET connector, I found I had similar problems with messages as above for installing a new JRE. The only way out of this seems to be a bit of trial and error. If you re-instate the JRE by selecting the path to java.exe then magically all your old database definitions (.odb) will work.

Viewing and Editing Tables

By double clicking on a particular table a new window is opened where you can view all the rows in the table. You can scroll down the table and inspect the data; scrolling speed has been improved in the RC2 version but there are still some curious yellow characters appearing sometimetimes on the right hand side of the scroll area. It is possible to sort the table by any particular column in ascending or descending sequence. There is a search facility that will locate any text in a particular column and a filter to restrict the view to certain records. You can enter edit mode that allows you to change or enter data in a particular field. This all works well with no real problems, except the column widths do not default to the maximum width field size, which is a bit annoying. Also it should be remembered that MySQL has its own Query Browser that lets you carry out many of these tasks.

Creating Tables

Although you can use MySQL Administrator to create tables it is not quite as easy as in Base. Here you can add a field name and then select the field type from a drop down list. This is very intuitive and easy to use. There is also a Wizard that takes you through designing and building the table. A further nice feature is the ability to create views, so that if there are certain data items from various tables that you commonly use you can create a view which can be used later in queries and forms without the need to go through all the process of table joins each time.

Queries

There are three methods of creating queries; either create in design mode where you first select the tables and then use the mouse to create joins. Once this is done you can select any fields, then functions and criteria for your 'where' clause. All of this can be achieved with very basic knowledge of SQL and finally run the query. From the tests I have run it works quite well. The second method is to enter raw SQL commands and run direct. Again this works well without any problems. The third method is to use the query wizard, which takes you through a series of forms that help you define the query. This allows joins, sorts and filters to be defined with a result similar to that of writing your own SQL.

Forms

I tried the forms wizard again in version 2.2 and this time I got reasonable results when basing a form on a single table. The wizard takes you though each step required but I did find the whole area of subforms and table joins quite confusing.There is a design mode which lets you place individual fields on a form, but I have still to thoroughly test this out. Suffice to say that I can see very little reason to use the base application as opposed to a database running under Microsoft Access or Delphi 7.

Reports

In the 2.2 version there is only one method available for creating a report using the reporting wizard. This worked reasonably well on a small database of 4,000 records, so it is vastly improved over the earlier versions which broke for databases of about 2,000 records. Unfortunately dates from MySQL database are still displayed in US mm/dd/yyyy format despite having selected the GB-en locale and English UK language in the options. The column widths for the fields are non-optimal and are displaced to the RHS. The overall impression is that this is still unacceptable as a reporting tool. In fact I can obtain better results by dumping the MySQL database to the calc module, setting a header for each page and printing out from the spreadsheet.