Page 1 of 1

FreeHSQLDB v.0.4

Posted: Sat Jun 06, 2015 5:43 pm
by Villeroy
2015-06-08: First bug fix release 0.3.1 (undeclared variable, sigh)
2015-12-13: 5th bug fix release 0.3.5: Thanks to Bill_NZ for his persistent testing and good advice. New AutoInstaller module. The modified database document will be stored automatically because the save command was not always enabled in the GUI.
2017-03-25: Minor bug fixes (closing braces) for compatibility with LibreOffice.
2017-07-06: More bug fixes for LibreOffice. The FreeForms macro extracts LibreOffice forms from registered databases. OpenOffice can do the trick with unregistered ones as well.
2022-06-02]Uploaded version 0.4. New feature: Use HSQL 1.8 driver of the office suite when no driver is given. Click the new default driver button on the dialog.


This is a new version of my FreeHSQLDB.oxt for the database developers among us.
FreeHSQLDB-0.4.png
FreeHSQLDB-0.4.png (38.24 KiB) Viewed 17995 times
For this version 0.3

-- I cleaned up fishy Basic code. It is a little bit less restrictive. You can now edit the log-in options for all types of database connections. This is useful with JDBC, ODBC and password protected spreadsheets. It does nothing with text files and behaves a little bit odd with dBase connections.

-- Added a combo box for on-the-fly database registration. It shows any registration name for the current Base document. Select an existing registration name to overwrite the registration with the current Base document. Enter a non-existing name to add a new registration for the current Base document. Clear the combo box to remove any existing registration for the current document. Like the "path of this Base document" text box, this feature is useful with all types of database documents.
The connection features are still restricted to external jdbc:hsqldb:file: connections.

-- Added an auto-install module to this library, very much inspired by the brilliant work of DACM. Copy that module together with the "Helper" module into your distributable database document, adjust the constant values to your requirements and assign the document open event to Sub onDocumentOpen. It offers the same features as the dialog for any distributed package with a Base document, a hsqldb.jar, a HSQL backend and a collection of stand-alone forms with optional registration at the office suite.

-- Added module "FreeForms" which prompts for a target folder where to extract all embedded forms including any subfolders within the forms container. The stand-alone forms are saved with "soft protection" (open read-only without password). The logical forms are either linked to the Base document's registered name or its file URL.

All this is just a Basic library with an ugly dialog. I tried to add a toolbar with 2 buttons to the add-on registration but failed. The advantage of the missing UI is compatibility with any Libre/OpenOffice suite since OpenOffice.org 2.3(?) while the extension manager provides a basic version control by version number.

Please add your own custom UI via menu:Tools>Customize...
The callable routines are:
1) FreeHSQLDB.FreeHSQLDB.Main (as in earlier versions this calls the dialog)
2) FreeHSQLDB.FreeForms.Main (the new forms extractor)
3) <your document library>.AutoInstall.onDocumentOpen which can be event triggered after copying modules "AutoInstall" and "Helpers" to your distributable Base document. After copying both modules, modify the declared AutoInstall constants to match your needs.

To be done:
-- Write a script to extract and rename embedded HSQLDB files.
DONE: [Python] Macro to extract and reconnect embedded HSQLDB

-- Design a nicer multi-step dialog with all availlable HSQL options. This version appends some reasonable options to the connection url
jdbc:hsqldb:file:/path/name;;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
and you may change them via menu:Edit>Database... The current Basic library contains hard coded HSQL options twice. One for the dialog and one for the auto-insall module.

-- Write a better report engine for stand-alone Writer documents. Sounds crazy? I'm pretty sure it can be scripted. If it would support the serial letter interfaces together with linked Writer tables for each letter then we could generate pretty invoices.

###############################################################################
Example package with auto-install macro: https://forum.openoffice.org/en/forum/v ... 21#p355521

Re: FreeHSQLDB v.0.4

Posted: Fri May 22, 2020 12:39 pm
by Villeroy
For those who are a little bit familiar with coding, this is a mini version which can be adjusted whenever some connection detail has changed:

Code: Select all

Sub Connect_JDBC_Backend()
REM This mini version of my FreeHSQLDB macro should be able to reconnect 
REM a database document with any type of JDBC connection (HSQL, MySQL or other)
REM if you set up the right constants for a valid URL, a driver path and class name

oDoc = ThisDatabaseDocument ' this code is embedded in the odb 
REM oDoc = ThisComponent ' this code is not embedded in the odb

REM-------- Database Location (URL) --------------------
REM protocol prefix refering to a set of files
Const cURL_Prefix = "jdbc:hsqldb:file:"
REM protocol prefix refering to a database server
REM Const cURL_Prefix = "jdbc:hsqldb:hsql:"

REM file path or server name with trailing slash
REM in case of cURL_Prefix = "jdbc:hsqldb:file:"
Const cURL_Path = "C:/Users/LoginName/HSQL/database/"
REM HSQL on Windows accepts strange URLs with slashes or with backslashes such as
REM Const cURL_Path = "C:\Users\UserName\HSQLDB\"
REM local files on a Linux sytem:
REM Const cURL_Path = "/var/hsql/database/"

REM in case of cURL_Prefix = "jdbc:hsqldb:hsql:" we specify a server name or IP address
REM Const cURL_Path = "//ServerName/"
REM Const cURL_Path = "//192.168/178.1/"

REM Either the database name is set up by a remote server if cURL_Prefix looks like "jdbc:hsqldb:hsql:
REM if cURL_Prefix looks like "jdbc:hsqldb:file:" the nae refers to a set of files starting with the same prefix
REM DatabaseName.properties DatabaseName.script DatabaseName.data etc.
Const cURL_DBName = "Test" 'file name prefix or database name defined on server side

REM options that turned out to be useful with HSQL (see HSQL documentation)
Const cURL_Options = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
REM JDBC connections other than hsql may support other options
REM or none at all:
REM cURL_Options = ""

REM--------- Database Driver -------------------
REM use system notation for the local HSQL driver
Const cJarPath = "C:\Drivers\hsqldb\lib\hsqldb-2.5.0.jar"
REM the "ability" used by the JDBC driver when used with a client:
Const cClass = "org.hsqldb.jdbcDriver"

on error resume next
	oDoc.CurrentController.ActiveConnection.close()
on error goto 0
oDataSource = oDoc.DataSource
oDataSource.URL = cURL_Prefix & cURL_Path & cURL_DBName & cURL_Options
With oDataSource.Settings
	.JavaDriverClass = cClass
	.JavaDriverClassPath = ConvertToURL(cJarPath)
End With
End Sub