How to use embedded and external HSQL with the same profile

[Villeroy]

The following code snippet makes it possible to store the hsqldb class path in the database document which is connected to an external HSQLDB 2.8 while a missing class path in the general Java settings enables the built-in hsqldb.jar (1.8) for embedded databases.

-- Store this code in a global container
-- Adjust the cPath constant to your external hsqldb.jar
-- Run it against your .odb connected to an external hsqldb

Code: Select all   Expand viewCollapse view

Sub Snippet
Const cPath = "/usr/local/lib/hsqldb/lib/hsqldb.jar"
  Dim oDataSource As Variant
  Dim oSettings As Variant
sURL = ConvertToURL(cPath)
  oDataSource = ThisComponent.DataSource
  oSettings = oDataSource.Settings
 
  oSettings.JavaDriverClassPath = sURL
End Sub

[DACM]

This is very interesting. Overall, you're saying that we can use ThisComponent.DataSource.Settings.JavaDriverClassPath to access an HSQLDB 2.x database without a global class path setup, so the *Office installation maintains support for legacy embedded db files using the built-in HSQLDB 1.8.0.10. Is that correct?

Does this macro need to be in a global container, or can it be embedded in the Base document alone for portability?

[Villeroy]

With the macro embedded in the Base document you should use ThisDatabaseComponent instead of ThisComponent.
For Windows users I added a call to ConvertToURL so the normal system path can be entered without URL encoding.

[Greengiant224]

Villeroy 5 (gold) *

Could I just add the following comments to your macro, this should make it easier for users to understand.

Const cPath = "/usr/local/lib/hsqldb/lib/hsqldb.jar" '<< path to version 2.x jar

oDataSource = ThisComponent.DataSource '<< use when macro is stored in global container i.e. My Macros
'oDataSource = ThisDatabaseComponent.DataSource '<< use when macro is embedded in db

Well done.

[DACM]

+1 huge kudo's to Villeroy for this discovery.

My extended reply here.

...

[Villeroy]

Thank you for your compliments.
I'm writing a macro with a little GUI to make this hidden feature accessible and extract an embedded database with forms. I have no idea how to handle reports without re-implementing the whole reporting tool.

[Villeroy]

Extension installing a set of Basic macros with a dialog to extract and connect HSQL databases.

So far I've got:
1) A routine to extract a bundled HSQLDB (inclding the "native" embedded type) from a Base document into a sub-directory "database". Then it connects your database document to the extracted backend. This does not need to be an embedded HSQLDB. You may simply dump your own HSQLDB files into a "database" folder of the document archive using your favourite zip tool. You may also drop your hsqldb.jar into a "driver" folder of the document archive to ship your database with an appropriate driver.

2) A routine to wrap a HSQL back-end database and a driver (hsqldb.jar) into a database document.

3.1) A routine to specify the driver path for a document's external jdbc connection. For the above outlined reasons this is particularly useful with HSQL connections when you want to run external and embedded databases. This may be useful with other jdbc connections as well. I successfully tested individual driver settings with H2 databases. Please check out with jdbc:mysql or whatever jdbc connection. Remove your jdbc driver path from the global class path settings and store the path in your document instead.
3.2) A routine to clear this driver path setting so the driver path is read from the global class path setting again.

4) Another routine to connect any type of HSQLDB front-end (embedded or not) to some existing back-end by pointing to the back-end's *.script file.

5) I added a text box for a property which saves the connection password in the frontend. Of course, this opens a security hole but you are the only one to know how important or unimportant your database privileges are.

All this is stuffed into one of my ugliest dialogs with code written in plain, stupid Basic. The dialog has an info page and an action page. The latter depends on your database being embedded or external.

How to create a self-installing bundled (not embedded) HSQLDB:
[list]
[*]Having the extension installed and your .odb front-end connected to some HSQL back-end anywhere on your system, open your database document, call Tools>Macros>Organize>Basic>FreeHSQLDB>FreeHSQLDB>Main and make sure that the driver setting on the configuration tab points to your hsqldb.jar.
[*] Hit the "Create Package" button on the installation tab.

On the target system where my FreeHSQLDB is installed, you open the database document, call the dialog and hit "Extract & Connect".

Important notes:
1) The dialog's user/password settings do not set any user name nor password in the backend-database. This little extra is meant to adjust the Base front-end to the user accounts in your database back-end. "SA" (system admin) with no password is the almighty default user of a newly extracted HSQLDB. After you have set up the group and users privileges in your HSQL back-end, the front-end settings can tell Base that it should prompt for the password of user "X" or that it should log-in as user "X" with password "Y". The latter option imposes a security problem because the password is stored in plain text and anyone with access to the Base document gets access to the database as user "X".

