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.


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