FAQ (Base)

OpenOffice Base

Is OpenOffice Base a database application?

Not really, but a HyperSQL (HSQLDB) database engine is included with Base. Base can use the HyperSQL engine to act as a database application in much the same way as Microsoft Access uses the Microsoft Jet database engine to act as one, but both are fundamentally database front-ends designed to connect to a variety of types of databases, not just the one they ship with.

As a front-end, Base provides database management tools (DDL / DML), as well as stored queries, forms, reports and macros. The associated tools include:

• a command-line SQL console
• a visual table designer
• an index manager
• editable table views
• a visual data-relationships designer (cardinality)
• a descent visual query builder
• a relatively powerful form designer
• a rudimentary reports designer [requires JRE]
• a macro development environment
• various extensions compliment this list nicely

Not only does Base display the information in databases in various ways, it can provide that information to the other applications in the OpenOffice suite. To provide this function, Base files can be 'registered' in OpenOffice.Simply press the 'F4' key from any OpenOffice application to access all registered data-sources. Typical data-source usage includes:

• Writer (mail-merge, forms),
• Calc (pivot tables, graphs, forms),
• Impress (dynamic slide data, graphs),
• Base (stored queries, forms, reports).

What database systems can OpenOffice Base connect to?

In addition to the driver for embedded HSQLDB engine, Base also supports standard JDBC or ODBC drivers, as well as native SDBC drivers written specifically for OpenOffice (see: PostgreSQL, MySQL, etc.). Some drivers provide read-only access to existing data stored in tabular-spreadsheets (.ods/.xls), delimited text files (.csv), or popular address-book formats. The bundled "dBase" driver provides full read-write access to flat-file "dBase" databases.

Where is the data saved?

Base saves its data in OpenDocument database files, which have an extension of .odb. Note, however, that as a front-end, its data includes information about queries, report layouts, etc., and where to find the database and how to connect to it, but does not include the data in the database (the information inside the tables)! When the included HSQL database engine is used via the SDBC driver (the default), the database data is stored inside the same .odb file, but otherwise the actual database data is stored externally.

Java

Do I need Java to use Base?

Not necessarily. Java is used to run various wizards and data sources such as the built-in “HSQL relational database engine.” And Java is necessary to utilize the popular 'JDBC' connection option available with a variety of external database engines. But Base, itself, does not require Java. So while Base can offer greater function out-of-the-box with Java installed, the Java components are optional.

Why can't Base load/find my JDBC driver?

OpenOffice must know the path to the JDBC driver for a given Java database engine. This path is known as a "Class Path" in OpenOffice.

To add a JDBC driver (.jar file) to the Class Path:

• Open the Tools > Options dialog

• Open the embedded Java dialog

• Press the Class Path button

• Press the Browse button to locate the .jar file associated with the Java database engine

• Re-start OpenOffice and close Quickstarter (tray-app) as applicable...or simply restart your computer

HSQLDB

What is HSQLDB?

HSQLDB is an open source, cross-platform, SQL, relational database management system (RDBMS). It's also known as HyperSQL or HSQL database engine. It's written in Java so a JRE (Java Runtime Environment) is required to utilize this database engine. HSQLDB 1.8 has been included with OpenOffice since OpenOffice.org version 2.0 (circa 2005). The specific version included with the last several realeases of OpenOffice is version 1.8.0.10. Documentation for this release is available here (rather than from the main HSQLDB home page).

Are there problems with version of HSQLDB that is embedded with OpenOffice Base?

HSQLDB 1.8 is both functional and highly reliable and in many cases will be adequate for single user databases. However, it is obsolete and lacking some features included in the current version.

Can I replace the embedded version of HSQLDB with the current version?