2) You can operate your formerly embedded DB with a much more recent version of HSQL, BUT notice that HSQL 2.2.9 does not play well with OpenOffice. Download 2.2.8 instead:
http://sourceforge.net/projects/hsqldb/ ... p/download
No installation required. Extract the downloaded zip on your system. You find the hsqldb.jar in sub-directory "lib".
Version 3.0 is due to be released soon.

4) A connection to an external database is much more reliable. With embedded HSQLDB you may lose all of your data just because some arbitrary office component crashed.

5) You can move the front-end (Base document) away from its back end (database files) and distribute the front-end across your local network. You can store the back-end on a file server and access it from multiple machines. BUT: the type of connection created by this tool is read-only for any subsequent user after the first user has connected.
For simultanious multi-user connections with read-write access you need to write a configuration file, start the HSQL software in server mode and change the connection-URL of your Base document from something like
jdbc:hsqldb:file:/path/name;... (reads directly from database files)
... to something like ...
jdbc:hsqldb:hsql:server/name;... (communicates with a server software)


To do:
1) A set of check boxes for the hsql options. This version simply appends a static string of options to any new URL:
jdbc:hsqldb:file:///path/MyDB;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
I believe that these options are reasonable defaults for Base with jdbc:hsqldb but I'm always uncertain about database schemas and connection parameters.
A set of check boxes should be trivial to implement if you know everything about HSQLDB options.

2) A tool to extract and reconnect forms.

3) A network sniffer to detect a running HSQLDB server and connect a Base document with it.

4) Extension builder to make extensions from database directories. Such a database extension should extract a registered front-end with freely accessible forms and reports connected to some back-end. It should include the back-end installation as an option.

5) A new reporting tool able to generate serial letters with report tables on stand-alone Writer documents, such as invoices with mail merge fields for the recipient and tables for the recipient's invoice line items.

[DACM]

Bravo Villeroy!

Needless to say, this code library is invaluable. Thank you so much for working out the extraction (and repackaging) of embedded database files. I like the existing dialogs but I'm also thinking outside-the-box a bit. In other words, I'm still thinking through some of the implications for database-portability including engine-compatibility across machines, but I think we now have all the necessary pieces of the puzzle.

[Villeroy]

Bravo Villeroy!

Needless to say, this code library is invaluable. Thank you so much for working out the extraction (and repackaging) of embedded database files.

Well, nothing gets repackaged because HSQL is not forward compatible. The extraction routine just doesn't delete the embedded database so the routine behind the reverse button can reconnect to the what is still in the document. It was so handy for testing, so I kept this button.
It checks if there is any ./database/ storage (assuming that any ./database/ must be HSQL 1.8.) and then it resets the DataSource object to the following defaults that did not change since the times of OOo 2.4 (which is the oldest embedded database I have):

Code: Select all   Expand viewCollapse view

With oDocument.DataSource
   .URL = "sdbc:embedded:hsqldb"
   .User = ""
   .Password = ""
   .IsPasswordRequired = False
   With .Settings
      .JavaDriverClass = ""
      .JavaDriverClassPath = ""
   End With
End With


Repackaging an external HSQLDB could be done easily if the external database was not touched by any HSQL engine older than 1.8.x.

[DACM]

Repackaging an external HSQLDB could be done easily...

That could be very useful...

I think "Unpacking" and even "Packing" the database components for portability (complete with applicable hsqldb.jar as an option) is a goal, but not for the purposes of running the package-file as an embedded database. I prefer to leave that era behind (with good riddance) while utilizing a single-file for in-transit portability alone. The database would be unpacked for use in all cases. The database would remain in this un-packaged state until the user decides to "Pack-&-Go" the components as a single-file for movement or distribution. The .odb should contain code sufficient to identify the packaging state and self-extract the database and engine as necessary for JDBC access. This would require macros enabled in *Office, but I'm assuming that Base would fail gracefully, without harm, in the event of a non-macro environment -- as long as we don't bother resetting those parameters as you've outlined above (particularly not ...URL = "sdbc:embedded:hsqldb").

[Villeroy]

Repackaging an external HSQLDB could be done easily

I feel sorry for this phony statement. I can get anything out of a document storage into a file but not the other way round.
Yes, I know http://www.openoffice.org/udk/common/ma ... reams.html but I do not understand it.

This will be another unfinished OOo project because I don't have the time/enegy/knowledge/endurance. I have to earn my living (which has nothing to do with programming btw).

[DACM]

You've done plenty Villeroy! This was an unbelievable effort over what appears to be a very short period of time. The impact won't be fully realized until core Base development moves in a similar direction, even embracing these goals. But this should serve as a proof-of-concept, even a mandate, from the user-community. Either way, I think the user-community has enough now to move away from the embedded file-format with sufficient automation. I'll keep working with the code in light of portability goals, but I've never studied the UNO API in-depth so your "amateur" coding skills will keep me occupied for months. In the meantime, the user can right-click the database folder (containing all the critical components) and zip it for portability. Perhaps others will chime in with help, but I'll certainly have some questions over time.

[Villeroy]

I cleaned up my code and the dialog a little bit, edited misleading (wrong) comments. It works a little bit better now. It won't revert to embedded mode anymore. Instead you can extract any kind of embedded database (the files in sub-storage "database") and you can extract an optional driver in substorage "driver". The driver setting is done automatically for any extracted hsqldb.jar.

As a database packager you can wrap your ./database/HSQLDB files and your ./driver/hsqldb.jar in the database document, don't care about any connection setting. Just use your favourite zip tool for the packaging since I don't get how to do it in UNO.
Oh, and make sure that your database files have no file name prefix. They should be named "script", "properties", "data" and not "mydb.script", mydb."properties", "mydb.data"

A self-installing database may carry my library as embedded Basic or the library may be installed on the target system. Then add a starter routine like this:

Code: Select all   Expand viewCollapse view

Sub Install_HSQLDB
'if NOT Globalscope.BasicLibraries.hasByName("FreeHSQLDB") then exit sub
'Globalscope.BasicLibraries.loadLibrary("FreeHSQLDB")

if NOT BasicLibraries.hasByName("FreeHSQLDB") then exit sub
BasicLibraries.loadLibrary("FreeHSQLDB")

REM initialize global vars
'FreeHSQLDB.FreeHSQLDB.gODB = ThisComponent
FreeHSQLDB.FreeHSQLDB.initialize(ThisDatabaseDocument)

REM this is the same routine started by the dialog button "Extract & Connect":
FreeHSQLDB.FreeHSQLDB.Button_ExtractEmbedded
End Sub

This may also work with FreeHSQLDB embedded in your database package. Then you leave out the "GlobalScope" and change ThisComponent to ThisDatabaseDocument in your starter routine and in FreeHSQLDB.

I'm going to replace the above attachment and edit the long description now.
The 5 downloaders should remove the old extension before installing the new version. I'm unable to build properly updating extensions.

Kudos to DACM for all his expertise and for pushing me onto the right track. Now we have a macro which treats an odb file as kind of database extension.

[Greengiant224]

DACM posted:
You've done plenty Villeroy! This was an unbelievable effort over what appears to be a very short period of time. The impact won't be fully realized until core Base development moves in a similar direction, even embracing these goals.

I'll second that ! ! !

I have downloaded your second version and will try that out now.

Here are a few of my problems, not in any particular order.
In the first version, I had to extract the .script files for it to work. On the second test, self extraction of the *.odb file
was successful.
The Revert to Embedded button remained greyed out at all times.

It appears the the file picker only remembers the last path selected. Not sure if this could be amended to
recall the correct path/folder. It would help in using the file picker to open the *.odb, connect to existing
.script file or classpath, without having to follow the folder tree structure everytime.

The classpath is stored to the macro but it is never remembered when you restart the database.
It has to be reinstated.

Overall, your contribution works very well.
Hopefully, the devs will pick up on this and integrate it into a new version of Apache OpenOffice.

Kind regards

Greengiant224

[Villeroy]

I want to give one of my "database extensions". It comes with a slightly modified FreeHSQLDB (so you don't need to install the macros under "My Macros") and a document-open macro in Standard.Module1
Notice that the database files in the archive are named database/data, database/script and database/properties. Before replacing the old embedded database with this one, I removed the file name prefixes so the file names look like the file names of a "native" embedded database. The extraction routine will prepend the file name prefix so the extracted files will be database/BerlinStreets_3.1data, database/BerlinStreets_3.1.script and database/BerlinStreets_3.1.properties. Notice that the latter file is a text file which identifies the database as HSQLDB 2.2.8 (I simply extracted it from its former embedded version and loaded it using HSQL 2.2.8)

This is how the installed database looks after loading it from directory "test". The installation adds the database directory and the driver directory.

DatabaseExtension.png (17.45 KiB) Viewed 27233 times

The starter macro is bound to the document open event. If you load this document from a trusted directory, it will extract a database (an arbitrary forum example about filtering), extract the driver and connect to the extracted database using the extracted driver.
You won't notice any difference to an embedded HSQLDB. The difference is that the database won't be packaged every time you close the document and it won't be extracted again when you open it again.

The bundle is too big for this forum. Download http://www.mediafire.com/?fa1ka4k3o98dw1t

[rudolfo]

As a database packager you can wrap your ./database/HSQLDB files and your ./driver/hsqldb.jar in the database document, don't care about any connection setting. Just use your favourite zip tool for the packaging since I don't get how to do it in UNO.

I have to admit that I downloaded Villeroy's macros, but I am not sure if I fully understand them. What I understood is that we are looking for something like a counterpart for the Sub extractStorage in case we want to package the database again for distribution. I have made some attempts some time ago and tests with a simply layout (no folders inside the zip archive) have been successful. The code can be found in this thread that is about 2 months old. If I remember this correctly the zip packager service does automatically create a manifest.xml file in the zip archive. So this might conflict with the already existing manifest file in the MEAT-INF directory. Though I never really understood the purpose of this manifest file.

[DACM]

...a slightly modified FreeHSQLDB (so you don't need to install the macros under "My Macros") and a document-open macro in Standard.Module1

Perfect. I also added the FreeHSQLDB library to the .odb, but I then inject the code into My Macros (using .insertByName), and then make similar calls using Globalscope.BasicLibraries. I create the global library name as .HDQLDBtools to avoid conflict with .FreeHSQLDB.

But...I'm re-thinking the necessity to leave a permanent copy on the host computer. I think if everyone is in agreement with distributing a dedicated copy of the hsqldb.jar driver with each database, then we can go completely portable with the code. In that case, we'll need a (non-persistent?) menu item for dialog access, in order to support legacy 'embedded database' file selection and conversion. This legacy conversion process (dialog) should include a file-picker to grab a copy of the HSQLDB 1.8 driver from the *Office installation,so we can avoid global ClassPath storage in all cases (StoreClassPath). And it follows, that if we're going portable with FreeHSQLDB, then I think we can eliminate the StoreClassPath constant -- and simply rely on automated folder-portability code such as in mydb_wizard.odb.

One note: As we know, the "driver" includes the engine classes and a light-weight GUI manager. But only the HSQLDB 2.x driver includes the licensing documents (as found in the "META-INF" subfolder of the .jar). Those documents are nowhere to be found in the built-in driver version, so I hope Fred Toussi & company will let us slide on that issue -- considering the distribution involved with this macro-enhanced ODB template. But then, I guess we could add those documents to the legacy jar and distribute it through a direct-download link as I'm doing with 2.x to accompany mydb_wizard.odb at the moment.

[DACM]

...we want to package the database again for distribution.

Yes, that would be a goal. But if it gets too messy, or in the meantime, I think we can teach users to right-click > Send To > Compressed (zipped) folder. I think that function was introduced in the Windows world in Vista or even Win7. Does Mac and Linux offer a similar function? Of course, various zip-archiving software also adds the right-click archiving option even for WinXP.

Just use your favourite zip tool for the packaging since I don't get how to do it in UNO.
Oh, and make sure that your database files have no file name prefix...

Hmmm...

I think for now, folks will just drag-&-drop the unpacked database-folder between locations or devices. I'm referring to the entire folder including the .odb and new "database" and "driver" subfolders. If they need the convenience of a single-file (such as for distro), I think we can simply promote a right-click > Send To > Compressed (zipped) folder methodology...followed by click (the .zip) > Extract -- in Windows parlance. It just seems unnecessary to repackage the .odb until or unless we can do it automatically [IMHO]. This may just be something for the devs to tackle.

On the other hand, we (I) will absolutely take advantage of your .odb packaging (with driver) for .odb template distribution, so thank you very much for that support and details.

[DACM]

HSQLDB 2.3.x solves a major "workaround" issue with the Base GUI and HSQLDB in this 'split' configuration. Apparently, HSQLDB 1.8.0.10 accepted a non-standard AutoValue syntax with "NOT NULL" preceding "IDENTITY" or "GENERATED BY DEFAULT AS AS IDENTITY". Unfortunately, Base developers hard-coded the non-standard syntax, so in the absence of ongoing bug fixes on the Base side, Fred Toussi of the HSQLDB group has modified HSQLDB 2.3.0 to accept the aberrant syntax.

To take advantage of the fix with HSQLDB 2.3, we must setup the following settings which are saved to our Base .odb file:

Edit > Database > Advanced Settings > Retrieve Generated Values [check] >

Auto-increment statement [add]: GENERATED BY DEFAULT AS IDENTITY
Query of generated values [add]: CALL IDENTITY()

Otherwise, these settings will cause an error when employed with earlier versions of HSQLDB.

Edit: HSQLDB 2.3 has been released. I've included this database engine and incorporated the above settings into the 'split HSQL database' template (.odb) found here: [Wizard] Automated 'split' HSQL database support ...

[Villeroy]

As a database packager you can wrap your ./database/HSQLDB files and your ./driver/hsqldb.jar in the database document, don't care about any connection setting. Just use your favourite zip tool for the packaging since I don't get how to do it in UNO.

I have to admit that I downloaded Villeroy's macros, but I am not sure if I fully understand them. What I understood is that we are looking for something like a counterpart for the Sub extractStorage in case we want to package the database again for distribution. I have made some attempts some time ago and tests with a simply layout (no folders inside the zip archive) have been successful. The code can be found in this thread that is about 2 months old. If I remember this correctly the zip packager service does automatically create a manifest.xml file in the zip archive. So this might conflict with the already existing manifest file in the MEAT-INF directory. Though I never really understood the purpose of this manifest file.

Thank you very much, Rudolfo. I could not find this service because I was fixated on the DocumentStorage object as data sink because I used this type of object as a data source when extracting a database. So we go from the SimpeFileAccess to c.s.s.packages.Package and vice versa. I streamed from DocumentStorage (same hierarchy) to SimpleFileAccess which is a one-way ticket.

Now I fight with the Basic lingo because I underestimated the string manipulation effort to convert valid URLs into strange ones and vice versa.
HSQL interpretes file:///C:/Path%20With%20Spaces/MyDB literally, generating a new database directory "Path%20With%20Spaces" with a database skeleton consisting of 3 files MyDB.script, MyDB.properties and MyDB.data.
So I wrote a Basic function to convert the relevant part of a weird connection URL
jdbc:hsqldb:file:///C:\Path With Spaces\MyDB;option=value
into a valid and system independend file:///C:/Path%20With%20Spaces/MyDB to locate the database on all systems.

Now I'm going to wrap files like database/MyDB.script as database/script in the archive because that is the way how the "native" databases are stored and I want this to be uniform so my code does not need to distinguish between "native" HSQL 1.8 databases and manually zipped HSQL 2.2.8 databases. It simply dumps any HSQLDB database into a subdirectory prepending the name of the source.odb on extraction. Then it connects the Base document to the extracted database turning the encoded file: URL into a strange one again.
The file name needs to be prepended to the script, properties and data files because it is part of the connecton URL
A document file:///C:/Path%20With%20Spaces/MyDB.odb extracts 3 database files MyDB.script, MyDB.properties and MyDB.data into a sub-directory and adds this weird connection-URL to the document settings so the HSQL driver actually finds the extracted database:
jdbc:hsqldb:file:///C:\Path With Spaces\database\MyDB;option=value

I can solve the string manipulation issues in Basic and Rudolfo's snippet enables me to add wraps the external HSQLDB and the used database driver into the Base document.

[DACM]

...Rudolfo's snippet enables me to add wraps the external HSQLDB and the used database driver into the Base document.

Now I know what it's like to be a cheerleader on the sidelines.


But from the sidelines, here's the next-gen (Version 3a) of mydb_wizard_2.2.8.odb.

It supports the packaged "driver" and "database" folders using the portable-variant of FreeHSQLDB. Both hsqldb.jar and sqltool.jar are included in the 'driver' folder (SqlTool is used for server-mode shutdown in multi-user environments and other command-line SQL). I run some portability code to check the folder location in all cases; this means the .odb must remain in the parent database-folder at all times -- although this requirement will likely be relaxed by future "Pack & Go" code in FreeHSQLDB. I also added some code to FreeHSQLDB to support renaming the .odb despite an existing, unpacked "database" subfolder. And I re-ordered the file extraction so the "driver" is extracted first (while maintaining the current success-driven code logic), just in case there's no internal "database" folder, such as would normally be the case with a new-database template such as this .odb wizard approach.

And here's another version (Version 3b): mydb_wizard_2.3.0_beta which includes the HSQLDB 2.3.0 snapshot beta driver supporting AutoValue setup through the Base GUI !!! Thanks Fred Toussi of the HSQLDB group!
...

[Villeroy]

OK, I've done a lot of more development and testing. The result is somewhat frustrating. I removed the extraction/packaging from my little macro suite because you must not manipulate a document's zip-archive while the document is loaded in the office suite. I tried to disconnect, save, close, manipulate and then re-open the document but that leads to even more crashes, incomplete results, incomprehensible error messages and so on.
Any decent zip tool can extract and wrap HSQLDB folders with script, properties and data files in whatever archive you like, together with .odb files or within .odb files. Just ensure that the .odb file is not loaded by some office suite and that the database is not in use by any software.
Then my little dialog helps you to connect your .odb document with the extracted database and to specify the right driver to be used with this specific database so you can use the default driver for embedded HSQLDBs.

This is how to free your embedded HSQLDB within a minute:
1) Close your document.
2) extract the database folder from the document.
3) rename the script, properties and data files to prefix.script, prefix.properties and theprefix.data ("prefix" is just an arbitrary name).
4) Open the .odb file, call Basic macro FreeHSQLDB>FreeHSQLDB>Main, hit the connect button and point to your extracted database by picking your prefix.script file.
5) Click the "Set" button and point to your driver hsqldb.jar.

