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=falseI
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.