How do I setup multi-user 'server mode' access to my HSQL database?

How do I setup multi-user 'server mode' access to my HSQL database?

To run HSQLDB in 'server mode' and configure clients for multi-user access from Base:

• Ensure you've setup the Class Path to hsqldb.jar as outlined above.

Class Path setup is required on the server and on each client using an identical hsqldb.jar file.

• Ensure HSQLDB is running in server-mode either locally or on a network computer with an accessible database folder.

• Download this pre-configured Base front-end file: mydb.server.odb
• Open it in Base,
• Then click on the Tables icon to access an existing 'split-database' in the same folder (a new database is created automatically if none exists)...
• ...and you're done!
  • Note: The provided Base file is configured for "server-mode" (multi-user capable) access to the associated HSQL database. The database files are created automatically in the same folder as the Base file (mydb.server.odb) with the prefix 'mydb'. If a database named 'mydb' is already present in the folder, and that database is currently running in server-mode, then Base will simply be connected to the existing database. For instance, you may have created a database named 'mydb' in that folder using 'mydb.file,odb' which runs HSQLDB automatically in file-mode. It's best to save all server-mode batch/script files used to start-up/shutdown the HSQLDB server in each database folder as well. Also consider this Windows app for server-mode management.
  • Note: Any Base front-end file (.odb) created with the 'Connect to an existing database' wizard may be re-configured for 'server-mode' access. This includes both 'mydb.file.odb' and 'mydb.server.odb'. Simply make a copy of the (.odb) file and follow the steps below to re-configure the file. Access these settings in an existing Base .odb file from the menu bar: Edit > Database > Properties... If this option is not available in Base, then your .odb file was created with the wrong Base wizard, so a new .odb file must be created as follows (don't worry, you can extract your existing embedded-database, while migrating your queries, forms, etc. in most cases).
  • Alternatively ...you can create this 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 >>  and fill-in the 'Data Source URL' and 'JDBC driver class' as follows:
    • Data Source URL (server mode): hsqldb:hsql:// localhost or IP-address:port /;default_schema=true;get_column_name=false
      • For example with HSQLDB running on the local machine: hsqldb:hsql://localhost/;default_schema=true;get_column_name=false
      • For example with HSQLDB running on a network computer: hsqldb:hsql://192.168.1.101/;default_schema=true;get_column_name=false
    • JDBC driver class: org.hsqldb.jdbcDriver
    • Press:  Test Class 
      • Errors may indicate Class Path setup issues
        • Each client must have a local copy of hsqldb.jar with the Class Path setup locally
      • Errors may indicate JDBC driver class syntax issues such as a space after the string: org.hsqldb.jdbcDriver
    • Press: Test Class
    • User name: SA
    • Password required: leave un-checked at this stage
    • Press:  Test Connection 
      • Errors may indicate that HSQLDB is not running in server-mode
      • Errors may indicate database authentication issues. Check username and password
      • Errors may indicate folder access privileges imposed by the operating system and managed by the system administrator
      • Errors may indicate network issues: database folder is not accessible from the client computer
      • Errors may indicate network issues: Data Source URL syntax reflects the wrong IP address or port for the database server
      • Errors may indicate network issues: Router or firewall is blocking the default port 9001 (or 554 if encrypted) to the database server
  • NOTE: each client should have a local installation of *Office with Class Path setup as previously outlined. And it's probably best to distribute separate Base (.odb) front-end files to each client in order to maximize stability and flexibility (custom user Queries, Forms, Reports, etc.). Windows clients can even run *Office/Base portable from USB flash drive with confidence (data reliability) in client-server configurations since no critical data is stored in the Base file or otherwise on the flash drive (see LibreOffice Portable & JavaPortable). On the other hand, it may be advantageous to maintain a single, shared, Base (.odb) file exclusively on the database host computer in order to ease administration and distribution tasks overall, particularly in the early phases of production use. Again, the Base file contains only front-end components, which are easily replaced with any old backup. So critical production data is not at risk even if Base crashes in this configuration due to the transactional nature of the HSQLDB engine. All critical production data is maintained on the database host computer (server) in this client-server configuration.

A database 'server' does not imply a specialized server operating system. Any available networked computer capable of running *Office and Java will do as the database host (shared folder and HSQLDB server). A modern laptop with battery can be a good choice due to battery backup automation, while offering both wired and encrypted wireless access. User authentication is best handled by the database engine (RDBMS), with true security requiring protocol- and database-encryption. These features and more are available with HSQLDB 2.x, H2, and Apache Derby, as well as with enterprise-oriented options listed above.