Difference between revisions of "FAQ (Base)"

Revision as of 07:54, 2 May 2012

Java

Do I need Java to use Base?

Not strictly. Base is designed to connect to various types of data sources, some requiring Java to run. And Java is necessary to run wizards throughout OpenOffice.org. But otherwise, Base is fully functional with non-Java data sources like Text files (CSV), dBase files, MS Access databases (Windows only) and most 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 must be run in client-server mode and managed separately from Base. They can be installed locally for a single-user or on a network for concurrent database access among multiple users. SQLite is also supported through ODBC. Like Java database engines, SQLite can be run seamlessly with Base, so there's no additional engine (start/stop) administration involved. SQLite can be a good choice for some Base projects, such as those requiring basic relational database functions. But the feature-set, ease-of-use and SQL-compliance of SQLite are not on par with today's leading Java database engines. Therefore, a Java database engine (HSQLDB 1.8.0) was chosen as the default and is bundled with Base, albeit a dated version of HSQLDB by today's standards.

If you have a Java Runtime Environment (JRE) installed, OpenOffice.org Base offers additional features including support for Java databases through the JDBC link option. HSQLDB, H2 and Apache Derby are the premier Java databases today. These databases are known for their speed, SQL standards compliance, rich features, cross-platform support, small footprint, ease-of-use, and sufficient multi-user support. Like SQLite, these engines run seamlessly with Base (no administration). So given the advantages, Java database engines occupy a niche in desktop applications like Base. They offer all the relevant desktop-features of popular enterprise-level engines, but with better ease-of-use, performance and modern SQL-compliance. 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 experimental Base code to store the HSQL database files inside the Base .odb file. So use these "emebedded database" files are not recommended for production use without an automated backup system. Suffice to say, these "embedded database" files have proven unstable and highly susceptible to file-corruption, unlike a traditional split-file database (Base .odb file + native database files). Base supports split-file databases through the "Connect to an existing database" wizard. Happily, this wizard supports the built-in HSQLDB engine in a seamless, split-file configuration through JDBC.

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 OpenOffice.org 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 Base. To achieve this, go to the Tools > Options > Java dialog, and press the Class Path button. Then use the Browse button to locate the .jar file associated with the Java database engine, and then re-start OOo including the Quickstarter tray-app as applicable (or restart your computer). For example, you can add support for split-file databases using the built-in HSQLDB engine by adding the Class Path to hsqldb.jar which is a file located in the OOo installation path: ...\basis\program\classes\hsqldb.jar.

HSQL DB

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 "HSQLDB 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.