Place Basic using Split_HSQLDB_2.3.2_Wizard_v3d

[arfgh]

thx so much DACM for all your work related to Split_HSQLDB_2.3.2_Wizard_v3d.odb

I am testing all that following your instructions, but i need to know where can i place my basic subs and functions that i have into my db in ''BASIC/STANDARD/Module1'.

thx in advance

[Villeroy]

Open your database.
Tools>Macros>Organize>Basic...
Tab [Libraries]
Location: Your_DB.odb
[Import...]
The stupid file selector does not show .odb files but when you navigate to the right folder and type "Split_HSQL" into the name box, it will let you select DACM's database file.
Import the FreeHSQLDB library but not the Standard one.
Note: If the "Embedded" module would be in library "FreeHSQLDB" or any other non-Standard library, all the code could be imported in one go.
OK, now for the Standard/Embedded module:
Open the Split_HSQL database document.
Tools>Macros>Organize>Basic...
Tab [Modules]
Ctrl+Drag&Drop the "Embedded" module into one of your libraries (FreeHSQLDB or Standard or some other lib).
Extract the database and the driver
Run macro Your_ODB/Standard/Embedded/Setup

Alternatively:
Import the FreeHSQLDB library into the global container "My Macros".
Open your database document and run My_Macros/FreeHSQLDB/FreeHSQLDB/Main which opens a dialog where you specify the *.script file of your extracted database and the hsqldb.jar to be used.

[DACM]

...i need to know where can i place my basic subs and functions that i have into my [.odb] in ''BASIC/STANDARD/Module1'

Are you simply trying to migrate your own code into the .odb template (Split_HSQLDB_2.3.2_Wizard_v3.odb), while creating Module1 within the STANDARD code library? As Villeroy illudes, you can't utilize the Library export/import functionality for Basic code stored in the STANDARD Library (for some reason). Therefore, you'll need to create Module1 in the existing STANDARD library, and then Copy&Paste your code (subs and functions), as follows:

• 1. Alt-F11 -or- Tools > Macros > Organize Macros > *Office Basic...
  
  2. Basic Macros [dialog] > Organizer... [button] > Modules [tab] > Split_HSQLDB_2.3.2_Wizard_v3.odb > STANDARD > New... [button] > New Module [dialog] >
  Name [text box] > Module1 > OK [button] > Close - Basic Macros Organizer [button]

  

  3. Basic Macros [dialog] > Split_HSQLDB_2.3.2_Wizard_v3.odb > STANDARD > Module1 > New [button]

  

  4. Copy&Paste your code (subs and functions) into Module1

  

And BTW, you can rename the .odb template as desired...from Split_HSQLDB_2.3.2_Wizard_v3.odb ...to something like MyDB.odb

[DACM]

Note: If the "Embedded" module would be in library "FreeHSQLDB" or any other non-Standard library, [then we could simply utilize the Library export/import functionality of the Basic Macros Organizer].

Yes, sorry. That code was a quick hack designed to integrate the code from mydb_wizard.odb with FreeHSQLDB, while making it all portable with the .odb. I've long-sense rewritten the algorithms from the ground-up and placed them in a proper library. But as I was attempting to add additional functionality I ran into so many bugs and limitations in the API that I lost interest in the project. And you know how that goes...

[Villeroy]

Just one note: With the help of a text editor and some zip tool, without any Basic code you can easily reconnect an existing "Base database" (embedded HSQL). All that macro code basically inserts some XML nodes into content.xml.

[arfgh]

So DACM, 'Split_HSQLDB_2.3.2_Wizard_v3d' isnt the lastest from you ?

[Villeroy]

Just one note: With the help of a text editor and some zip tool, without any Basic code you can easily reconnect an existing "Base database" (embedded HSQL). All that macro code basically inserts some XML nodes into content.xml.

Here we go:
Assuming a Windows box with a database "MyDB" extracted to C:\HSQL\BACKENDS\ so that directory contains
MyDB.script
MyDB.data
MyDB.properties
and a driver stored in C:\HSQL\DRIVER\hsqldb.jar

0) You already made a backup of your original odb document, extracted its embedded database out of the zip archive and renamed the script, data and properties fields to get the external database

1) Extract contents.xml too and load it into a text editor.

2) Find the following tag
<db:connection-resource xlink:href="sdbc:embedded:hsqldb"/>
and replace it with
<db:connection-resource xlink:href="jdbc:hsqldb:file:///C:\HSQL\BACKENDS\MyDB;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"/>
Note that the green file path is written in system notation (with Windows backslashes) so the file URL is formally incorrect but this is how the HSQL engine works.

3) Find the tag
<db:application-connection-settings db:is-table-name-length-limited="false" db:append-table-alias-name="false" db:max-row-count="100">
and insert the following section directly behind this tag:
<db:data-source-settings>
<db:data-source-setting db:data-source-setting-is-list="false" db:data-source-setting-name="JavaDriverClass" db:data-source-setting-type="string">
<db:data-source-setting-value>org.hsqldb.jdbcDriver</db:data-source-setting-value>
</db:data-source-setting>
<db:data-source-setting db:data-source-setting-is-list="false" db:data-source-setting-name="JavaDriverClassPath" db:data-source-setting-type="string">
<db:data-source-setting-value>file:///C:/HSQL/DRIVER/hsqldb.jar
</db:data-source-setting-value>
</db:data-source-setting>
</db:data-source-settings>
Note that the green file path needs to be written in valid URL notation. Use simple latin characters with no spaces for the driver path and replace Windows specific backslashes with regular slashes. Real life example from a Windows box with a space in "Program Files": file:///C:/Program%20Files/hsqldb/hsqldb.jar. "Normal" systems use slashes anyway.

4) Put the modified content.xml back into the .odb archive and open the database.

[DACM]

So DACM, 'Split_HSQLDB_2.3.2_Wizard_v3d' isnt the lastest from you ?

Well it is the latest stable/published version. I haven't looked at the code in over a year, so I don't remember specifically what finally deterred me, but at least one of the issues involved the APE file-picker tool specific to newer versions of Windows.

[DACM]

Just one note: With the help of a text editor and some zip tool, without any Basic code...

Hey thanks for the tip and steps Villeroy!

I played with those XML strings manually back when we were considering the possibility of re-packaging the split-database as an embedded-odb database. After all, I'm not opposed to embedding database files (and drivers) within the .odb file structure -- for the explicit purpose of database distribution -- and without deleting the original split-database; I'm a fan of the 'Pack-&-Go' concept. I simply contend (as you do) that the early Base developers over-reached when they chose to re-package/embed the .odb on a per session basis while deliberately deleting the source split-database from disk! The results have been disastrous, damaging the reputation and goals of the ODF spec and *Office Base, while leaving users high-and-dry (running to alternative solutions without regard for ODF).

Thankfully, we now have stable alternatives to the 'New Database' wizard in the form of preset macro-enhanced Base templates and Extensions from the Base user community.