Installing HSQL on Amazon EC2

Recently we were required to develop a web application for a client and deploy on Apache Tomcat hosted on Amazon EC2. The application had very simple persistence requirements as it mostly dealt with integrating content from various sources such as web services. Therefore we decided to use HSQL as the database. It took a while to set up HSQL on Amazon as initially the application just couldn't connect to the database although it had been installed correctly (caused by having different versions of the hsqldb.jar file in the web app and deployed on EC2). This post will hopefully reduce the pain of anyone attempting the same.

I'm assuming you have correctly installed hsql via "sudo yum install"
As hsql is installed without a password by default, the first thing you need to do is set the password for the db.

1. Navigate to the hsql installed directory "cd /var/lib/hsqldb"
2. Start the db using  "sudo java -cp lib/hsqldb.jar org.hsqldb.Server - database.0 file:data/db0 -dbname.0 firstdb &" This will start the database with an alias of "firstdb" and create the files under the "data" directory.
3. The next step use the command line sql tool to access the db.
4. Copy the "sqltool.rc" file from the hsqldb directory to the user's home directory "cp /var/lib/hsqldb/sqltool.rc ~/sqltool.rc"
5. Execute the following command to start the tool : "java -jar /var/lib/hsqldb/lib/hsqldb.jar db0-url" This will provide an "sql>" prompt.
6. Now set the password "sql> set PASSWORD password;" (syntax may not be 100% correct)
7. Now shutdown the database "sql> shutdown;"
8. Quit the sqltool "sql> \q"
9. At this stage the hsqldb should have been shutdown correctly with the new password configured. Restart the hsql database as before in step 1 and 2.
10. To check that the password has been changed, try accessing the database with the sqltool again from step 5. It should fail to connect.
11. From the home directory, edit sqltool.rc to change the password used to access the database. "vi sqltool.rc"
12. Find the urlid db0-url that represents your db. It has 4 properties: urlid, url, username and password. The value for password is probably empty so change that to "password". Save and exit vi.
13. Now try accessing the database again from step 5. It should connect successfully.