- 1 OpenOffice Base
- 2 Java
- 3 HSQLDB
- 3.1 What is HSQLDB?
- 3.2 Are there problems with version of HSQLDB that is embedded with OpenOffice Base?
- 3.3 Can I replace the embedded version of HSQLDB with the current version?
- 3.4 So what is the 'gotcha' if I specify the location of a new HSQLDB.jar file in the class path?
- 3.5 When are changes to a database recorded? Are any temporary files used?
- 3.6 How do I determine which version of HSQLDB is in use with Base?
- 4 Database Conversions
- 5 dBase
- 6 Spreadsheets
- 7 Forms
- 8 Miscellaneous
- 8.1 I can access my tables with *Office, but I can't update or insert any data - why?
- 8.2 Which Database Type do I use?
- 8.3 How are my Address Book fields mapped to OpenOffice.org columns?
- 8.4 Why does my table show the wrong number of records?
- 8.5 Why is searching in my form/table so incredibly slow?
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.
- 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.
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 > Optionsdialog
- Open the embedded
- Press the
- Press the
Browsebutton 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
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 22.214.171.124. 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 global class path setting 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 for details and for how to specify the path an alternate way.) 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 global class path setting. That occurs because Base will use the newer database engine if the global 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 that the database files are no longer in the format used by HSQLDB 1.8 and will conclude that a newer version of OpenOffice must have somehow created the .odb file, so it will refuse to open it. This only happens when Base opens a .odb file containing an embedded database using a version of HSQLDB that is 2.0 or later.
The problem can be avoided using a document macro to set the Java driver class path for each document that uses the newer HSQLDB engine, rather than setting the class path globally. This uses a property that is specific to an individual database document (.odb file), so it will not apply when a different .odb file (potentially containing an embedded database) is opened. However, the alternate class path property can not be set manually; a macro must be used as shown below:
ThisComponent.DataSource.Settings.JavaDriverClassPath = _ "file:///C:/Program%20Files/HyperSQL%20Database/hsqldb-2.3.0/hsqldb/lib/hsqldb.jar"
The JDBC driver class (ThisComponent.DataSource.Settings.JavaDriverClass) and datasource URL (ThisComponent.DataSource.URL) can be specified (manually or programmatically) and the connection can then be made to the back-end database. Windows users should note that the Java driver class path must be specified as a URI, with forward- rather than backslashes and with spaces encoded as "%20". The macro only needs to be run once for each database document that requires the setting because it is retained inside the .odb file. Of course, if it is known that OpenOffice will never be used to open a .odb file containing an embedded database, the global class path can be used, instead.
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 file. 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 126.96.36.199 engine, then type-in (copy & paste):
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.
How to convert a table to CSV
- register the database under "Tools > Options > OpenOffice.org Base > Databases"
- open a calc sheet
- press F4
- choose the table in your database
- copy the content into the calc sheet
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?
How can I import a spreadsheet table to a database?
(answer originally provided by Regina Henschel in email@example.com)
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.
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.
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?
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.