How to use embedded and external HSQL with the same profile

[Villeroy]

DACM,

Your "Student Attendance" examples work very well except for one thing (which is entirely my fault):
You can't separate the frontend from the backend directory. When you put the .odb file to some other macro-trusted location it will install another database. If the archive does not include any database, it installs a new blank database.
This is because my function "IsInstalled" tests if there is some database in the frontend directory.

[DACM]

Thanks for testing Villeroy!

...You can't separate the frontend from the backend directory. When you put the .odb file to some other macro-trusted location it will install [a new] database.

That's true, but I anticipated that in my unpublished version -- which checks the current folder, then the 'database' sub-folder, then the current data-source URL. So you can move the .odb at will. And this search priority is select-able in the macro as level-1,2,3 because there are some issues to consider as we jump to level 3 support. You see, moving the .odb out of the dedicated folder (level 3 support) effectively defeats portability goals, and renders the .odb unusable as a template for new databases. On the other hand, there are good reasons to move and distribute the .odb independently in multi-user environments. Perhaps on a separate note, the code also checks for an existing driver and/or database; if an existing database exists without a dedicated driver, the code checks the .properties file for compatibility before extracting the packaged driver for use with that database. Again, these features align to avoid the issues we now face with 'global' class path settings and driver compatibility.

So I was planning to distribute the template with level '2' support enabled in order to promote database portability, while planning to coach folks through enabling level '3' support for deployment into multi-user environments. I presume portability is the priority, given the impetus for the 'embedded database' concept. On the other hand, we could simply enable level '3' in the template and plan to deal with any portability issues if-and-when they arise. For that matter we could also add a one-time warning when opening the .odb outside the dedicated folder. I could be convinced either way...

I'm glad you brought this up because it's worthy of some additional thought and discussion. I can't personally think of a reason to move the .odb out-of the dedicated database-folder in single-user database applications. Perhaps there are advantages, but it seems the .odb can be registered in any location for global *Office use, and a shortcut can be created to the file as necessary. But if the user does move the .odb out-of the dedicated folder, then it should be moved back for portability purposes; in fact upon moving the database-folder, the .odb must be run once within the database-folder to generate the proper data-source URL. I do understand that some users would be unaware of the issues and simply move their "database" (the .odb alone) in this manner without much thought. So we could certainly support independent .odb movement (level 3 support) if that's the best course of action. Otherwise, I think folks would understand that portability means moving the entire folder (drag&drop), or even zipping the entire folder into a single-file for electronic distribution as I did with the examples you cite.

I haven't worked on this stuff in a week or more now, but when I get back to it I plan to explore an additional approach whereby the template simply creates a dedicated folder and extracts the driver plus a streamlined (driver-less) copy of the macro-enhanced .odb to that folder. The extracted .odb within the dedicated folder would then be used to begin a new Base project. So the original download actually remains a true 'template' used only to create new database folders -- which solves several issues including new database creation and unnecessary driver bloat. This pure 'template' approach might be extended to realistically distribute multiple HSQLDB engines and associated .odb's within a single template, while giving the user a choice upon install.

[Villeroy]

From your template mydb_wizard_2.3.0_46.odb I removed the FreeHSQLDB library and copied one function from that lib into your Standard lib. The function extracts the red path from jdbc:hsqldb:file:///C:\path name\db name;option=value and returns a valid (encoded) file:///C:/path%20name/db%20name. Function IsInstalled tests if that URL + ".script" points to an existing script file. Your auto-open routine exits if this condition is met. Now you can move the front-end .odb away from its database directory without triggering a new installation process.
I modified the user name handling. It does not touch the user name unless it is an empty string which is replaced with the default "SA" user. Embedded databases have an empty user name. The "SA" user for embedded mode must be hard coded somewhere in the Base code and I think it should be set explicitly when the databases has been extracted.

My posting of 03 May 2013, 21:20 contains the latest version of the updatable FreeHSQLDB extension which is 0.2.1 currently.
I'll upload the extension to the extension server if I ever find out how to configure a toolbar button for this and if I get this installed in AOO 4.
Here I attach my version of your database template.