Purpose

Give a simple solution to accessing HSQL database via command line

Resolution

 Be sure to edit "path/to/" in the below examples with the full context path to "bamboo-home/database/defaultdb", "hsqldb-1.8.0.7.jar", and "sqltool.rc"

1. First, create the sqltool.rc file populated with the following:

   # sqltool.rc 
    
   urlid defaultdb
   url jdbc:hsqldb:path/to/bamboo-home/database/defaultdb;shutdown=true
   username sa
   password

2. Next, stop Bamboo 

    HSQL database cannot be accessed while in use by Bamboo or any other source

3. Run the following via command line adding the org.hsqldb.util.SqlTool class as well as the path to sqltool.rc

   $ java -cp path/to/hsqldb-1.8.0.7.jar org.hsqldb.util.SqlTool --rcFile path/to/sqltool.rc defaultdb

4. Successful connection will look like below:

   $ java -cp ~/atlassian-bamboo-5.12.3.1/lib/hsqldb-1.8.0.10.jar org.hsqldb.util.SqlTool --rcFile ~/sqltool.rc defaultdb
   JDBC Connection established to a HSQL Database Engine v. 1.8.0 database as 'SA'.
   SqlTool v. 1.55. (SqlFile processor v. 1.135)
   Distribution is permitted under the terms of the HSQLDB license.
   (c) 2004-2005 Blaine Simpson and the HSQLDB Development Group.
   \q to Quit.
   \? lists Special Commands.
   :? lists Buffer/Editing commands.
   *? lists PL commands (including alias commands).
   SPECIAL Commands begin with '\' and execute when you hit ENTER.
   BUFFER Commands begin with ':' and execute when you hit ENTER.
   COMMENTS begin with '/*' and end with the very next '*/'.
   PROCEDURAL LANGUAGE commands begin with '*' and end when you hit ENTER.
   All other lines comprise SQL Statements.
   SQL Statements are terminated by either a blank line (which moves the
   statement into the buffer without executing) or a line ending with ';'
   (which executes the statement).
   SQL Statements may begin with '/PLVARNAME' and/or contain *{PLVARNAME}s.
    
   sql>

Additional Options:

SQL commands can be ran directly from command line using the –sql 'your query here' option:

$ java -cp ~/atlassian-bamboo-5.12.3.1/lib/hsqldb-1.8.0.10.jar org.hsqldb.util.SqlTool --sql 'SELECT * FROM GROUPS;' --rcFile ~/sqltool.rc defaultdb

   ID  GROUPNAME

-----  ------------

32769  bamboo-admin