Skip to main content

HyperSQL start/stop scripts

HyperSQL is a terrific lightweight Java database solution that can be embedded to applications. This post outlines a couple of simple scripts for safely starting / stopping a HyperSQL database server instance (hosting one or more individual databases). It also avoids the need to have the main HyperSQL jar file sitting alongside your database files.

In the examples below, I'm configuring a local database instance called 'resdb'.

You’ll need to configure three files:
  • a start-up script called: start_hsqldb_server.bat
  • a connection properties file called sqltool.rc
  • a shutdown script called stop_hsqldb_server.bat

The start-up script looks like this:
#REM start_hsqldb_server.bat
@echo off
set HSQLDB_LIB=<path to hsqldb library>
set DB_FILE=<path to your database .script file [minus the .script part]>
set DB_ALIAS=<database alias name>
start "HSQLDB Server" java -cp "%HSQLDB_LIB%" org.hsqldb.Server -database.0 file:"%DB_FILE%" -dbname.0 "%DB_ALIAS%"
The configurable settings are:
  • HSQLDB_LIB: The path to the /lib directory in your HyperSQL installation, e.g. C:\hsqldb-1.8.0.10\lib\hsqldb.jar
  • DB_FILE: The path to your database .script file, e.g. C:\Temp\fndres\resdb
  • DB_ALIAS: The database alias name you wish to use, e.g. resdb
If configuring more for more than one database, you’ll need to duplicate the DB_FILE, DB_ALIAS and ‘start…’ lines accordingly (remember to change the variable names as well, e.g. DB_FILE1, DB_FILE2…).

The connection properties file ‘sqltool.rc’ (used by HyperSQL’s ‘sqltool’ utility) looks like this:
urlid <unique id>
url jdbc:hsqldb:hsql:<database URL>
username <database username>
password <database user password>
You can think of the sqltool.rc file as a database connection credentials file; here you can define the connection credentials for many HyperSQL databases. The configurable settings are:
  • urlid: The unique ‘id’ for this set of connection credentials. e.g. resdb-sa
  • url: The host URL for the database. Should match the host and alias configured in your start-up script. e.g. //localhost/resdb
  • username: The database user to connect as. Generally this is ‘sa’ unless additional database user accounts have been created.
  • password: The database user’s password. Generally this is blank for the ‘sa’ user, unless it has been explicitly changed

The shutdown script looks like this:
#REM stop_hsqldb_server.bat
@echo off
set HSQLDB_LIB=<path to hsqldb library>
set RC_FILE=<path to the connection properties file 'sqltool.rc'>
set RC_URLID=resdb-sa
java -jar "%HSQLDB_LIB%" --rcFile "%RC_FILE%" --sql "shutdown compact;" "%RC_URLID%"
The configurable settings are:
  • HSQLDB_LIB: The path to the /lib directory in your HyperSQL installation, e.g. C:\hsqldb-1.8.0.10\lib\hsqldb.jar
  • RC_FILE: The path to your sqltool.rc connection properties file. e.g. C:\Temp\fndres\sqltool.rc
  • RC_URLID: The urlid of the connection settings (in the sqltool.rc file) to be used. e.g. resdb-sa
If configuring more for more than one database, you’ll need to duplicate the RC_URLID and ‘java -jar…’ lines accordingly (remember to change the variable names as well, e.g. RC_URLID1, RC_URLID2…). 
 
All going well, after you run the start-up script you should be presented with a console window displaying the output from the database server:

The shutdown script should (safely) stop the database and close the console window.

For further information, refer to the HyperSQL documentation located here: http://hsqldb.org/web/hsqlDocsFrame.html

M.
Labels:

Comments

Post a Comment

Popular posts from this blog

Windows 7 Printer Registry

Here's today's annoyance: Adding a new network printer to Windows 7 failed to update the Windows registry properly. At work the other day I needed to add a new network printer. Pretty easy huh? After browsing for the network device, installing the correct driver and running of a test page I thought I was done. Apparently I was mistaken. Here's what happened after the printer was added: Applications such as Adobe Reader X fail to "see" the added printer. Applications such as Microsoft Word 2010 or Excel 2010 can "see" the printer (kinda) but will not let you select it. Could not set and/or change the default printer, fails with " Operation could not be completed (error 0x00000709). Double check the printer name and make sure that printer is connected to the network. ". Thinking there was something wrong with the network, printer driver and/or ports I (attempted to) uninstall/re-install the printer several times - ending up with severa...
Post a Comment
Read more

Why Me?

Well, here we are, another new year and yet another new blogger. Sigh. Don’t get me wrong, I don’t have anything against blogs - I totally get the point of blogging, I really do! - it’s just that I’m not a journal type of guy. I’m notoriously slack at posting or even returning correspondence on any of these social networking sites. Years have been known to pass before I might get around to letting Bob (not his real name) know that “ yes, I do think that Beck’s is a great beer ”. So why the hell am I writing a blog I hear you ask? Well, 2011 has brought a reasonably significant event in my life: I’m moving. More specifically I’ve left my current job with Company I and will be starting a new job with Company A (not their real names either). I’m also selling my house and moving 832km with my wife and four children from Wollongong, NSW to Melbourne, VIC . Well, slight correction: hopefully selling my house, we haven’t had any offers just yet... so it looks very likely that I’ll be movi...
Post a Comment
Read more

Connecting to HyperSQL with OpenOffice.org

For those of you who wished for a 'user-friendly' graphical interface for a HyperSQL database, your wait is over! OpenOffice.org Base is the preferred graphical environment for a HyperSQL database as OOo actually utilises HyperSQL as its embedded database engine. Here's what you'll need: A HyperSQL database server. You can download HyperSQL from here: http://sourceforge.net/projects/hsqldb/files/ OpenOffice.org Base. You can download the full OpenOffice.org suite from here: http://download.openoffice.org/   And here's how to get it up and running: 1. Extract the downloaded HyperSQL bundle to a location on your local machine, e.g. C:\hsqldb-1.8.0.10 [optional]: Add the following entries to your system environment variables: name: HSQLDB_HOME value: <path to your HyperSQL installation, e.g. C:\hsqldb-1.8.0.10> name: PATH value: ;%HSQLDB_HOME%\lib; Note: This value should be added to your existing PATH value! 2. Install OpenOffice.org. 3. Confi...
Post a Comment
Read more