Addition of ESB tables to JBoss Hypersonic Database

 

Background

JBoss server comes with a pre installed hypersonic database (HSQLDB). The database can only be accessed in the same JVM as it is it runs in the in-process mode.

 

The process to create ESB tables in the HSQLDB for simpler installation and use of ESB as a product is as follows.

Note: Use of HSQLDB for production is not recommended.

 

Steps

  1. Uncomment the following section in hsqldb-ds.xml which is in the JBoss domain.

   <mbean code="org.jboss.jdbc.HypersonicDatabase"

     name="jboss:service=Hypersonic">

     <attribute name="Port">1701</attribute>

     <attribute name="BindAddress">${jboss.bind.address}</attribute>    

     <attribute name="Silent">true</attribute>

     <attribute name="Database">default</attribute>

     <attribute name="Trace">false</attribute>

     <attribute name="No_system_exit">true</attribute>

   </mbean>

This step will make sure that TCP connections can be made to Hypersonic. JBoss uses MBeans to connect to Hypersonic DB.

 

  1. Start the JBoss Server and make sure it is up and running. This step is to start the HSQLDB database which is part of server startup process.

 

  1. Create a hsqldb.rc file in the directory product\docs\Install\database.

The rc file contains the following parameters

 

urlid JBoss-LocalDB

url jdbc:hsqldb:hsql://localhost:1701

username sa

password

           

The hsqldb.rc file will be used by the Hypersonic SQL Tool to connect to the DB and using the parameters defined in the file.

 

  1. Run the following java command.

java –cp $JBOSS_HOME/server/${DOMAIN}/lib/hsqldb.jar org.hsqldb.util.SqlTool --autoCommit --continueOnErr --rcfile hsqldb.rc JBoss-LocalDB JBossESB-DB-HSQL.sql

 

The command runs a HSQLDB SQLTool in non interactive mode and uses the urlid called JBoss-LocalDB as defined in the hsqldb.rc file to connect to the DB. It takes as input a SQL file with SQL scripts in it. The SQL file mentioned above in the command is JBossESB-DB-HSQL.sql

 

JBOSS_HOME – Home directory where JBoss is installed. If JBoss is installed in C:\JBoss then JBOSS_HOME refers to C:\JBoss.

 

DOMAIN  - The domain where hsqldb libraries exist. Mostly default directory.