[Greengiant224]

villeroy:
The result is somewhat frustrating. I removed the extraction/packaging from my little macro suite because you must not manipulate a document's zip-archive while the document is loaded in the office suite. I tried to disconnect, save, close, manipulate and then re-open the document but that leads to even more crashes, incomplete results, incomprehensible error messages and so on.

That is an anomaly of the program.
If you use my taskbar app in server mode shutting down the .odb file first, it then makes an sql dump and a *.tar.gz
backup of the file.
However using single user file mode you cannot make a backup whilst the *.odb file is open. As you have found.
I am currently looking at python which is part of AOO/LO which may overcome these shortcomings.
Running it from a GUI (under Windows) or a batch/shell scrip. It is far from complete and needs more work.

Greengiant224

[DACM]

...you must not manipulate a document's zip-archive while the document is loaded in the office suite. I tried to disconnect, save, close, manipulate and then re-open the document but that leads to even more crashes, incomplete results, incomprehensible error messages and so on.

...using single-user file-mode you cannot make a backup whilst the *.odb file is open.

What's the common issue here? Are these errors and limitations associated with "disconnecting" the .odb document from HSQLDB reliably?

If that's the problem then I presume we can force the disconnect by shutting down HSQLDB through SQL (in either mode: file or server). That's really a back-end function anyway. Can't we simply issue "SHUTDOWN" in the macro and wait? The code could then check the connection status at 1 second intervals up to a reasonable limit. That limit could be as low as 5 seconds or so based on a shutdown rate of 200,000 rows per second -- as tested on my 7 year old, dual-core computer. SHUTDOWN COMPACT adds an order of magnitude to the time, so 200K rows take more like 10 seconds to process. If we don't get confirmation of a shutdown with disconnect in a reasonable time-frame, then simply abort and exit (or ask the user if they want to wait for million+ record databases).

