How to shutdown HSQLDB from Ant

Martin Fowler is of the opinion that Ant is not good enough.

And now Spring has a Java configuration option in the works. So the anti-XML movement is in full swing :)

I’ve personally been happy with extending Ant with Beanshell scripts. Here’s one example. It is easy to start HSQLDB from the command line, but as far as I could figure, the only way to stop the database cleanly is to connect to it and issue the following pseudo-sql: “SHUTDOWN”

So I have this Ant snippet:

<target name="hsqldb-stop">
    <java classname="bsh.Interpreter" fork="true">
        <classpath refid="master-classpath"/>
        <arg line="hsqldb-stop.bsh"/>
    </java>
</target>

And this is how “hsqldb-stop.bsh” looks like:

import java.sql.*;

Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:hsql://127.0.0.1/mydb";
Connection con = DriverManager.getConnection(url, "sa", "");
String sql = "SHUTDOWN";
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
stmt.close();

There. No main method, no compilation, no mess. In true scripting language fashion, even the variable type declarations can be skipped (I haven’t). In this case I had started HSQLDB in “network” mode (not embedded). Both the HSQLDB and Beanshell jars need to be in the “master-classpath”.

And yes, I know there is an Ant task for sql. Consider this just an example. But if you need to conditionally do stuff, Beanshell is perfect.

Update 2008-09-08: For those who are looking for the “right way” to shut down HSQLDB from Ant, here you go:

<target name="hsqldb-stop">
  <sql
    classpathref="master-classpath"
    driver="org.hsqldb.jdbcDriver"
    url="jdbc:hsqldb:hsql://127.0.0.1/mydb"
    userid="sa" password=""
    autocommit="true">SHUTDOWN</sql>
</target>

Update 2009-10-12: Also see this blog post by Frederic Daoud – it looks like HSQDB comes with a class called “org.hsqldb.util.ShutdownServer” that can be used instead.

Speaking for myself personally – I’m much more productive in “normal” Java rather than Groovy, Ruby etc. (yes maybe there is something wrong with me :) And I’ve found myself using Beanshell in all kinds of interesting ways – such as for example a batch file that will connect to a series of Oracle databases and check if they are alive.

Previous blog posts on how to do cool stuff with Ant: Jetty, Maven Integration, Tomcat.

Update 2006-12-20: Just to make a point in response to comment # 1 and discussion on TheServerside.com., here’s one way to get the script into the Ant file itself (and the db name also is passed as a parameter):

<target name="hsqldb-stop">
    <concat destfile="temp.bsh">
        <![CDATA[
            import java.sql.*;
            
            Class.forName("org.hsqldb.jdbcDriver");
            String url = "jdbc:hsqldb:hsql://127.0.0.1/" + bsh.args[0];
            Connection con = DriverManager.getConnection(url, "sa", "");
            String sql = "SHUTDOWN";
            Statement stmt = con.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        ]]>
    </concat>
    <java classname="bsh.Interpreter" fork="true">
        <classpath refid="master-classpath"/>
        <arg line="temp.bsh ${db.name}"/>
    </java>
</target>

Not very pretty but it works. I guess I really don’t want to learn a new language :)

About these ads

15 Responses to How to shutdown HSQLDB from Ant

  1. The only thing I don’t like about that method, vs the ANT SQL task method, is I have to go digging into yet another file to see what it is I’ve done. Yes, this is a simple case, but simple cases should be accomplished as simple as possible. You’ve just created a another layer of abstraction for no reason. This is simpler to me for the simple act of shutting down hsqldb:

    SHUTDOWN

    Also not that much of that mess can be created in a parent target and referenced for all the nifty hsqldb tasks I create in ANT.

  2. Well, crap, my nifty XML was escaped for whatever reason. I think you get the idea though. Cheers.

  3. Peter Thomas says:

    Hey I saw that coming and I did say that it was just an example and that I know about the SQL task :)

    One option would be to stuff the script into a CDATA section and write it out onto the file system using Concat, and then run it in Beanshell.

    Of course there may be many more ways to do this. But the point is: it is easy to mix Ant and scripting – without having to compile anything or mess with your Ant classpath.

  4. Flipouk says:

    I’m glad to see that I’m not alone in using Beanshell to do sysadmin tasks :) I like it very much and I had never thought of integrating it with Ant. Thanks for the nice tip!

  5. ejboy says:

    Scriptella ETL and scripts execution tool has a built-in adapter for HSQLDB which performs DB shutdown on JVM exit:

    http://scriptella.javaforge.com/docs/api/scriptella/driver/hsqldb/package-summary.html

  6. Peter Thomas says:

    I’m not sure if Scriptella helps when I want to do this from Ant? Do you have to modify your Ant classpath?

  7. ejboy says:

    Just declare etl task as it is described in the Scriptella manual. The HSQLDB driver is added in a connection classpath attribute or as a part of Ant taskdef declaration.

  8. Peter Thomas says:

    Ok, but does this help when I start HSQLDB in a forked process in network (not embedded) mode?

  9. Peter Thomas says:

    Hmm ok. But I really think the Ant SQL task is the best option :)

  10. Pingback: Technical Related Notes » Blog Archive » links for 2006-12-19

  11. LucuraMan says:

    This should shoutdown the database:

    SHUTDOWN

    Is there any good tool to design hsqldb databases but more complete than DatabaseManagerSwing ?
    If somebody can answer to the mail, really thanks

  12. LucuraMan says:

    target

    sql

    classpath=”${hjar}” driver=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost:${hport}/${halias}” userid=”sa” password=”” print=”yes”

    SHUTDOWN

    /sql

    /target

  13. Pingback: How to do String operations in Ant « Incremental Operations

  14. Tim Myer says:

    Great post. I slightly modified what you did because I, too, like to be able to do as much as possible using straight Ant with as few dependencies as possible. In my case, I am already using JDK 6, so I used this javascript task:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 29 other followers

%d bloggers like this: