Difference between revisions of "FAQ (Base)"

Revision as of 06:06, 19 June 2012

Java

Do I need Java to use Base?

Not strictly. Base is designed to connect to various data sources, some requiring Java to run. Some wizards throughout OpenOffice.org also require Java. But otherwise, Base is fully functional with other data-sources like delimited Text files (CSV), spreadsheets, address books, dBase files, MS Access files (Windows only; tables/views only) and many relational databases through ODBC or ADO connectivity. These include popular database engines like PostgreSQL, MySQL, MariaDB, Firebird, Oracle, and MS SQL Server. These database engines can be installed locally or to a network for database sharing. And it's worth noting that these engines must be managed separately for everyday tasks like engine start and shutdown...so they're not completely "seamless" with Base; seamless support is exclusive to SQLite and Java database engines.

SQLite is also supported through ODBC in non-Java environments. SQLite can be run seamlessly with Base, so once setup there's no additional engine (start/stop) administration involved. SQLite can be a good choice with Base in non-Java environments for smaller projects confined to a single-user and with relatively basic relational-database requirements.

Otherwise, if you have a Java Runtime Environment (JRE) installed (even portably) OpenOffice.org Base offers additional features including support for Java database engines through JDBC. HSQLDB, H2 and Apache Derby are the premier Java database engines today. These engines are known for their speed, cross-platform nature, small footprint, ease-of-use, rich features, modern SQL compliance, and small-scale multi-user support. So they're small, fast and seamless, just like SQLite, but offer much more function, while expanding the scope and potential of Base projects. This eases development to a degree because Base queries, forms and reports benefit from the additional built-in function provided by the engine without resorting to macros. Considering the depth features and the seamless connectivity with Base, it's hard to justify installation and management of other options including MySQL, PostgreSQL, Oracle, etc. Modern Java engines support all the necessary desktop database features including: advanced data-types; contemporary SQL syntax and functions; multi-user support with fine-grain access control; database encryption; automated LOB store; stored procedures; etc. Thus, Java database engines occupy a niche on the desktop and pair well with Base -- offering superior ease-of-use (seamless; no administration), significantly faster performance (2x-5x; short of billions of records), sufficient scaling (dozens of concurrent users) and the latest SQL-standards (extensive SQL:2008 support). Given the advantages, it's no wonder that HSQLDB 1.8.0 (a Java database engine 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 engine. HSQLDB 2.x and H2 are particularly well-suited for Base projects and find wide support 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 configuration uses some aggressive Base code to redirect and store the HSQL database files inside the Base .odb container file. These files have proven unstable and quite susceptible to data-corruption. So these "emebedded database" files are not recommended for production use, and certainly not without an automated backup system. Base also supports a robust, "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 to create a new, split-database featuring seamless OOo/Base access using the built-in HSQLDB engine as outlined below.

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 database files.
• You will not be able to run wizards including the table, query, form, or report wizard.
• You will not be able to connect to any database offering only a JDBC link option.

.

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 connect Base+HSQLDB for non-embedded (split-file) database use?

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 "Class Path" to the hsqldb.jar file included with OOo, as follows:

• Open the Tools > Options dialog

• Select the Java dialog

• Press the Class Path button

• Press the Browse button to locate hsqldb.jar as found in the OOo installation path: ...\basis\program\classes\hsqldb.jar.

• Re-start OOo 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-file database and/or gain 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,
  • -or- create a new Base .odb file using the 'Connect to an existing database' Base wizard while selecting 'JDBC.'
    • Fill-in the 'Data Source URL' and 'JDBC driver class' as follows:
    • Connection type: JDBC
    • Data Source URL: hsqldb:file:<path to database>/<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
    • 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 database using Base+HSQLDB?

To create a new split-file database and/or gain multi-user (server mode) 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 -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?

• LDAP field mapping
• Outlook/Express field mapping

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.