Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

connect to HSQLDB in JBoss 7.1 from external

2012-08-10
2014-01-19
  • I have recently downloaded hsqldb from sourceforge and configured it in JBoss 7.1 Final (Brontes). The in-memory database is starting up as soon as my application starts up. I can access, modify, populate the database from my application. Unfortunately I am not able to connect to it through hsqldb swing dataviewer or squirrel. While trying to connect, I am getting a new database.

    More so, JBoss 7 has moved away from mbeans, which means I cannot use jmx-console to get a dataviewer.

    I appreciate any help in this regard. Below is my excerpt of what all I did in standalone.xml of JBoss. Is there anything more I need to do ? (assuming that mbeans do not work in JBoss 7.1 final Brontes)

                    <datasource jta="true" jndi-name="java:/InMemoryDB" pool-name="InMemoryDB" enabled="true" use-java-context="true" use-ccm="true">
                        <connection-url>jdbc:hsqldb:mem:inmemorydb://localhost:1701</connection-url>
                        <driver>hsqldb</driver>
                        <pool>
                            <min-pool-size>10</min-pool-size>
                            <max-pool-size>100</max-pool-size>
                            <prefill>true</prefill>
                            <flush-strategy>FailingConnectionOnly</flush-strategy>
                        </pool>
                        <security>
                            <user-name>sa</user-name>
                        </security>
                    </datasource>
                    <drivers>

                        <driver name="hsqldb" module="org.hsqldb">
                            <driver-class>org.hsqldb.jdbcDriver</driver-class>
                        </driver>
                    </drivers>
                </datasources>

     
  • Fred Toussi
    Fred Toussi
    2012-08-10

    I don't know enough about JBoss configuration to answer this.  But your URL is incorrect as it mixes the mem: protocol with server:port specification. A URL for external connections is like "jdbc:hsqldb:http://localhost/etc"

    You can try this with HSQLDB 2.2.9:

    Set up the org.hsqldb.server.Servlet as a servlet in JBoss. Configure it to connect to the mem: database in question with the URL "jdbc:hsqldb:mem:yourmemdb". Your JBoss web apps connect to the database with this URL.

    For external connections, such as Squirrel, connect to the servlet using a URL like "jdbc:hsqldb:http://localhost:80/hsqldbservlet/" which points to the servlet on your app server  (the port and servlet name depend on how you configure the Servlet). You can connect with a web browser to the servlet, which shows an HTML page confirming it is running.

     
  • Hi Fred, Thanks a lot for your reply. 
    But I have no issue in connecting to the database through my application as both are running within JBoss.  Actually my Startup Servlet is generating and populating record. My objective is to access this database running within JBoss JVM from an external User Interface or HSQLDB graphical user interface.

    In earlier version of JBoss it was possible. 

     
  • Fred Toussi
    Fred Toussi
    2012-08-10

    Yes, I know what you want to do. Please read my answer again.

    The org.hsqldb.server.Servlet is a special servlet which allows you to connect a GUI (or any external application) to your database which is embedded in JBoss.

     
  • Hi Fred, Thanks for your great suggestion. I incorporated the program into my application. There was an error. DBTYPE was resolved as file: . I hardcoded it into mem:

    Then I connected to the url the servlet is configured in web.xml : http://localhost:8080/CacheService/dbservice/hsqldb

    Now I got a page which reads :

    **_HSQL Database Engine Servlet
    The servlet is running.
    The database is also running.

    Database name: mem:jdbc:hsqldb:mem:inmemorydb

    Queries processed: 0
    _**

    But I am still at loss….. I need to see the schemas  and tables and table data. It seems this servlet gives me capabilities to do these operations, but I have to write my own code. (I still tried to provide this servlet url to squirrel and hsqldb swing with a faint hope that they might recognise what it is. But to no avail)

    I know I am asking stupid questions.  But still .. is there any application already there, which can provide me database view with this servlet url configured into it?

     
  • Fred Toussi
    Fred Toussi
    2012-08-10

    When you connect with a web browser, you get the above message to tell you everything is working fine.

    Now if you use Squirel or any other GUI database manager and connect to this URL using the user name and password for your mem: database, you can interact with the database:

     jdbc:hsqldb:http://localhost:8080/CacheService/dbservice/hsqldb
    
     
  • Thanks Fred for all your support.

    I tried to connect externally through squirrel and got following error. (The JBoss JVM I am running has lots of free space. Only 16% of 1GB heap is utilized)

    ava.lang.RuntimeException: java.sql.SQLTransientConnectionException: connection exception: connection failure: java.lang.OutOfMemoryError: Java heap space
    at java.util.concurrent.FutureTask$Sync.innerGet(Unknown Source)
    at java.util.concurrent.FutureTask.get(Unknown Source)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.lang.RuntimeException: java.sql.SQLTransientConnectionException: connection exception: connection failure: java.lang.OutOfMemoryError: Java heap space
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
    … 6 more
    Caused by: java.sql.SQLTransientConnectionException: connection exception: connection failure: java.lang.OutOfMemoryError: Java heap space
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver$1.run(Unknown Source)