FAQ (Base)

From Apache OpenOffice Wiki
Revision as of 18:04, 1 November 2012 by Dacm (Talk | contribs)

Jump to: navigation, search


Base

What is Base?

As you may know, Base serves multiple duties within the *Office (*OO) suite -- primarily referring to the current Apache OpenOffice.org (AOO) and LibreOffice (LibO) suites:

  • 1. Base enables external data integration across the entire *Office suite including Writer (mail-merge, forms), Calc (pivot tables, graphs, forms) and Impress (dynamic slide data, graphs). To provide this function, a connection file (.odb) must be created in Base and 'registered' in *Office for central access from all applications.
  • 2. Base will also act as a front-end providing data definition, as well as data manipulation services to a connected data source. Note that some data sources are read-only through Base. In this role, Base provides table views, a visual data-relationships designer, a descent visual query builder, a powerful form designer, a rudimentary reports designer, and a relatively cryptic macro development environment. Various extensions compliment this list nicely. In addition to these visual facilities, a rudimentary command-line SQL console rounds-out the Base front-end package, such that it is possible (although not necessarily recommended) to use Base for all data definition tasks.
  • 3. Base is also a complete database application. While Base, itself, is never anything more than a front-end, the magic of object-oriented programming allowed Base developers to provide both flat-file (non-relational) and relational database functionality within the Base package. So with the inclusion of two small files, Base gains the ability to produce dBase tables (flat-file, non-relational) and a full-blown relational database (multiple related tables through the included HSQL database engine).
  • 4. Base can produce an all-in-one file database. While the dBase option produces one file per table, the Base plus HSQLDB option produces a single file by default in Base. In this configuration, all the front-end components (queries, forms, reports and macros) are combined with the native HSQL database files, all packaged within a single Base (.odb) file. Unfortunately, this all-in-one front-end plus data design has proven faulty, leading to rampant data corruption. The design caches and redirects the input/output of the HSQL database engine using faulty Base code, so the user community has all but abandoned use of this all-in-one file (with the exclusion of prototyping and examples). Fortunately, Base can be reconfigured through a few simple steps (outlined below) to utilize the powerful HSQLDB seamlessly with Base.

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. 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 and DDL 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 OOo/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 OOo.

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

HSQLDB

How do I setup Base+HSQLDB for non-embedded database access?

Java database engines typically include a built-in JDBC driver for external connection purposes. HSQLDB is no exception. To connect Base with HSQLDB externally, simply add the location of the associated hsqldb.jar file to the "Class Path" within *OO, as follows:

  • Open the Tools > Options dialog
  • Select the Java dialog
  • Press the Class Path button
  • Press the Browse button to locate hsqldb.jar
    • To use the built-in version of HSQLDB 1.8.0.10, look in the *OO installation path: ...\basis\program\classes\hsqldb.jar.
    • To use a compatible installation of HSQLDB 2.x, look in the associated folder ...\lib\hsqldb.jar.
  • Re-start *OO and close Quickstarter (taskbar-app) as applicable...or simply restart your computer

How do I create a new, non-embedded database for Base+HSQLDB?

To create a new split-database with seamless (single-user) access from Base:

  • Ensure you've setup the Class Path to hsqldb.jar as outlined above...
  • Download (mirror) a pre-configured Base front-end file, open it in Base, then click on the Tables icon to create a database.
    • OR ...you can create this Base .odb file manually using the 'Connect to an existing database' Base wizard.'
      • Fill-in the 'Data Source URL' and 'JDBC driver class' as follows:
      • Connection type: JDBC
      • Data Source URL: hsqldb:file:<path>/<database name>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
        • For example:
          • 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
      • User name: SA
      • Password required: leave un-checked at this stage
    • 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
    • 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 imposed by the operating system and managed by the system administrator

How do I setup multi-user access to my HSQL database?

To run HSQLDB in 'server mode' and configure 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 -or- Create a new Base .odb file using the 'Connect to an existing database' Base wizard while selecting 'JDBC.'
    • NOTE: An existing Base front-end file -- used to access an existing, non-embedded, HSQL database as created previously (above) -- may be re-configured for multi-user access. Simply make a copy of the (.odb) file and follow these steps to re-configure the file. Access these settings in Base > Edit > Database > Properties...
  • Fill-in the 'Data Source URL' and 'JDBC driver class' as follows:
    • Connection type: JDBC
    • Data Source URL: 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
    • User name: SA
    • Password required: leave un-checked at this stage
  • 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
  • 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 is blocking default port 9001 (or 554 if encrypted) to the database server
  • Note: each client should have a local installation of OOo 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 OOo/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). But for that matter, central access to a shared Base (.odb) file can ease administration and distribution tasks as necessary, especially in the early phases of production use. Again, the Base file contains only front-end Forms and such, 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 split-file, client-server configuration.
  • Note: A database 'server' does not imply a server operating system. Any available networked computer capable of running OOo 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.

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 OOo, but I can't update or insert any data - why?

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

  • OOo 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.
  • OOo 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 OOo. 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", "MySQL (ODBC)" ...

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

Why does my table show the wrong number of records?

Base cannot know the number of records until its displayed all of them. This only happens while you scroll through your table, so initially, Base will tell you a smaller number than how many records you actually have. To indicate this, Base will show a small asterisk (*) behind the record count. That is, a number 23 means there actually are 23 records in your table, where as 23 (*) means there are 23 known records, but there are more which Base does not yet know about.

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 OOo'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.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=FAQ_(Base)&oldid=208943"
Personal tools