HSQL DB + JBoss + Ant

Do you want to fill/update the contents of the Hypersonic SQL database that is running under JBoss by running an Ant task?
(
General JBoss info:
In your JBOSS_HOME, you have the server/all, server/default and server/minimal dirs. Usually, one uses the default server set-up configuration, so we are looking at that one.
)

Recipe for addressing HSQL-DB under JBoss via Ant:

1. Edit the hsqldb-ds.xml in the server/default/deploy directory.
   Nice feature:
   JBoss automagically detects changes when files are placed/removed/modified in this directory, so once you save your changes, JBoss should automatically incorporate the changes, or start complaining if you did something wrong!
   • Make sure your one and only connection URL entry is as follows:

     <connection-url>
     jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}fillInYourDB
     </connection-url>
     

   • In the </local-tx-datasource> element, make sure that you have an associated

     <depends>
     jboss:service=Hypersonic,database=fillInYourDB
     </depends>
     

   • Have the following two sections uncommented:

        <mbean code="org.jboss.jdbc.HypersonicDatabase"
          name="jboss:service=Hypersonic">
          <attribute name="Port">1701</attribute>
          <attribute name="Silent">true</attribute>
          <attribute name="Database">fillInYourDB</attribute>
          <attribute name="Trace">false</attribute>
          <attribute name="No_system_exit">true</attribute>
        </mbean>
     

     and

        <!-- This mbean can be used when using in process persistent db -->
        <mbean code="org.jboss.jdbc.HypersonicDatabase"
          name="jboss:service=Hypersonic,database=fillInYourDB">
          <attribute name="Database">fillInYourDB</attribute>
          <attribute name="InProcessMode">true</attribute>
        </mbean>
     

2. You can now enter an SQL section. We’ll demonstrate a DROP tables one here, since it is the shortest, but once you can drop, you can easily extend to CREATE and INSERT, of course.

   <target name="dropTables">
     <java classname="org.hsqldb.util.ScriptTool" fork="yes" >
       <arg value="-url"/>
       <arg value="${url}"/>
       <arg value="-database"/>
       <arg value="//localhost:1701"/>
       <arg value="-script"/>
       <arg value="sql/drop.sql"/>
       <classpath refid="hsql.classpath" />
     </java>
   </target>
   

   The hsql.classpath must have been defined at the top of your build.xml with

    <path id="hsql.classpath">
       <pathelement location="${jboss.server}/lib/hsqldb.jar"/>
    </path>
   

   The SQL script itself is, as you can read, located in the directory sql/drop.sql, relative from the location of build.xml:

   DROP TABLE myTable1;
   DROP TABLE myTable2;
   DROP TABLE myTable3;
   

   The associated build.properties must at least contain:

   jboss.home=/usr/local/jboss
   jboss.server=${jboss.home}/server/default
   user=sa
   password=
   url=jdbc:hsqldb:hsql:
   

3. Finally, run ant dropTables, and everything should be up and running!