So what is the 'gotcha' if I specify the location of a new HSQLDB.jar file in the class path?

So what is the 'gotcha' if I specify the location of a new HSQLDB.jar file in the class path?

The problem is that any .odb file that contains an embedded HSQLDB database will be corrupted if it is opened while the location of a newer HSQLDB.jar archive is specified in the global class path setting. That occurs because Base will use the newer database engine if the global class path points to it. The new version of HSQLDB will immediately and silently update any old HSQLDB database that it encounters, so if a .odb file containing a HSQLDB 1.8 database is opened and anything causes the database inside the file to be read, it will be updated. The next time Base opens the .odb file, Base (not HSQLDB) will notice that the database files are no longer in the format used by HSQLDB 1.8 and will conclude that a newer version of OpenOffice must have somehow created the .odb file, so it will refuse to open it. This only happens when Base opens a .odb file containing an embedded database using a version of HSQLDB that is 2.0 or later.

The problem can be avoided using a document macro to set the Java driver class path for each document that uses the newer HSQLDB engine, rather than setting the class path globally. This uses a property that is specific to an individual database document (.odb file), so it will not apply when a different .odb file (potentially containing an embedded database) is opened. However, the alternate class path property can not be set manually; a macro must be used as shown below:

 ThisComponent.DataSource.Settings.JavaDriverClassPath = _
 "file:///C:/Program%20Files/HyperSQL%20Database/hsqldb-2.3.0/hsqldb/lib/hsqldb.jar"

The JDBC driver class (ThisComponent.DataSource.Settings.JavaDriverClass) and datasource URL (ThisComponent.DataSource.URL) can be specified (manually or programmatically) and the connection can then be made to the back-end database. Windows users should note that the Java driver class path must be specified as a URI, with forward rather than backslashes and with spaces encoded as "%20". The macro only needs to be run once for each databasedocument that requires the setting because it is retained inside the .odb file.

Of course, if it is known that OpenOffice will never be used to open a .odb file containing an embedded database, the global class path can be used, instead.

[[Category:Documentation/FAQ/<INSERT FAQ SECTION>]]