How do I create a new 'split' HSQL database using Base?

How do I create a new 'split' HSQL database using Base?

See: Wizard - Portable 'split' HSQL database template or follow the manual steps below. But understand that these two methods are mutually exclusive.

To manually create a new split-database from Base:

• Ensure you've setup the Class Path to hsqldb.jar as outlined above.
  • Note: The above 'Class Path' procedure must be accomplished on every computer used to access 'split' HSQL databases without employing a macro-enhanced template.

• Download this pre-configured Base front-end file: mydb.file.odb (feel free to rename this file)
• Click the file to open it in Base
• Then click on the Tables icon to create a new 'split-database'
• ...and you're done!
  • Note: The provided Base file is configured for "file-mode" (single-user) access to the associated HSQL database. The database files are created automatically in a dedicated folder named 'mydb' as specified in the 'data source URL' within the Base file (mydb.file.odb). The full path to the database is: \users\public\databases\mydb\mydb.*. The database path and name are flexible, but any manual changes must also be recorded in the 'Data-source URL' string as outlined below. "File mode" is a single-user access mode, but we can reconfigure this Base file for "server-mode" access to the same database thereby allowing concurrent, read/write access among multiple users. In any case, a 'split database' consists of two major components:
    • 1. A Base front-end file (such as mydb.file.odb) contains the database connection information, plus any Queries, Forms, Reports and Macros that you develop using Base;
    • 2. The back-end HSQL database files consisting of four (4) separate files containing your Tables and Data: mydb.script, mydb.properties, mydb.data, mydb.backup.
  • Note: These files makeup your portable database application, so they're best maintained in a dedicated database folder. Other supporting files may also be included in the database folder, such as your 'server mode' batch/script files (discussed in the note below). In any case, a properly configured Base "database application" will consist of at least two files including the Base (.odb) file and the database file(s). So while H2 and SQLite utilize a single file per database, when combined with Base, it's generally best to maintain a dedicated database folder per database application.

To create additional, new databases:

See: Wizard - Portable 'split' HSQL database template or follow the manual steps below. But understand that these two methods are mutually exclusive.

• 1. Download a new copy of 'mydb.file.odb' (feel free to rename this file), or create your own copy using the steps under the 'Alternative' heading below.
• 2. Select Edit → Database → Properties… to access the 'Data source URL' settings.
• 3. Then change only the folder name or the entire path to the 'mydb' database in the 'Data source URL' string.
  • For instance, change the path to:
    • \users\public\databases\contacts\mydb.*
    • \users\public\databases\inventory\mydb.*
    • \users\myname\databases\contacts\mydb.*
  • Note: You can also change the database name (from 'mydb') in this manner in order to create a new database. While this is logical, it will also affect server mode access (as opposed to file mode access). The server mode start-up and shutdown strings reference the database name ('mydb' in this case). So any change to the database name must also be applied to the start-up/shutdown strings. These strings are complex, and they're not stored within the Base file, so we typically store and run them using a convenient batch/script file. These batch/script files can remain relatively universal/unchanged as long as we keep the database name constant across database folders. Store a copy of these batch/script files in each database folder for server mode access. So as a recommendation, change only the folder name within the 'data source URL' in order to create a new 'split' HSQL database, identified by folder name. Feel free to change the name of your Base (.odb) files as necessary to differentiate database access (change mydb.file.odb to AddressBook.odb perhaps matching the name of the folder).

Alternatively you can create this master Base .odb file manually using the 'Connect to an existing database' Base wizard as follows:

• Select: File → New → Database -or- Press:  Ctrl  +  N 
• Select Option: Connect to an existing database
• Select Connection type: JDBC
• Press:  Next >> 
• Data Source URL (file mode): hsqldb:file:<path>/<database name>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
  • For example (database folder named 'mydb' with database also named 'mydb'):
    • hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
  • HSQLDB 2.x supports the wildcard character (~) to specify/derive the current user's folder:
    • hsqldb:file:~/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
• JDBC driver class: org.hsqldb.jdbcDriver
• Press:  Test Class 
  • Errors may indicate Class Path setup issues
  • Errors may indicate JDBC driver class syntax issues, such as a space after the string: org.hsqldb.jdbcDriver
• Press:  Next >> 
• User name: SA
• Password required: leave un-checked at this stage.
• Press:  Test Connection 
  • Errors may indicate Data Source URL syntax issues
  • Errors may indicate database authentication issues. Check username and password.
  • Errors may indicate user-access privileges (file or folder not shared) imposed by the operating system and managed by the system administrator.
• Optionally enable database-folder portability using the macro and instructions provided here: Wizard - Create a new split HSQL database