Date Created: Sat 25-Sep-2010

Related Document Categories:

Get my WebSphere Application Server course here >> http://www.themiddlewareshop.com/products/


How to query a HSQLDB using a GUI tool

While learning the basicsof ZK, I wanted to understand how to query the Java Database.. HSQLDB is a relational database engine written in Java. HSQLDB (HyperSQL DataBase) is the leading SQL relational database engine written in Java

I have a single table DB file located in

C:\hsqldb

called event, and I wanted to see what was in the DB.

the DB Path is located @ c:\hsqldb\event


It had a script file [event.script].containing, which is used by my EAR file. The root user is SA, password is ""


CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE EVENT(ID VARCHAR(30),NAME VARCHAR(30),PRIORITY INTEGER,DATE TIMESTAMP)
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO EVENT VALUES('d1f0b9d5-0d24-435f-ad95-25c04dc3e219','Read ZK Tutorial',1,'2007-07-24 00:00:00.000000000')
INSERT INTO EVENT VALUES('80bf9d61-43a5-4143-a547-c86d0206355f','Develop application with ZK',1,'2007-07-24 00:00:00.000000000')

I had a props file called [event.properties] conatining


#HSQL Database Engine 1.8.0.7
#Fri Sep 24 23:12:06 BST 2010
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=memory
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

===============================================

I wanted to run a GUI to query the DB

downloaded hsqldb_1_8_1_3.zip

unzipped to

C:\hsqldb_1_8_1_3

created shortcut using %COMSPEC% with working directory C:\hsqldb_1_8_1_3



created a bat file to run the Database Tools Manager, which us a simple Java Application.






The bat file contains

java -cp hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager





I got the error The Database is already in use by another process: lockfile: org. etc etc



See full error below.

java.sql.SQLException: The database is already in use by another process: lockFi
le: org.hsqldb.persist.LockFile@2f32ee9c[file =C:\hsqldb\event.lck, exists=true,
locked=false, valid=false, ] method: checkMagic reason: java.io.IOException: Th
e process cannot access the file because another process has locked a portion of
the file
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.hsqldb.util.ConnectionDialog.createConnection(Unknown Source)
at org.hsqldb.util.ConnectionDialog.actionPerformed(Unknown Source)
at java.awt.Button.processActionEvent(Unknown Source)
at java.awt.Button.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.Dialog$1.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

The lock was taken by my ZK EAR file application running in WebLogic

I stopped weblogic and I was able to connect to the Event Database. I currently have a table called Event inside the Event DB



I then ran the command select * from event




The result was:





So there we have it, a simple example of how to query a HSQLDB using a GUI tool.

Get my WebSphere Application Server course here >> http://www.themiddlewareshop.com/products/

Steve Robinson - IBM Champion 2013

About Me

Steve Robinson has been working in IT for over 20 years and has provided solutions for many large-enterprise corporate companies across the world. Steve specialises in Java and Middleware.

In January 2013, I was awarded the prestigous 'IBM Champion' accolade.


  • Linked In
  • Twitter
  • About Me
  • My Blog
  • Contact Me

Read my books?

IBM WebSphere Application Server 8.0 Administration Guide

IBM WebSphere Application Server 8.0 Administration Guide

WebSphere Application Server 7.0 Administration Guide

WebSphere Application Server 7.0 Administration Guide

WebSphere Categories

Other Categories