Difference between revisions of "FAQ (Base)"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (How do I setup 'split database' support for HSQLDB in Base?)
m (How do I setup 'split database' support for HSQLDB in Base?: (checkpoint save))
Line 117: Line 117:
 
*** For LibreOffice navigate to: <code>LibreOffice X.x\program\classes\hsqldb.jar</code>
 
*** For LibreOffice navigate to: <code>LibreOffice X.x\program\classes\hsqldb.jar</code>
 
**To use an installation of [http://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_2/hsqldb-2.2.8.zip/download HSQLDB 2.2.8], look in the associated folder <code>...\lib\hsqldb.jar</code>.
 
**To use an installation of [http://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_2/hsqldb-2.2.8.zip/download HSQLDB 2.2.8], look in the associated folder <code>...\lib\hsqldb.jar</code>.
***Note: Base is [http://forum.openoffice.org/en/forum/viewtopic.php?p=252960#p252960 incompatible with HSQLDB 2.2.9], so for now utilize HSQLDB 2.2.8. HSQLDB 2.3x is expected to be compatible with Base again.  
+
***Note: Base is [http://forum.openoffice.org/en/forum/viewtopic.php?p=252960#p252960 incompatible with HSQLDB 2.2.9], so utilize HSQLDB 2.2.8 or the latest [http://hsqldb.org/ HSQLDB 2.3x].  
  
 
*Click: OK twice and re-start *Office while also closing the Quickstarter (taskbar-app) manually as applicable...or simply restart your computer to make sure.
 
*Click: OK twice and re-start *Office while also closing the Quickstarter (taskbar-app) manually as applicable...or simply restart your computer to make sure.

Revision as of 17:12, 11 February 2013


Base

Is Base a database?

Not technically. Base serves several roles within the *Office suite (AOO / LibO). But in the final analysis Base, itself, is nothing more than a front-end. For instance:

  • Base is a database connection tool. Base generates a connection file, which then serves as a bridge between the data source and the *Office suite -- including Writer (mail-merge, forms), Calc (pivot tables, graphs, forms) and Impress (dynamic slide data, graphs). To provide this function, a Base wizard is used to create a connection file (.odb). The resulting (.odb) file is then 'registered' in *Office for universal access. Simply press the 'F4' key from any *Office application to view all registered data-sources. Supported data sources include:
    • spreadsheets (tabular data),
    • delimited text files (CSV),
    • popular address book formats,
    • dBase formatted tables/files, and
    • SQL relational databases.
  • 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:
  • 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 installed along with Base. The ' Connect to an existing database ' wizard is used to connect to a database option (dBase, Spreadsheet, Text, JDBC). Upon connection, a new database may be created and managed through Base. For example, if we select the included 'dBase' option, Base can instantly be used to create dBase tables (dBase file format). The 'Spreadsheet' option allows Base to be connected to an existing Calc 'spreadsheet' or other delimited 'Text' file (.csv) containing tabular data. These "flat-file" drivers (dBase, Spreadsheet and Text) may offer some SQL-like functions, although they're not as extensive as those provided by an SQL engine. In addition to these functions, Base SubForms can provide table filtering, or be used to create ad hoc relationships between multiple tables (flat-file or relational). Beyond Base functions, Calc 'pivot tables' offer pseudo-queries including aggregate functions. For relational database needs, Base can be connected to the included SQL database engine (HSQLDB) using the 'JDBC' driver option (see the 'split database' setup steps below). An SQL engine adds a plethora of features, functions, and query options to the mix. So the installed *Office package includes several options that may be used in the development of a complete, *Office-based application. In summary, the bundled 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 new database' wizard in Base which produces these '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 (tables and user-data) is stored in a separate file (or files). As it turns out, separate front-end (Base) and back-end (database) files are critical for database stability, even with MS Access. Steps for creating a new 'split-database' utilizing the built-in HSQL database-engine with Base are outlined below.


So when paired with a bundled data-source Base can certainly be mistaken for a seamless "database" application. But in reality, Base contributes only connection- and front-end functionality. This distinction is easily blurred by the included database options (flat-file drivers and the relational HSQLDB engine) which are installed with *Office.

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.


Data sources usable without Java include: delimited Text files (CSV), spreadsheets, address books, dBase files, as well as many relational-database options. In addition, tables and views created with MS Access are accessible on the Windows platform. The dBase option is particularly handy for creating non-relational, 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 or HSQLDB, these RDBMS's require additional tools for database creation, schema development (DDL) tasks, and for daily chores such as engine start-up and shutdown...so they're not exactly "seamless" with Base. SQLite is another relational-database option with Base in non-Java environments. SQLite is not a full-scale RDBMS, but it can be adequate for smaller projects confined to a single-user (throughout the database life-cycle) and with rather basic relational-database requirements (no DATE data-types, etc.). Like the full-scale RDBMS options, SQLite installation requires third-party drivers, and additional tools for database creation and schema development (DDL) tasks. But after setup an SQLite database runs seamlessly with Base, so there's no daily administration (start/stop) involved.


Otherwise, with a Java Runtime Environment (JRE) installed (even portably) Base runs seamlessly with various Java databases including the built-in HSQLDB. This has some 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. But Java databases enjoy broad support by the Base GUI including most database creation (DDL) and data manipulation (DML) tasks. And Java databases are particularly rich in features. 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. And as the need arises, 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 H2 in the Base community forums and other support channels.


And lastly, with Java (JRE) installed, Base supports the all-in-one "embedded database" leveraging the bundled "HSQL database engine." Simply choose "Create a new database" in the "New Database" wizard ( File | New | Database ). But *CAUTION* is in order. This particular option uses some aggressive Base code to redirect and store the HSQL database files inside the Base .odb container file. This code has proven unstable and responsible for data-corruption. So these all-in-one files are not recommended for production use, and should only be used in a protected folder utilizing an automated backup system with versioning. The common remedy re-configures Base with HSQLDB as a traditional "split database" in which the 'front-end' Base file (.odb) remains separate from the 'back-end' database files. You can use the "Connect to an existing database" wizard (JDBC option) to create a new, split database featuring seamless *Office/Base access using the built-in HSQLDB engine as outlined below.


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 utilize the JDBC link option.
  • You will not be able to utilize, seamless, Java relational-database options such as HSQLDB 2.x and H2.

.

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 *Office.

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 *Office and close Quickstarter (tray-app) as applicable...or simply restart your computer

HSQLDB

What is HSQLDB?

HSQLDB is an cross-platform, SQL 'relational database management system' (RDBMS), 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 *Office (AOO/LibO).

The included JDBC driver supports several HSQLDB operating modes including two with Base.

  • In-process ('file mode') provides seamless, single-user access (no database server necessary; just like SQLite and dBase) with Base.
  • 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 maintains a backup file by default, and employs a transaction log with auto-recovery. 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 data-critical applications on a computer protected by an 'uninterruptable power supply' (UPS) or on a laptop with the battery installed, to avoid losing (rollback) buffered data in the event of a power failure. All transactional database engines employ a write-delay buffer (which can be set to 0) to enhance performance. HSQLDB uses a 500ms buffer by default, so new data is safely written to disk after a half-second delay. But in the case of an existing HSQL database that was extracted from an 'embedded database' file in Base, the delay is set to 60 seconds by default; this delay setting is excessive under all conditions and is easily changed by the end-user, but most folks can recover from the loss of one minute's productivity in the event of a power failure or similar event. 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.


Base, on the other hand, has issues. Specifically, the 'New database > Create a new database' wizard in Base generates an all-in-one 'embedded database' file utilizing the built-in 'HSQL database engine.' In this case, the database data-files are stored ('embedded') inside the Base (.odb) file. So Base becomes the file-host for critical data. While convenient for database portability, these all-in-one files cannot be trusted with critical production data due to Base instabilities. The reason lies in the make-up of a Base (.odb) file, which is actually a zip-archive containing many files and folders. Upon access, the file is un-zipped by Base to a temporary folder. If the database data-files are present, Base then redirects all HSQLDB data storage and retrieval to the temporary file folder. This could work in theory, but any glitch (Base bug) in the process can lead to file corruption. Tedious, manual recovery is often possible, but it's really best to avoid these 'embedded database' files altogether when possible. The issue can hardly be blamed on Java or HSQLDB -- although debugging is complex. Hypothetically, this 'embedded database' design could also corrupt the database files of Oracle, PostgreSQL, MySQL, etc. But HSQL is among a small-handful of engines that are flexible enough to support this 'embedded' configuration. So this issue with Base in no way implies that HSQLDB is a poor choice as your database engine. You'll find it faster, better integrated (with Base), better supported in Base forums, sufficiently-featured (perhaps particularly HSQLDB 2.x), easier to setup, and easier to manage on a daily basis than most other SQL engines. So you'll do well to consider HSQLDB (or H2) over other SQL database options with Base when you have the option. You'll simply need to utilize a more traditional, and proven, configuration with Base.


So thankfully, Base also offers a robust alternative to the all-in-one 'embedded database' file. The 'New database > Connect to an existing database' wizard creates a traditional 'split database.' In this configuration the database files are external to the Base (.odb) file. The database files are accessed exclusively by their respective driver or database engine. The Base (.odb) file is left with only front-end components (connection parameters, stored queries, forms, reports, and macros). Due to the separation of roles at the file-level, Base cannot corrupt the actual database by crashing. This 'split' configuration is the normal Base configuration with PostgreSQL, MySQL, H2, etc. As expected, connecting Base with HSQLDB in this configuration also achieves proper data reliability. Everything necessary to run Base with HSQLDB in this configuration is conveniently included with Base. And note that HSQLDB creates a new database automatically, as necessary (as does H2), so don't let the name of this wizard discourage you. This is not necessarily the case with other SQL database engines (MySQL, SQLite, etc.) which will require alternate database management tools in order to create and structure new databases. In addition, most other database engines (MySQL, PostgreSQL, Firebird, Oracle, etc.) require separate database server management for daily/session start-up/shutdown tasks. HSQLDB, H2, and SQLite are uniquely managed (started/shutdown) by Base in single-user mode.

How do I setup 'split database' support for HSQLDB in Base?

Create a new "Class Path" in *Office as outlined 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 database' using JDBC. So to connect Base with HSQLDB, begin by creating a new "Class Path" in *Office specifying the location of hsqldb.jar, as follows:

  • 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.org X.x\basis\program\classes\hsqldb.jar
      • For LibreOffice navigate to: LibreOffice X.x\program\classes\hsqldb.jar
    • To use an installation of HSQLDB 2.2.8, look in the associated folder ...\lib\hsqldb.jar.
  • Click: OK twice and re-start *Office while also closing the Quickstarter (taskbar-app) manually as applicable...or simply restart your computer to make sure.
    • NOTE: While it is possible to setup multiple Class Paths to different JDBC drivers (or Java database engines), it is not possible to support two different versions of HSQLDB simultaneously (1.8 and 2.x) using a single installation of *Office. Although a special Base edition of HSQLDB 2.3x is expected to eliminate this limitation. Otherwise, this is an important consideration because HSQLDB 2.x support effectively disables Base support for the built-in HSQLDB 1.8.0.10 engine, and its legacy 'embedded databases' files. 'Split databases' based on HSQLDB 1.8 will run with version 2.x support enabled (by Class Path setup), but these databases will be upgraded automatically upon access with Base. This database upgrade process is generally safe but non-reversible so backup your database folders before experimenting with HSQLDB 2.x. It is possible to install multiple versions of AOO/LibO as necessary to maintain one-or-the-other for legacy 'embedded database' file support.

How do I create a new 'split' HSQL database using Base?

To 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 a 'split' HSQL database.
  • 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, it's generally best to maintain a dedicated database folder per database application with Base.


To create additional, new databases:

  • 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 entire path to the 'mydb' database in the 'Data source URL' string.
    • 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).


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
  • 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 user name and password.
    • Errors may indicate user-access privileges (file or folder not shared) imposed by the operating system and managed by the system administrator.

How can I convert an "embedded database" to a "split-database" ?

Under construction...

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.
  • Download this pre-configured Base front-end file: mydb.server.odb
  • Open it in Base,
  • Then click on the Tables icon to create an empty 'split-database' in the same folder...
  • ...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 migrate/transfer your existing database, 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
      • Press: Next >>
      • 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 user name 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 portably 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 OpenOfficePortable or LibreOffice Portable & JavaPortable). On the other hand, it may be advantageous to maintain a single, shared, Base (.odb) file on the database host computer (or otherwise) in order to ease administration and distribution tasks overall, especially 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.
    • NOTE: A database 'server' does not imply a specialized server operating system. Any available networked computer capable of running *Office and Java will do as the database host (shared folder and HSQLDB server). A modern laptop with battery can be a good choice due to battery backup automation, while offering both wired and encrypted wireless access. User authentication is best handled by the database engine (RDBMS), with true security requiring protocol- and database-encryption. These features and more are available with HSQLDB 2.x, H2, and Apache Derby, as well as with enterprise-oriented options listed above.

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:
    • Call "org.hsqldb.Library.getDatabaseFullProductVersion"()
  • If you suspect HSQLDB 2.x, then type-in:
    • Select DISTINCT DATABASE_VERSION() as "HSQL Version" From INFORMATION_SCHEMA.SYSTEM_TABLES

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?

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.

Personal tools