In a word, no. Simply replacing the included HSQLDB.jar file with a current one will not work. However a newer version of HSQLDB can be installed and Base can connect to it using the JDBC driver. To do that, download and install the latest version of HSQLDB. Then locate the HSQLDB.jar file, which will likely be inside the hsqldb\lib directory, relative to the install location. Add this location to the Class Path in OpenOffice. (From any application: Tools/Options.../OpenOffice/Java, then click on 'Class Path...', then 'Add Archive...' and enter the full path to the file.) (WARNING! This can lead to a serious 'gotcha'! See below.) An HSQLDB database can then be created by using the OpenOffice Database Wizard. Select "Connect to an existing database" (even though it doesn't exist yet) and select the JDBC driver, then click on 'NEXT >>'. For 'datasource:' specify "jdbc:hsqldb:file:<path>;shutdown=true;default_schema=true;hsqldb.default_table_type=cached;get_column_name=false", where <path> is location to store the database files. The path can include a filename without an extension. For 'driver class:', specify "org.hsqldb.jdbc.JDBCDriver". Then complete the wizard to create a .odb file for this database connection. More information about the actual database files that will be created is available here. More information about the connection properties specified in the datasource URL is available here.

So what is the 'gotcha' if I specify the location of a new HSQLDB.jar file in the Class Path?

The problem is that any .odb file that contains an embedded HSQLDB database will be corrupted if it is opened while the location of a newer HSQLDB.jar archive is specified in the Class Path. That occurs because Base will using the newer database engine if the Class Path points to it. The new version of HSQLDB will immediately and silently update any old HSQLDB database that it encounters, so if a .odb file containing a HSQLDB 1.8 database is opened and anything causes the database inside the file to be read, it will be updated. The next time Base opens the .odb file, Base (not HSQLDB) will notice the difference and will decide that a newer version of OpenOffice must have created the file, so it will refuse to open it.

When are changes to a database recorded? Are any temporary files used?

As implemented by Base, changes made to a HSQL database are silently recorded directly to the database files. Even when the database is embedded in an .odb file, any changes are written directly to the .odb fild. It is not true that changes are written to temporary files (or held in memory) until the user exits from base, and only then written to the .odb file, despite what some people have guessed.

How do I determine which version of HSQLDB is in use with Base?

In Base, create a query in SQL View, then enable 'Run SQL Command Directly'

• If you suspect the default HSQLDB 1.8.0.10 engine, then type-in (copy & paste):
  • Call "org.hsqldb.Library.getDatabaseFullProductVersion"()

and run the query.

• If you suspect HSQLDB 2.x, then type-in (copy & paste):
  • Select DISTINCT DATABASE_VERSION() as "HSQL Version" From INFORMATION_SCHEMA.SYSTEM_TABLES

and run the query.

Database Conversions

How to convert a table to CSV

1. register the database under "Tools > Options > OpenOffice.org Base > Databases"
2. open a calc sheet
3. press F4
4. choose the table in your database
5. copy the content into the calc sheet

dBase

How can I, in dBase, create queries for more than one table?

You cannot. Queries spanning more than one table are currently not supported in OpenOffice.org's own dBase implementation. There is an issue requesting this, so if you're interested in this feature, go and vote for it.

Why can't I delete a record in my dBase Table?

You probably checked the "Display deleted records as well" option in Edit|Database|Properties, didn't you?

Spreadsheets

How can I import a spreadsheet table to a database?

(answer originally provided by Regina Henschel in users@dba.openoffice.org)

There are two ways:

• You can create a database file which gives you access to the calc-table. You cannot alter the table within base, instead you must use the calc-table
• You can create a new database file and import your calc table.

I prefer to use the second way:

• Create a new, empty database or open an existing one. Leave it open.
• Open your Spreadsheet-table.
• Select your data in the Calc-table and copy them into the Clipboard.
• Go to the databasefile and use Edit - Paste.

You will get the "Copy Table" dialog, which leads you through the import.

Instead of copy & paste, you can also use drag & drop.

• Reduce the windows so that you can see the Database pane of the databasefile window beside the calc table.
• Mark all your data in the Calc-table, including the headings.
• Drag them to the database file window and drop them to the "Tables"-Icon in the Database-pane.

You will get the "Copy Table" dialog too.

Forms

How do I create a form with a sub form?

Look here for an excellent document (OpenOffice.org Text Document, 850K) giving you a step-by-step description.

Miscellaneous

I can access my tables with *Office, but I can't update or insert any data - why?

There are three possible reasons (counting the frequent ones only :):

• *Office requires a table (or, more specifically, the data in a row set) to contain a primary key or a a unique index in order to allow data manipulation. Alternatively, it suffices if the driver supports bookmarks (like some ODBC drivers or the internal dBase implementation do).
  The reason for this is that this is the only secure way to ensure that the changes you do affect the proper record (in case of updates), resp. that you, later on, can work with your newly inserted record.
• You are using a spreadsheet, a text (CSV/TXT), or an address data source. All of these are not yet writable. It's not a bug, it's a missing feature.
• *Office asks the database (in reality its the databases driver) for the privileges (if any) the current user has for the table you want to access. If the driver doesn't report that you have INSERT/UPDATE permissions, then the respective functionality is disabled in *Office. There are various drivers out there (for various databases, most often reported is MySQL) which handle the permissions improperly, and return wrong or too few privileges. Please try the latest possible driver for your database.

Which Database Type do I use?

• Q: I'm asked which database type I use - how do I know?
• A: If you don't know, it's probably HSQLDB. To be sure, open the database document (.odb) in OpenOffice.org, and look into the middle part of the status bar at the bottom of the main window. It should read something like "HSQL database engine", "JDBC", "MySQL (ODBC)" ...

How are my Address Book fields mapped to OpenOffice.org columns?

• LDAP field mapping
• Outlook/Express field mapping

Why does my table show the wrong number of records?

Base developers chose not to load all records for performance and bandwidth reasons. Unfortunately, they also chose to inform you of this incremental approach by displaying a count of only the loaded records. So initially, Base will report a smaller number of records than you actually have. To indicate this, Base will show a small asterisk (*) after the record count. So a number without asterisk 23 means there a total of 23 records in your table, while 23 (*) means there are 23 known records currently loaded, but there are additional unloaded records in the table/result-set.

This record-count display philosophy is not related to performance or bandwidth considerations. Record count retrieval is instantaneous. It's a poor design, but some workarounds may suffice (SubForm workaround / macro workaround).

Why is searching in my form/table so incredibly slow?

This is because when you search (as opposed to filter) the data with OpenOffice.org, then Base retrieves all the data from the server, and looks up itself, instead of letting the server do this. This, of course, is slow. But, though *Office's search is probably not the fastest possible on earth, it's more a problem of you doing the wrong thing to get the right results. Filter your form, to let the server do the work. More information is available in the archive of our mailing list.