Otherwise, if these issues are unrelated to database connection, then where are the specific problems? I ask, because starting with an unconnected file (before clicking the Tables icon or opening any queries, forms or reports), I've never seen an issue with extracting an embedded database and/or driver from sub-storage using portable FreeHSQLDB code. I've run several hundred tests, extracting drivers and databases from the running "host" .odb without issue while bypassing the FreeHSQLDB dialog. By "host" .odb I'm referring to an .odb with embedded FreeHSQLDB library. I've also run quite a few tests using the FreeHSQLDB dialog from a "host" .odb to extract an embedded database from a separate, unopened .odb in side-saddle mode. By "side-saddle mode" I'm referring to FreeHSQLDB residing and run within in a "host" .odb, but using the FreeHSQLDB dialog to select a separate, legacy, 'embedded database' file for database extraction. I've had no issues with this side-saddling approach either, perhaps because the separate .odb isn't open in Base. Am I missing something here?

Edit: Oh, I wasn't actually connecting to the files extracted from a legacy 'embedded database' after using the "host" in side-saddle mode. It seems that Basic's 'Name OldName As NewName' facility can't rename those particular files (script, properties, data, backup) as necessary for JDBC connection, apparently due to the lack of a dot or file-extension. Unless they're renamed with at least a dot (.script), JDBC ignores them. So unless there's another facility to rename those files, I'm stuck with manual intervention to rename files extracted from a legacy 'embedded database' file. Bummer.

Now, I did run into problems while attempting to inject the FreeHSQLDB library itself into another .odb. That's proven hit or miss, but so far I haven't tried it after unloading the library in the "host" .odb.

Or were the issues primarily a matter of re-packing the "host" .odb with an external database and driver? Before or after database connection? I haven't tested re-packing at all because the portable FreeHSQLDB code did not support re-packaging.

[Villeroy]

It's easy enough to package and extract the back-end manually. Then my little tool helps to get the connection details right.

A python script should watch out for any lock files, read the JavaDriverClassPath and the connection-resource from the content.xml, analyse the connection-URL (which is a strange URL with no URL encoding) then wrap the back-end into a separate zip folder, say "database2" and the .jar file (which is specified by a truely encoded URL) into a "driver" directory.

When extracting the package, Python should watch out for any lock file, extract 2 folders "database2" and "driver", and then manipulate JavaDriverClassPath and the connection-resource in the content.xml (I think the xml.dom module can read and write xml). After successful installation it should remove the "database2" and "driver" folders from the zip.

The new "database2" must not interfere with any previously existing "database" folder from an embedded DB, which is part of ThisDatabaseDocument.DocumentStorage together with some registration and stuff.

[Villeroy]

Testing the latest beta of HSQL 2.3.0 I found out that any globally set hsqldb.jar (via Tools>Options>Java>ClassPath) overrides the document specific setting.

Having a database document configured to use a HSQL 2.2.8 driver and an office configuration with a class path pointing to HSQL 2.3.0, the following query reports 2.3.0 as currently used database engine.

