New Features in OpenOffice.org Base 3.1
- 1 New database features in OOo 3.1
- 2 Base General
- 2.1 Macros in database documents
- 2.2 Easier programmatic access to form/report documents
- 2.3 New configuration setting for pre-specifying per-driver classpaths
- 2.4 Relation design get notified when database structure changed
- 2.5 Relation design now supports self referencing table relations
- 3 Table
- 4 Query
- 5 Form
- 5.1 Form controls: new property: "Text direction"
- 5.2 Form controls: new property "Input Required"
- 5.3 "Empty string is NULL" behavior refined
- 5.4 Check box: modified property
- 5.5 Image controls: scaling the image by keeping the ratio
- 5.6 Image controls: support for document-embedded images
- 5.7 Image controls: can be bound to text database columns, interpreting their content as relative link to the image
- 5.8 Navigation Toolbar: new item "Refresh Control"
- 6 Report
New database features in OOo 3.1
Compiled from features dba openoffice org mailing list mails, and referenced specifications.
Macros in database documents
Database documents (.odb) now allow to embed macros and scripts, in exactly the same way the other OOo document types already allow.
Up to now, macros can be embedded into sub documents of a database document, that is, into forms and reports (which technically are Text documents), it will change to one place, database document.
This means, all macros from forms moved into main part of database, assigning macros to elements in a form document will be possible with macros from the database document only, not with macros from the form document. Macros can be run from either the document itself, or any of its sub components: forms, reports, table design, query design, relation design, table data view. Customization of macro can be done into a toolbar, or a menu, of any sub component. When recording a macro in a form or report, the final dialog which lets you decide where to store the macro will not list the form/report anymore, but the database document.
Database documents created with version prior to OpenOffice.org 3.1 might contain forms or reports with embedded macros or scripts. Since this is prohibited, a migration path is necessary.
The goal is to transform documents so that all the macros and scripts are in the database document, this cannot be done automatically.
When the user simply opens an "old" document containing macros and/or scripts in the sub documents, nothing except compatibility warning box came up, when first time opened that file. Until the user migrated the macros, the database document, and all sub documents, will behave as if the feature which this spec is about were never implemented. In particular, the user is free to create additional or modify existing macros in her forms and reports, to customize macros from sub documents into her toolbar/menu, to bind those macros to events in the sub documents, and so on.
Introduced new menu item, called "Migrate Macros ...", located in the top-level menu "Tools", immediately below the existing "SQL ..." item.
When the user selects this menu item, a wizard will guide her through the migration, which effectively consists of the following steps:
- close all sub components of the database document, namely forms, reports, and any open designers
- backup the database document
- migrate the scripts and macros while displaying a progress bar
- show a summary
For a detailed description of the feature, you're encouraged to read the specification:
Easier programmatic access to form/report documents
Programmatic access to forms/reports contained in database documents is easier than ever before:
ThisDatabaseDocument.FormDocuments.getByName( "form name" ).open
ThisDatabaseDocument.ReportDocuments.getByName( "report name" ).open
will open the form "form name", or the report "report name", in the very same way a double-click onto the form in the document UI would do, i.e. doing all the proper knittings to the document UI.
New configuration setting for pre-specifying per-driver classpaths
There's a new setting in the org.openoffice.Office.DataAccess configuration module which allows to specify, on a per-driver basis, a classpath to use when searching for a JDBC driver.
An administrator of a shared OOo installation can use this setting to pre-configure the installation, without all users having the need to add the respective .JAR file to their own Java options.
The complete path of the new configuration node is
An exemplary configuration fragment:
<node oor:name="com.mysql.jdbc.Driver" oor:op="replace">
Relation design get notified when database structure changed
The relation design now detects when a new table will be added and also when new columns will be added. The "Add Table" dialog also gets notified when a new table was added.
Relation design now supports self referencing table relations
The relation design now supports the creation of relations which allows that the referencing table is also the referenced table.
Relative path for file based databases
At the moment, when you create a file-based database (such as dBase or Spreadsheet), the URL to the files (let's call it the "data URL") is stored in an absolute manner - that is, something like "[../../../foo/bar file:///c:/foo/bar]".
As a result, when you move the database document (the .odb file), together with the underlying data, to another machine, you need to either duplicate the file structure on this target machine, or to adjust the settings for the database.
Now it is possible that the path is stored relatively when the option "Save URLs relative to file system" setting in the "Tools->Options->Load/Save" dialog is checked.
Table design handles primary key differently for hsqldb
This is specific for hsqldb only.
When the user adds a auto increment column this one gets automatically the primary key of the table. When the user after wards removes the primary key flag, then the autoincrement flag will also be removed.
Hsqldb only supports one auto increment column which then must also be the only primary key column.
PS: A primary key consists of more than one column which isn't the fact for hsqldb when an auto increment column is used.
Views opened for editing are automatically put into "Run SQL Directly" mode
When you open an table view for editing its constituting SQL command (which is a feature currently supported for embedded HSQLDB only), then the query editor is automatically put into the "Run SQL command directly" mode.
Parameters recognized in function argument lists
OOo's parser has the ability to recognize parameters in function argument lists. That is, a query like
SELECT CONCAT( :C, "name" ) FROM "name" will now, when executed, properly present the dialog asking the user for parameter value input.
Query design: SQL syntax extended
The SQL parser now recognize the TRIM command correctly.
It now also supports
TRIM( FROM <COLUMN_NAME> )
as an abbreviated form of
TRIM( BOTH FROM <COLUMN_NAME> )
This functions, remove leading and trailing blanks from a string.
SQL syntax highlighting
Added support for SQL syntax highlighting for the Base SQL view as well as Tools-SQL...
The used font for the SQL view will respect the same settings as HTML and BASIC IDE, all used colors can be configured
Form controls: new property: "Text direction"
All form controls support a new property "Text direction", which controls, well, the text direction of the control. In languages where people uses right-to-left directed text like in Arab, Hebrew, Hindi etc., forms will look strange if they need to use left-to-right layout control forms.
New property can be found on Properties, General tab below “Label field”, when CTL support enabled in language settings.
See the specification for details:
Form controls: new property "Input Required"
All form controls which can be bound to a database column (i.e. have the "Data field" property) now have a new property called "Input required". New property can be found on Properties, Data tab, below the "Empty string is NULL"
This property controls whether or not the input of this field is checked against being empty (NULL). It is evaluated for controls which are bound to a database field which is defined as required (i.e. Which is not allowed to contain the special NULL value), immediately before the current record of the form is to be written into the database.
If the property is set to "Yes", and the field contains no input when the current record is to be written to the database, then an error message will be shown to the user, and the respective control will be focused afterwards. Note that this is the known behaviour so far – the property defaults to "Yes" so that newly created controls behave as they would do in previous OOo versions.
If the property is set to "No", and the field contains no input when the current record is to be written to the database, then this is ignored. It's up to the underlying database to either reject the update, or fill the respective column with a server-side default value.
If the "Form data input checks for required fields" option in the advanced settings of the database document (Edit / Database / Advanced Settings ...) is *not* checked, then the "Input required" property for all controls in all forms in this database document does not have any effect, since the document-wide setting overrules the per-control settings.
If the "Data field" is not set (i.e. empty), then "Input required" is disabled, since it would be evaluated for bound controls only, anyway.
If the "Empty string is NULL" is set to "No", then "Input required" is also disabled, since "Empty string is NULL" being "no" implies that when the user does not enter any value in the control, then an empty string, instead of the dedicated value NULL, is written, so there's always a non-NULL value no matter the user's action.
"Empty string is NULL" behavior refined
The behavior of form controls whose "Empty string is NULL" property is set to "Yes" has been refined. This change might make existing forms behave slightly different than before, but certainly more expectation-conformant now.
First, the property is now also respected when you never touched the respective control before saving the record. That is, imagine a control which has this property set to "Yes", this way declaring that if the control contains an empty string, then this should be propagated to the database as (the dedicated) NULL value.
Formerly, when you moved to the insertion row of the form, so the control was initially empty, entered some data into other controls of the current record, and saved the record without actually touching the first control, then it actually updated an empty string. Now, with the change, it updates NULL, as this is what "Empty String is NULL" = "Yes" requests.
Second, when a control was bound to a database column which was declared as NOT NULL, then the control *always* updated an empty string instead of NULL, no matter what its "Empty string is NULL" property requested. Effectively, this killed server-side defaults of database fields, as such defaults are only applied when the field is NULL. Now, with the change, controls always update NULL in such a setup, this way enabling server-side defaults.
Check box: modified property
Check box columns in grid controls now have the "Tristate" property, which controls whether or not the "indetermined" state is allowed for the check box, as known from ordinary check box form controls.
Image controls: scaling the image by keeping the ratio
Image form controls in documents got an additional mode for scaling the image they display.
Previously, you could control the scaling by setting the "Scale" property to "Yes" or "No" only, where "Yes" implied an anisotropic scaling, i.e. one which distorted the image's dimensions.
Now, the "Scale" property allows the values "No" (same as before), "Keep Ratio" and "Fit to Size" (equivalent to the old "Yes"). When "Keep Ratio" is selected, the image is still scaled up or down to match the control dimensions, but it's ratio is aspect kept constant.
This is especially useful for controls where the designer of the document does not know, at time of designing the document/control, the dimensions of the to-be-displayed images. In particular, this is useful for image controls in database forms, displaying images obtained from the database.
Image controls: support for document-embedded images
Image controls can now be bound to images which are embedded in the document where the control lives in.
More precise, when you select an image to be displayed at an image control, the "Link" option in the file picker is not disabled anymore.
When you uncheck the option (the default is "checked", to mimic the legacy behavior), then the image is displayed in the control, and upon saving the document, it's embedded in the document itself.
This way, you can create documents with image controls which are exchangeable with other people/installations/platforms, which formerly was much more difficult due to the need to also copy the images, and place them in exactly the same location as on the originating machine (which often is simply impossible).
Image controls in database forms can now be bound to text columns. Formerly, you could only bind them to columns whose content could reasonably be interpreted as binary (BLOB etc.). Now, when you select a text database column as source for the image control, then it will interpret the content of the respective column's content as URL, and load and display the image pointed to by this URL. Also, the URL might be relative to the document which the image control is embedded into.
In the Form Navigation Toolbar (the one used as soon as you have an alive form filled from a database), there's a new item, called "Refresh Control", located right after the "Refresh" item.
This new item is enabled if and only if a list or combo box control has the focus. When pressed, the item list of the control is refreshed.
This is particular interesting for lists based on the content of a table (or query) of the database, when some instance outside the form did changes to this table/query's data. In this case, you can reflect those changes in the control's item list by using the "Refresh Control" function.
New short cuts for Sun Report Builder
The Edit menu now contains a "Select All" entry.
Edit>Select All -> Select All (CTRL+A)
Select all Labels Select all Formatted Fields Select Report
Automatic binding of first table when opening a new Report
When creating a new report in a database, the report will be bound to the first table from the database. Additionally the Add Field dialog will open with all available fields.
Report output format now also available in the property browser
The property browser now shows the selected output format for a report on the Data page, last item under Filter, when it is selected in the SRB.
Currently available formats are:
- ODF Text Document
- ODF Spreadsheet
Add Field dialog supports sorting and insert toolbar
The Add Field dialog now has a toolbar above the listbox for the fields. The toolbar allows to sort the fields ascending and descending as well to remove the sort order and restore origin order from the source (table,query). Additional the toolbar contains an "Insert" entry which allows to insert the selected fields into a report section. Multi selection of fields is also supported.
Function Autopilot from inside the SRB
The Function Autopilot which is used in the spreadsheet can now be used inside the Sun Report Builder.
The Autopilot can be started from:
- the data field ( formatted field and image control)
- the conditional print expression, on general tab.
- the formula
- the initial value
The Autopilot shows all functions which are supported by the report engine.
The documentation for the SRB can be found here:
The functions description can be found here: