FAQ (Base)
Contents
- 1 Base
- 2 Java
- 3 HSQLDB
- 3.1 What is HSQLDB?
- 3.2 Is HSQLDB reliable?
- 3.3 So why should I avoid 'embedded databases'?
- 3.4 How do I setup support for 'split' HSQL databases in Base?
- 3.5 How do I create a new 'split' HSQL database using Base?
- 3.6 How do I convert an "embedded database" to a "split-database" ?
- 3.7 How do I edit the Field properties of a 'split database' Table ?
- 3.8 How do I setup multi-user 'server mode' access to my HSQL database?
- 3.9 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?
Base
Is Base a database?
Not technically. Database applications often incorporate separate "front-end" and back-end components. Base is no exception. This two-tiered software model provides flexibility and the necessary data-durability. It involves separating the graphical user-interface (GUI front-end) from the core database functionality (database engine back-end). Base is merely a front-end, used primarily to connect *Office ODF documents to various data-sources including tabular spreadsheets, address books, dBase tables, or relational databases. In this primary role, Base is used to create the necessary connection file (ODB), which can then be 'registered' in *Office for universal data-access from Writer, Calc, etc. For instance, a registered data-source can be selected in the Mail Merge Wizard within Writer, allowing the creation of mailing labels or form-letters. You can also add dynamic tables or query-views to a Writer document or Calc spreadsheet utilizing registered data-sources. Data analysis becomes possible with Calc through charting and pivot tables (Data Pilots). Base, itself, can be used to view and manipulate the data, generate stored queries or views, create forms, reports, and macros. These graphical elements in Base are provided through various wizards or visual designers including: a Table Designer, Query Builder, Form Designer, Report Designer, and Integrated Macro Development Environment (IDE). And while Base is typically used to create forms in the *Office environment, note that all Form elements (form 'controls' such as table grids, list boxes, navigation bar, etc) are available in Writer and Calc, complete with access to all registered data-sources. In fact, a Base form can be exported as standalone Writer document.
Confusion lies in the fact that *Office is also bundled with various "back-end" database options which connect seamlessly with Base. This can give the impression that Base, itself, is providing the back-end database functionality. But that's not actually the case. Bundled database options include: spreadsheets (flat-file datasets), dBase tables (DBF), and a relational database engine (HSQLDB) featuring SQL.
Furthermore, Base creates an "embedded database" by default utilizing the bundled HSQLDB engine. In this configuration Base collects the user-files generated by both Base and HSQLDB, and stores these files within a single ODB file. This really confuses the issue because we naturally assume that a Base ODB file is comprised entirely of Base-generated output. In reality, a Base ODB file is little more than a zip-archive which may contain files produced by multiple applications. So your ODB file might contain files generated by the database engine (HSQLDB), or by Writer (Forms and Reports), or by other components such as the Query Builder (stored-queries) or IDE (macros). In other words, various user-files are commonly "embedded" inside a single Base ODB file (zip-container file).
So when paired with a bundled data-source Base can be mistaken for "database." But in reality, Base contributes only connection- and other front-end functionality. The back-end database functionality is provided exclusively by the specific database connection driver and/or DBMS. This distinction is admittedly blurred by the seamless nature of the bundled database options (flat-file drivers and a relational HSQLDB engine) which are also installed with *Office.
While it could be said that *Office includes a database (or two), the Base component does not qualify as a database in-and-of-itself.
What are my database options?
Several database-connection drivers are included with Base. Some provide read-only data access, while others provide full read-write data access. For example, the included read-only "Text" drivers provide 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" tables, to include table creation and rudimentary queries through Base (requires write-privileges to the database folder); this dBase driver provides flat-file database functionality comparable to MS Works. But Base goes much farther. Base also supports standard JDBC or ODBC driver access to all major database engines, as well as native-SDBC drivers written specifically for *Office (see: PostgreSQL, MySQL, etc.). The bundled HSQL relational database engine enjoys tight integration with Base, but HSQLDB provides an external JDBC driver option which increases reliability and provides multi-user database access (see 'split HSQL database' below). The bundled HSQLDB engine is often sufficient, but HSQLDB 2.x takes Base to new levels with all the latest desktop database features and functions. Base can also be connected to full-scale engines, but be aware that these RDBMS's generally require an alternate database manager for DDL tasks and general setup -- although it is possible to send the necessary low-level commands using the Tools > SQL console in Base.
- With so many options, it can be difficult to choose the best path for a Base project. In general, if you're building a database from scratch using Base, the bundled HSQLDB engine is the 95% solution. Very few Base users will ever exceed the features and functions provided by this bundled engine (HSQLDB 1.8). But if you outgrow the bundled version, it's a relatively trivial matter to upgrade to HSQLDB 2.x which is fully sufficient (99% solution) with Base -- because it's virtually impossible to exceed the capabilities of this full-featured SQL engine within the context of a Base desktop/LAN project. HSQLDB 2.x features include: modern SQL support, multi-user security/optimizations, advanced data-types, stored procedures, ACID reliability, encryption, hot-backups, billion record databases, and much more. So it's probably best to begin with HSQLDB 2.x whenever possible.
- Of course, Base can also be used to access MySQL, PostgreSQL, and other popular full-scale RDBMS solutions. This is a critical feature because it provides access to corporate data, or other existing databases. Otherwise, these full-scale RDBMS solutions are overkill for new projects with Base due to more complex setup and daily maintenance requirements, while adding little -- if anything -- to a Base-centric project.
- At the other end of the spectrum are those who simply maintain their data in tabular format using a spreadsheet. No problem. Base connects seamlessly to data in popular spreadsheet or delimited-text formats. However, the data is read-only through Base and a spreadsheet can become unmanageable beyond a few hundred rows of data. The dBase driver takes it to the next level with read-write access to one or more flat-file tables, which can be created through Base (ensure write-privileges to the dBase folder/files). These basic drivers also supports some rudimentary query capabilities through Base, thereby providing a flat-file database capability quite similar to MS Works. This may be sufficient for many Base users, particularly as SubForm-links may be used to create ad-hoc relationships between tables. But unless you're working in a Java-restricted environment, the bundled HSQLDB driver can also enhance flat-file data access. Of course, HSQLDB can be used to create both flat-file and relational database tables. But did you know that HSQLDB can also be used to establish read/write connectivity with delimited-text files (such as CSV)? So HSQLDB becomes the best choice with Base even when you're simply connecting to tabular data (CSV delimited data) such as a spreadsheet, since this connection method allows both read & write capabilities. So HSQLDB subsumes the function of the Text and dBase drivers with Base (while making child's-play of SQLite).
Can I install Base alone?
No. A full installation of Apache OpenOffice including Writer is necessary to access all available functions of Base.
What can Base do for me?
Available Base functions and configurations include:
- Base is a database connection tool. Base can be used to generate connections to various data-sources, thereby enabling data access/integration across the Apache OpenOffice suite. To provide this function, a Base wizard is used to create a connection file (.odb). This standalone file is then 'registered' in Apache OpenOffice for universal access to the data-source. This process may be repeated to gain access to additional data-sources. Simply press the F4 key from any *Office application to view 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).
- Supported data sources include:
- spreadsheets (tabular data; read-only),
- delimited text files (CSV; read-only),
- popular address book formats,
- dBase tables (one table per file; read/write to include table creation),
- SQL relational databases (multiple tables; read/write plus many additional functions).
- Typical data-source usage includes:
- Base is a well-rounded 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 built-in query parser adds parameter query support
- a relatively powerful form designer
- a rudimentary reports designer [requires JRE]
- a macro development environment
- various extensions compliment this list nicely
- Base provides a database application development environment. Base is merely a front-end, but it's bundled with a few 'back-end' database options. These options become a source of confusion, because they run seamlessly with Base. You'll find both flat-file (non-relational) and relational database functionality included. "Flat-file" connection drivers (dBase, Spreadsheet and/or Text) may offer SQL-like functions, but don't expect them to be as extensive as those available when connecting Base to an SQL engine. Some additional function is provided by Base SubForms, which support filtering and ad hoc relationships even among flat-file tables. Beyond these Base functions, Calc 'pivot tables' offer pseudo-queries including aggregate functions, while Calc charting is suitable for reports. So flat-file tables can provide entry-level database functionality, sufficient for many home-projects including a simple contact list, inventory, etc. But when data redundancies become a liability, or the full power of SQL is desired, it's time to connect Base to an SQL engine such as the bundled HSQL database engine. This engine adds a plethora of features, functions, and query options to the mix. So, while you're not limited to the installed back-end database options, everything required to create a complete *Office database application is included with Base. A quick summary of the bundled back-end options include:
- flat-file table (Calc spreadsheet with tabular data, CSV, or dBase file)
- flat-file table queries (dBase file-format generated through Base; single-table query support; one table per file; similar to MS Works)
- flat-file based functions (provided by Base through selected flat-file 'drivers' such as dBase, Text, etc.)
- flat-file filtering and ad hoc relationships (provided by SubForms in Base/Writer/Calc)
- flat-file pseudo queries (provided by Calc through Pivot Tables)
- SQL relational database (multiple related tables and extensive functionality through the included HSQL database engine; similar to MS Access + MS Jet engine).
- Base offers to create an all-in-one 'embedded database' file similar to MS Access. In this configuration, the front-end components (queries, forms, reports and macros) are packaged together with the database files produced by HSQLDB, all within a single Base (.odb) file. Unfortunately, this single-file concept has proven unreliable with Base, leading to rampant data corruption. So it's best to avoid the 'Create a new database' wizard in Base thereby avoiding 'embedded database' files (perhaps with the exception of developing prototypes or distributing examples). Fortunately, Base offers an alternative wizard which produces a robust 'split-database' configuration in which the database itself (tables and user-data) is maintained as a separate file (or files) as necessary for the proper stability. Steps for creating a new 'split-database' utilizing the built-in HSQL database-engine with Base are outlined below.
Java
Do I need Java to use Base?
Not necessarily. Java (JRE) is used to run various wizards, extensions and data sources -- such as the Report Builder extension and the built-in “HSQL relational database engine.” Java is also 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.
Data sources usable without Java include: delimited Text files (CSV), spreadsheets, address books, dBase files, as well as many relational-databases. In addition, tables and views created with MS Access are accessible on the Windows platform. The dBase option is particularly handy for creating flat-file databases through Base, similar to MS Works. For relational database needs, Base connects to a variety of non-Java engines such as PostgreSQL, MySQL, MariaDB, Firebird, Oracle or MS SQL Server. These RDBMS's may be installed locally or on a network for database sharing among multiple users. Base connects to these full-scale RDBMS's in non-Java environments through standard ODBC, or native SDBC, drivers as available. But unlike dBase, these RDBMS's require additional tools for database creation and schema development (DDL) tasks. They also require additional daily chores such as RDBMS start-up and shutdown...so they're not exactly "seamless" with Base. SQLite can fill-the-gap between dBase (flat-file databases) and these full-featured RDBMS options. SQLite is not a full-featured RDBMS, but it can be adequate for simple projects confined to a single-user (throughout the database life-cycle) and with rather basic relational-database requirements (no DATE data-types or date-related searches/functions, etc.). SQLite runs seamlessly with Base, but installation requires third-party drivers, and additional tools for database creation and schema development tasks. Recently, Firebird was chosen as the non-Java alternative to HSQLDB in LibreOffice. So "Embedded Firebird" is now an experimental feature in LibreOffice 4.2+. This integration of a full-scale, relational, database engine holds great promise, but proper implementation could take years because the current design risks data-corruption, while Firebird inherently requires a more advanced database manager than Base currently provides.
Otherwise, with a Java Runtime Environment (JRE) installed (even portably) Base runs well with various Java databases including the built-in HSQLDB. This has some distinct advantages over SQLite, and perhaps over other relational database (RDBMS) options. Like SQLite, Java database-engines are light-weight, fast, cross-platform and run seamlessly (auto start/stop) with Base. HSQL databases are created through Base, while the GUI Table Designer is adequate for most schema development tasks. Installation is a non-issue, since Java engines are completely portable. And Java databases are particularly rich in features by comparison. For example, modern Java databases like HSQLDB 2.x, H2 and perhaps Apache Derby offer advanced features including: ACID transactions, modern SQL syntax, data-typing, custom functions, stored procedures, encryption, hot-backups, and advanced multi-user support (MVCC). These additional features can ease project-development to a degree. For instance Base queries, forms and reports thrive in a full-featured SQL environment, thereby avoiding macros. Something as simple as 'data-typing' enables flexible queries based on date/time fields; something that becomes a nightmare with a weak data-typed engine like SQLite. And as the user-count grows, these Java engines scale modestly in support of multiple, concurrent users. So Java database engines occupy a niche on the desktop and pair well with Base, expanding the scope and potential of Base projects while easing application development. Given the advantages, it's no wonder that HSQLDB 1.8 (released July 2005) was selected over SQLite as the default with Base -- albeit a rather dated/crippled version of HSQLDB by today's standards. But you're not confined to the bundled version/engine. HSQLDB 2.x and H2 are much more advanced and particularly well-suited for Base projects. You'll also find wide support for HSQLDB and perhaps H2 in the Base community forums and other support channels. NOTE: a 32-bit JRE is necessary for Windows and macOS (Mac) environments, where Base is strictly a 32-bit application.
In summary, if you don't have Java (JRE), then the following functionality will not be available:
- You will not be able to create or open all-in-one “embedded database” files.
- You will not be able to run wizards including the table, query, form, or report wizard.
- You will not be able to use the Report Builder extension.
- You will not be able to utilize the JDBC link option.
- You will not be able to create a seamless, portable, cross-platform, full-featured, data-safe, relational-database project as available with Base templates.
Why can't Base load/find my JDBC driver?
OpenOffice.org must know the path to the JDBC driver for a given Java database engine. This path is known as a "Class Path" in Apache 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 Apache 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) must be installed (or available as a portable app) in order to utilize this database engine.
Version 1.8.0.10 (circa 2005) of HSQLDB is integrated with OpenOffice (AOO/LibO).
The integrated JDBC driver supports several HSQLDB operating modes including two with Base.
- In-process ('file mode') provides seamless, single-user access with Base. "Seamless" implies automated database access with no need to start/stop a database server; similar to H2, dBase, and SQLite.
- Client-server ('server mode') provides multi-user access on a network or locally from client-applications such as Base.
Is HSQLDB reliable?
Yes, HSQLDB is a robust, transactional, database engine with ACID properties and a mature code base. These same technologies are at the heart of PostgreSQL, MySQL, Oracle, DB2, MS SQL Server, etc. In addition, HSQLDB employs a transaction log with auto-recovery. An automatic backup file is also maintained if enabled. The end-user can supplement these measures by storing the database files in a folder protected by automatic backup software or cloud-sync services. It's also prudent to run database applications on a computer protected by an 'uninterruptable power supply' (UPS) or on a laptop with the battery installed. These measures avoid loss (rollback) of any buffered data in the event of a power failure. Speaking of buffered data, all transactional database engines employ a write-delay buffer to enhance performance. The buffer can be set to 0 seconds at the expense of write performance. HSQLDB uses a modest 500ms buffer by default, so new data is safely written to disk after a half-second delay. Base sets this write-delay buffer to 60 seconds for all 'embedded databases' files, which is excessive, but most folks can recover from the loss of one minute's productivity in the event of a power failure or similar event. In all cases, buffered data is written immediately to disk upon normal shutdown. Advanced, enterprise-level, reliability measures such as hot backups, clustering and replication, designed for 24/7 availability, are not available with HSQLDB 1.8 -- although hot backups are supported by HSQLDB 2.x, while clustering is oddly-enough a feature of H2 database.
So why should I avoid 'embedded databases'?
In Base parlance, an 'embedded database' is a single-file database. Specifically, this refers to the inclusion of both front-end and back-end database components within a single, zip-archive file. So with Base, an 'embedded database' is simply a zip-archive file (.odb) which includes files generated by OpenOffice as well as user-files generated and maintained by the bundled HSQLDB engine. The contents of this (.odb) file are automatically unzipped to a temporary folder upon opening the (.odb) file in *Office. When the file is "closed," the latest contents are repackaged into a single zip-archive by *Office. This re-packaging process is problematic and commonly leads to file-corruption, beyond the capabilities of the auto-recovery feature built-into *Office. The first signs of a corrupted Base .odb file is a popup dialog offering to open the file with the proper "filter" selection. In many cases the file is recoverable through tedious manual recovery. But much like recovering a crashed hard-drive, the process requires some expertise with file-recovery tools and knowledge of HSQLDB's automatic backup system.
So it's best to avoid these 'embedded database' files whenever possible. This means avoiding the wizard dubbed 'Create a new database' in Base. Thankfully, this particular wizard isn't necessary to create new HSQL databases with Base.
In fact, Base offers a robust alternative to leveraging the 'Connect to an existing database' wizard. The wizard connects Base to various data-sources in a traditional 'split database' architecture. In this configuration the database files remain separate. The Base (.odb) file, in this case, contains only front-end components (connection parameters, stored queries, forms, reports, and macros). The back-end database files are managed exclusively by their respective driver or database engine. Due to the separation of roles at the file-level, a Base crash cannot corrupt the back-end database files. This 'split' configuration is the normal Base configuration with PostgreSQL, MySQL, H2, etc. As expected, connecting Base with HSQLDB in this configuration also achieves the proper data-reliability. Everything we need to generate a 'split HSQL database' is included with Base. But manual setup is tedious, and the resulting database is scarcely portable. Things certainly don't get any easier with PostgreSQL, MySQL, etc. In response, the user-community developed macro-enhanced Base templates. These templates have become the best way to generate a new, full-featured, portable database using Base with the latest HSQLDB engine.
How do I setup support for 'split' HSQL databases in Base?
See: Wizard - Portable 'split' HSQL database template or follow the manual steps below. But understand that these two methods are mutually exclusive.
A macro-enhanced Base template (.odb) as offered in the link above is highly recommended for single-user, split-database access. These templates do not require manual Class Path setup as outlined below. The templates use a macro-derived 'session' Class Path as opposed to a manually-setup 'global' Class Path. So unless you're running a split HSQL database in server-mode (multi-user support), there's no good reason to setup the global Class Path manually in Base. In fact, a global Class Path has the potential to corrupt your legacy 'embedded database' (.odb) files, through inadvertent upgrade of the internal database to HSQLDB 2.3.x. Once upgraded, these 'embedded database' files become unusable. So before proceeding with the following Class Path setup steps, make good backups of all your legacy 'embedded database' files (.odb). You've been warned.
To manually setup a global "Class Path" in *Office, such as for server-mode access to a split HSQL database, follow the steps below. Java database engines are typically packaged as a single zip-archive file (.jar extension). This JAR file will normally contain all the necessary components, including a JDBC driver for external connection purposes. Therefore, Base simply needs to know the path to the engine's JAR file. This one-time setup becomes necessary on each computer used to access a 'split HSQL database' without the use of a macro-enhanced Base front-end (.odb) file, such as when running the database in server-mode.
- Open the Tools → Options dialog
- Select the Java dialog
- Press the Class Path button
- Press the Add Archive… button
- Navigate among your installed programs to locate hsqldb.jar
- To use the built-in version of HSQLDB 1.8.0.10, look for hsqldb.jar in the *Office installation path:
- For AOO or OOo navigate to:
OpenOffice X\program\classes\hsqldb.jar
- For LibreOffice navigate to:
LibreOffice X.x\program\classes\hsqldb.jar
- For AOO or OOo navigate to:
- To use an installation of HSQLDB 2.2.8, look in the associated folder
...\lib\hsqldb.jar
.- Note: Base is incompatible with HSQLDB 2.2.9, so utilize HSQLDB 2.2.8 or the latest HSQLDB 2.3x.
- To use the built-in version of HSQLDB 1.8.0.10, look for hsqldb.jar in the *Office installation path:
- Click: OK twice and re-start OpenOffice while also closing the Quickstarter (taskbar-app) manually as applicable...or simply restart your computer to make sure.
How do I create a new 'split' HSQL database using Base?
See: Wizard - Portable 'split' HSQL database template or follow the manual steps below. But understand that these two methods are mutually exclusive.
To manually create a new split-database from Base:
- Ensure you've setup the Class Path to hsqldb.jar as outlined above.
- Note: The above 'Class Path' procedure must be accomplished on every computer used to access 'split' HSQL databases without employing a macro-enhanced template.
- Download this pre-configured Base front-end file: mydb.file.odb (feel free to rename this file)
- Click the file to open it in Base
- Then click on the Tables icon to create a new 'split-database'
- ...and you're done!
- Note: The provided Base file is configured for "file-mode" (single-user) access to the associated HSQL database. The database files are created automatically in a dedicated folder named 'mydb' as specified in the 'data source URL' within the Base file (mydb.file.odb). The full path to the database is: \users\public\databases\mydb\mydb.*. The database path and name are flexible, but any manual changes must also be recorded in the 'Data-source URL' string as outlined below. "File mode" is a single-user access mode, but we can reconfigure this Base file for "server-mode" access to the same database thereby allowing concurrent, read/write access among multiple users. In any case, a 'split database' consists of two major components:
- 1. A Base front-end file (such as mydb.file.odb) contains the database connection information, plus any Queries, Forms, Reports and Macros that you develop using Base;
- 2. The back-end HSQL database files consisting of four (4) separate files containing your Tables and Data: mydb.script, mydb.properties, mydb.data, mydb.backup.
- Note: These files makeup your portable database application, so they're best maintained in a dedicated database folder. Other supporting files may also be included in the database folder, such as your 'server mode' batch/script files (discussed in the note below). In any case, a properly configured Base "database application" will consist of at least two files including the Base (.odb) file and the database file(s). So while H2 and SQLite utilize a single file per database, when combined with Base, it's generally best to maintain a dedicated database folder per database application.
- Note: The provided Base file is configured for "file-mode" (single-user) access to the associated HSQL database. The database files are created automatically in a dedicated folder named 'mydb' as specified in the 'data source URL' within the Base file (mydb.file.odb). The full path to the database is: \users\public\databases\mydb\mydb.*. The database path and name are flexible, but any manual changes must also be recorded in the 'Data-source URL' string as outlined below. "File mode" is a single-user access mode, but we can reconfigure this Base file for "server-mode" access to the same database thereby allowing concurrent, read/write access among multiple users. In any case, a 'split database' consists of two major components:
To create additional, new databases:
See: Wizard - Portable 'split' HSQL database template or follow the manual steps below. But understand that these two methods are mutually exclusive.
- 1. Download a new copy of 'mydb.file.odb' (feel free to rename this file), or create your own copy using the steps under the 'Alternative' heading below.
- 2. Select Edit → Database → Properties… to access the 'Data source URL' settings.
- 3. Then change only the folder name or the entire path to the 'mydb' database in the 'Data source URL' string.
- For instance, change the path to:
- \users\public\databases\contacts\mydb.*
- \users\public\databases\inventory\mydb.*
- \users\myname\databases\contacts\mydb.*
- Note: You can also change the database name (from 'mydb') in this manner in order to create a new database. While this is logical, it will also affect server mode access (as opposed to file mode access). The server mode start-up and shutdown strings reference the database name ('mydb' in this case). So any change to the database name must also be applied to the start-up/shutdown strings. These strings are complex, and they're not stored within the Base file, so we typically store and run them using a convenient batch/script file. These batch/script files can remain relatively universal/unchanged as long as we keep the database name constant across database folders. Store a copy of these batch/script files in each database folder for server mode access. So as a recommendation, change only the folder name within the 'data source URL' in order to create a new 'split' HSQL database, identified by folder name. Feel free to change the name of your Base (.odb) files as necessary to differentiate database access (change mydb.file.odb to AddressBook.odb perhaps matching the name of the folder).
- For instance, change the path to:
Alternatively you can create this master Base .odb file manually using the 'Connect to an existing database' Base wizard as follows:
- Select: File → New → Database -or- Press: Ctrl + N
- Select Option: Connect to an existing database
- Select Connection type: JDBC
- Press: Next >>
- Data Source URL (file mode): hsqldb:file:<path>/<database name>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
- For example (database folder named 'mydb' with database also named 'mydb'):
- hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
- HSQLDB 2.x supports the wildcard character (~) to specify/derive the current user's folder:
- hsqldb:file:~/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
- For example (database folder named 'mydb' with database also named 'mydb'):
- JDBC driver class: org.hsqldb.jdbcDriver
- Press: Test Class
- Errors may indicate Class Path setup issues
- Errors may indicate JDBC driver class syntax issues, such as a space after the string: org.hsqldb.jdbcDriver
- Press: Next >>
- User name: SA
- Password required: leave un-checked at this stage.
- Press: Test Connection
- Errors may indicate Data Source URL syntax issues
- Errors may indicate database authentication issues. Check username and password.
- Errors may indicate user-access privileges (file or folder not shared) imposed by the operating system and managed by the system administrator.
- Optionally enable database-folder portability using the macro and instructions provided here: Wizard - Create a new split HSQL database
How do I convert an "embedded database" to a "split-database" ?
Under construction...
- In the meantime, see this tutorial for single-user access, or perhaps these steps to run the database in server mode.
How do I edit the Field properties of a 'split database' Table ?
Under construction...
- In the meantime, see the Field Properties workaround.
How do I setup multi-user 'server mode' access to my HSQL database?
To run HSQLDB in 'server mode' and configure clients for multi-user access from Base:
- Ensure you've setup the Class Path to hsqldb.jar as outlined above.
- NOTE: Class Path setup is required on the server and on each client using an identical hsqldb.jar file.
- Ensure HSQLDB is running in server-mode either locally or on a network computer with an accessible database folder.
- Download this pre-configured Base front-end file: mydb.server.odb
- Open it in Base,
- Then click on the Tables icon to access an existing 'split-database' in the same folder (a new database is created automatically if none exists)...
- ...and you're done!
- Note: The provided Base file is configured for "server-mode" (multi-user capable) access to the associated HSQL database. The database files are created automatically in the same folder as the Base file (mydb.server.odb) with the prefix 'mydb'. If a database named 'mydb' is already present in the folder, and that database is currently running in server-mode, then Base will simply be connected to the existing database. For instance, you may have created a database named 'mydb' in that folder using 'mydb.file,odb' which runs HSQLDB automatically in file-mode. It's best to save all server-mode batch/script files used to start-up/shutdown the HSQLDB server in each database folder as well. Also consider this Windows app for server-mode management.
- Note: Any Base front-end file (.odb) created with the 'Connect to an existing database' wizard may be re-configured for 'server-mode' access. This includes both 'mydb.file.odb' and 'mydb.server.odb'. Simply make a copy of the (.odb) file and follow the steps below to re-configure the file. Access these settings in an existing Base .odb file from the menu bar: Edit > Database > Properties... If this option is not available in Base, then your .odb file was created with the wrong Base wizard, so a new .odb file must be created as follows (don't worry, you can extract your existing embedded-database, while migrating your queries, forms, etc. in most cases).
- Alternatively ...you can create this Base .odb file manually using the 'Connect to an existing database' Base wizard as follows:
- Select: File → New → Database -or- Press: Ctrl + N
- Select Option: Connect to an existing database
- Select Connection type: JDBC
- Press: Next >> and fill-in the 'Data Source URL' and 'JDBC driver class' as follows:
- Data Source URL (server mode): hsqldb:hsql:// localhost or IP-address:port /;default_schema=true;get_column_name=false
- For example with HSQLDB running on the local machine: hsqldb:hsql://localhost/;default_schema=true;get_column_name=false
- For example with HSQLDB running on a network computer: hsqldb:hsql://192.168.1.101/;default_schema=true;get_column_name=false
- JDBC driver class: org.hsqldb.jdbcDriver
- Press: Test Class
- Errors may indicate Class Path setup issues
- Each client must have a local copy of hsqldb.jar with the Class Path setup locally
- Errors may indicate JDBC driver class syntax issues such as a space after the string: org.hsqldb.jdbcDriver
- Errors may indicate Class Path setup issues
- Press: Test Class
- User name: SA
- Password required: leave un-checked at this stage
- Press: Test Connection
- Errors may indicate that HSQLDB is not running in server-mode
- Errors may indicate database authentication issues. Check username and password
- Errors may indicate folder access privileges imposed by the operating system and managed by the system administrator
- Errors may indicate network issues: database folder is not accessible from the client computer
- Errors may indicate network issues: Data Source URL syntax reflects the wrong IP address or port for the database server
- Errors may indicate network issues: Router or firewall is blocking the default port 9001 (or 554 if encrypted) to the database server
- NOTE: each client should have a local installation of *Office with Class Path setup as previously outlined. And it's probably best to distribute separate Base (.odb) front-end files to each client in order to maximize stability and flexibility (custom user Queries, Forms, Reports, etc.). Windows clients can even run *Office/Base portable from USB flash drive with confidence (data reliability) in client-server configurations since no critical data is stored in the Base file or otherwise on the flash drive (see LibreOffice Portable & JavaPortable). On the other hand, it may be advantageous to maintain a single, shared, Base (.odb) file exclusively on the database host computer in order to ease administration and distribution tasks overall, particularly in the early phases of production use. Again, the Base file contains only front-end components, which are easily replaced with any old backup. So critical production data is not at risk even if Base crashes in this configuration due to the transactional nature of the HSQLDB engine. All critical production data is maintained on the database host computer (server) in this client-server configuration.
How do I determine which version of HSQLDB is in use with Base?
In Base, open the SQL console: Tools → SQL…
- If you suspect the default HSQLDB 1.8.0.10 engine, then type-in (copy & paste):
Call "org.hsqldb.Library.getDatabaseFullProductVersion"()
- If you suspect HSQLDB 2.x, then type-in (copy & paste):
Select DISTINCT DATABASE_VERSION() as "HSQL Version" From INFORMATION_SCHEMA.SYSTEM_TABLES
Database Conversions
How to convert a table to CSV
- register the database under Tools → Options → OpenOffice Base → Databases
- open a Calc sheet
- press F4
- choose the table in your database
- 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'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 Text Document, 850K) giving you a step-by-step description. Or download it here
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, 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 are 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, 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.