Code: Select all   Expand viewCollapse view

SELECT DATABASE_VERSION( ) AS "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" WHERE "TABLE_NAME" = 'ROUTINES'

I find this behaviour rather unusual which is why I report this matter of fact here. I would expect that the more specific setting overrides the global one.

[DACM]

...any globally set hsqldb.jar (via Tools>Options>Java>ClassPath) overrides the document specific setting.

It's unfortunate, and perhaps could be changed quite easily by a core developer. I had noticed the same behavior and included step (2) in these instructions for using related template files:

Instructions for use:

NOTE: These steps are applicable to both new and existing 'split' HSQL database users

(1) Ensure global macro security in *Office is set to Medium (or Low):

Tools (*Office) > Options (Preferences) > *Office > Security > Macro Security > Medium

(2) Remove any global Class Path to hsqldb.jar you may have setup manually in *Office:

Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > Remove

...

[Sliderule]

Villeroy:

Just my thoughts on the issue you mention above . . . I it does make sense. Since, OpenOffice / LibreOffice uses the USER SPECIFIED class path, to get to the USER DESIRED jar files . . . whatever / whichever HSQL jar is specified will take priority.

Therefore, if a user defined jar points to HSQL 2.3.0 . . . that is what will be used, and, defined in the *.properties file ( for example, mydb.properties ).

While, going BACK to HSQL Version 1.8.0.10, from a HSQL 2.2.8 or, HSQL 2.3.0 is possible, ( specifically by issuing a SHUTDOWN SCRIPT ) and, manipulating the *.properties file ( actually, replacing it ) . . . it is possible, but, not worth the 'effort / time', in my opinion.

Likewise, going from HSQL 2.3.0 to HSQL 2.2.8 is possible, by changing the *.script file ( specifically, if VARCHAR_IGNORECASE was used in HSQL 2.2.8 . . . the SCRIPT file in HSQL 2.3.0 will 'define' it differently ) . . . for example:

1. HSQL 2.2.8 *.script file
   
   

   Code: Select all   Expand viewCollapse view

   CREATE CACHED TABLE PUBLIC."PEUGEOT_PART"("ID" INTEGER NOT NULL PRIMARY KEY,"DESCRIPTION" VARCHAR_IGNORECASE(250),"PART_NO" VARCHAR_IGNORECASE(250),"PRICE" DECIMAL(50,2),"MANUFACTURER" VARCHAR_IGNORECASE(250))

   
   
   
2. HSQL 2.3.0 *.script file
   
   

   Code: Select all   Expand viewCollapse view

   CREATE CACHED TABLE PUBLIC."PEUGEOT_PART"("ID" INTEGER NOT NULL PRIMARY KEY,"DESCRIPTION" VARCHAR(250) COLLATE SQL_TEXT_UCC,"PART_NO" VARCHAR(250) COLLATE SQL_TEXT_UCC,"PRICE" DECIMAL(50,2),"MANUFACTURER" VARCHAR(250) COLLATE SQL_TEXT_UCC)

Explanation:

1. The VARCHAR_IGNORECASE ( in HSQL 2.2.8 ) becomes VARCHAR with a COLLATE SQL_TEXT_UCC added ( in HSQL 2.3.0 ).
   
   
2. You can see, in the following code ( HSQL 2.3.0 ), that, SQL_TEXT_UCC is a 'valid' COLLATION_NAME
   
   

   Code: Select all   Expand viewCollapse view

   Select * From INFORMATION_SCHEMA.COLLATIONS;

Sliderule

[DACM]

Well it makes some sense, but I don't think the user actually wants to take on the risk of that rather limited environment if they don't have to. As Sliderule expertly points-out, managing HSQLDB versions through the global class path setting has huge implications. It not only disables support for 'embedded database' files, but it risks trashing a database (inadvertently upgrading/downgrading a database which can fail without expert manual adjustments).

We taught users to manually adjust the global class path in order to support HSQLDB 2.x with Base. That was then, this is now. We now need to retract that advice in light of more flexible and safer alternatives. This trend of backwards incompatibility is ongoing with HSQLDB, so perhaps now more than ever we need to adopt and promote portable database folders which include a dedicated copy of the user-chosen engine (per database as desired). I'm currently running and actively interacting with four different HSQLDB engines in support of their respective databases -- including a legacy embedded database. Granted this scenario is somewhat unrealistic but I'm apparently limited only by available RAM from opening an unlimited number of different databases supported by different versions of HSQLDB, simultaneously.

So I think we should discourage the 'global' class path setting in all future discourse in these forums with HSQL database users, so we can move on to an era which simultaneously supports HSQLDB 1.8.0.10, 2.2.8, 2.3.0, etc. Each database folder gets its own copy of the engine, so the database remains compatible across all installations and computers (with OOo/AOO/LibO and Java 1.6+ installed); the document-level class path and data-source URL are handled automatically, so the folder is completely portable such that it can be moved at will, or even zipped in-transit as necessary for email, USB, cloud, or a forum post/example.

