Friday 28 September 2012

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.

8 comments:

  1. I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
    Java training in Chennai

    Java training in Bangalore

    ReplyDelete

  2. This is quite educational arrange. It has famous breeding about what I rarity to vouch. Colossal proverb.
    This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved. This arrange is synchronous s informative impolites festivity to pity. I appreciated what you ok extremely here 


    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training

    ReplyDelete
  3. I am happy for sharing on this blog its awesome blog I really impressed. thanks for sharing.

    Become an Expert In Software Testing Training! The most trusted and trending Programming Language. Learn from experienced Trainers and get the knowledge to crack a coding interview, @Softgen Infotech Located in BTM Layout.

    ReplyDelete
  4. wow really nice. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.


    Java training in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Online Training

    ReplyDelete