Hypersonic SQL

Hypersonic SQL (HSQLDB) is a compact, high-performance relational database management system (RDBMS) written in Java. It supports both in-memory and file-based storage options and can be run in server mode for remote access. Martini is a lightweight Java framework designed for building high-performance web applications and microservices. Integrating Hypersonic SQL with Martini Runtime allows developers to utilize HSQLDB's database capabilities within a Martini application, whether for remote access or file-based storage.

Configuring Hypersonic SQL

This guide covers how to configure Hypersonic SQL for both remote and file-based storage scenarios using Martini Runtime, enabling you to leverage HSQLDB's capabilities within a Martini application.

Configuration Guide

Via API Explorer: Database connections can be configured using the Martini API Explorer, a user-friendly interface designed for efficient interaction with the system's APIs. Follow these steps to configure the database connections through the API Explorer:

1. Access the API Explorer: Open a web browser and go to [HOST]:8080/api-explorer/ to access the API Explorer.

2. Navigate to the Database Tag: Locate and click on the database tag within the API Explorer to view various database configuration and operation endpoints.

3. Configure Connection Pool: Use the esbapi/databases endpoint found under the database tag to save a database connection pool.

4. Use POST to Save a Connection Pool: Save the connection pool by using the POST method at the /esbapi/databases endpoint. Here is a minimum requirement for a JSON payload:

Remote setup:

 {
     "name": "[name]",
     "type": "jdbc",
     "url": "jdbc:hsqldb:hsql://[HOST]:[PORT:9001]/[dbName]",
     "driverClassName": "org.hsqldb.jdbc.JDBCDriver",
     "username": "sa",
     "password": ""
 }

File setup:

 {
     "name": "[name]",
     "type": "jdbc",
     "url": "jdbc:hsqldb:file:[PATH]/[dbName]",
     "driverClassName": "org.hsqldb.jdbc.JDBCDriver",
     "username": "sa",
     "password": ""
 }

• driverClassName: JDBC driver for Hypersonic SQL
• username: The default system administrator account is 'sa', but you may use a different user account depending on your setup.
• password: Ensure to provide a password if authentication requires it.

Via File System: Alternatively, database connections can be modified directly in the file system by editing the <name>.dbxml file located in the /<martini-home>/conf/db-pool/ directory. The <name>.dbxml file refers to the specific file for your database configuration. This method is particularly useful in environments with restricted API access or during initial setup stages.

To modify database connections directly through the file system, follow these steps:

1. Locate the Configuration File: Navigate to the /<martini-home>/conf/db-pool/ directory on your server and locate the <name>.dbxml file.

2. Edit the Database Connections: Open the <name>.dbxml file in a text editor and modify the database connection properties.

3. Save and Restart: After making the desired changes, save the file. It is essential to restart the Martini Runtime instance to apply the new settings. This ensures that all components of the application use the updated JDBC configuration. Here is a minimum requirement for a dbXML:

Remote setup:

 <database>
     <name>[name]</name>
     <type>jdbc</type>
     <url>jdbc:hsqldb:hsql://[HOST]:[PORT:9001]/[dbName]</url>
     <driverClassName>org.hsqldb.jdbc.JDBCDriver</driverClassName> <!-- JDBC driver for Hypersonic SQL -->
     <username>sa</username> <!-- The default system administrator account is 'sa', but you may use a different user account depending on your setup. -->
     <password></password> <!-- Ensure to provide a password field if authentication requires it. -->
 </database>

File setup:

 <database>
     <name>[name]</name>
     <type>jdbc</type>
     <url>jdbc:hsqldb:file:[PATH]/[dbName]</url>
     <driverClassName>org.hsqldb.jdbc.JDBCDriver</driverClassName> <!-- JDBC driver for Hypersonic SQL -->
     <username>sa</username> <!-- The default system administrator account is 'sa', but you may use a different user account depending on your setup. -->
     <password></password> <!-- Ensure to provide a password field if authentication requires it. -->
 </database>

Configuring Hypersonic SQL in Martini Designer

1. Navigate to the Navigator view and right-click on the Databases node of the instance where you intend to add the database connection.
2. Choose New > Database Connection.
3. Specify a name for the connection and select the database type JDBC.
4. Fill in the database connection details in the provided form.
5. Save the changes.

Database Properties

These properties include various settings that control the operation of the database. For detailed information on configuring these settings, refer to the JDBC database properties documentation.