Here's the concept for independent testing. Eliminate any global class path to hsqldb.jar, enable macros, and unzip as necessary to see these portable databases run simultaneously in separate instances of Base. The macro autoruns and completes in the first split-second the .odb is opened, so you're left with a normal .odb with the appropriate data-source URL and document-level class path setting based on the current database folder location. Feel free to rename the main folder, .odb file, and/or HSQL data-files as desired:

Student Attendance - embedded.odb
Student Attendance - HSQLDB 1.8.zip
Student Attendance - HSQLDB 2.2.8.zip
Student Attendance - HSQLDB 2.3.0_43.zip
Student Attendance - HSQLDB 2.3.0_46.zip

...

[Sliderule]

I think ( yes, Sliderule thinking, or, attempting to think may be hazardous to your health ) it is important to mention something I just 'learned' about HSQL Version 2.3.0 ( the official version from HSQL is scheduled to be released this month, May, year 2013 ), and, COLLATION.

According to HSQL 2.3.0 documenation http://www.hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_collations:

The supported collations are named according to the language. You can see the list in the INFORMATION_SCHEMA.COLLATIONS view. You can use just the name in double quotes for the default form of the collation. If you add a strength between 0, 1, 2, 3, the case sensitivity and accent sensitivity changes. The value 0 indicates least sensitivity to differences. At this strength the collation is case-insensitive and ignores differences between accented letters. At strength 1, differences between accented letters are taken into account. At strength 2, both case and accent are significant. Finally 3 indicates additional sensitivity to different punctuation. A second parameter can also be used with values 0 or 1, to indicate how decomposition of accented characters for comparison is handled for languages that support such characters. See the Java and ICU (International Components for Unicode) collation documentation for more details on these values. For example, possible forms of the French collation are "French", "French 0", "French 1", etc. and "French 2 1", etc. When the collation is specified without strength, it seems the system defaults to strength 2, which is case and accent sensitive.

When a collation is not explicitly used in the CREATE TABLE statement for a column, then the database default collation is used for this column. If you change the database default collation afterwards, the new collation will be used. With the older versions of HyperSQL the special type VARCHAR_IGNORECASE was used as the column type for case-insensitive comparison. Any column already defined as VARCHAR_IGNORECASE will be compared exactly as before. In version 2.3.0 and later, this form is represented by the addition of UCC after the collation name, for example "French UCC". You can still use the SET IGNORECASE TRUE statement in your session to force the UCC to be applied to the collation for the VARCHAR columns of new tables. UCC stands for Upper Case Comparison. Before comparing two strings, both are converted to uppercase using the current collation. This is exactly how VARCHAR_IGNORECASE worked.

Important: If you change the default collation of a database when there are tables containing data with CHAR or VARCHAR columns that are part of an index, a primary key or a unique constraint, you must execute SHUTDOWN COMPACT. If you do not do this, your queries and other statements will show erratic behaviour and may result in unrecoverable errors.

In the above, Sliderule added, the colour highlights.

In my experience . . . and . . . testing HSQL 2.3.0 . . . what this means in the real world:

1. I issued the commands ( ONLY ONCE ), from the menu: Tools -> SQL...
   
   
   1. To define the COLLATION language and rules, notice that the COLLATION_NAME is surrounded by double quotes, and, SQL_TEXT_UCC means SQL Text Upper Case Comparison:
      
      

      Code: Select all   Expand viewCollapse view

      SET DATABASE COLLATION "SQL_TEXT_UCC";

      
      
      
   2. To rebuild the indices from any Primary Key(s) and Unique Constraints:
      
      

      Code: Select all   Expand viewCollapse view

      SHUTDOWN COMPACT;

   
   
2. Now, any text field, defined as, CHAR, or, VARCHAR will be 'found' regardless of CASE ( UPPER CASE, Mixed Case, lower case ).
   
   For example, if a field in my database table, "WORLD_POPULATION", with a column name of "Country" contains a value of: 'Uruguay' . . . the following Select statement WILL find it:
   
   

   Code: Select all   Expand viewCollapse view

   Select *
From "WORLD_POPULATION"
Where "WORLD_POPUALTION"."Country" = 'URUGuay';

   
   
   
3. Now, any NEW text field, defined in a NEW table and/or column, as VARCHAR, will automatically find the 'value' regardless of CASE . . . ( UPPER CASE, Mixed Case, lower case ).
   
   
4. Valid collation names can be found by running the the following Query:
   
   

   Code: Select all   Expand viewCollapse view

   Select *
From INFORMATION_SCHEMA.COLLATIONS

Sliderule