3

I am using HSQL in memory database for test purpose of my application and using SQL Server as main database, now when am doing test then HSQL Database is being populated with same data that I have in my SQL Server, now I am trying to test particular service which is retrieving data from Database(it would query MS Server if directly service is run or it will query HSQL Database if called from test)

I am able to see data from MS Server when I run the query but HSQL Db does not return any data if am running same query on it. My hunch here is that HSQL DB is not being populated with the data, is there a way where in I can go and look in what tables I have in HSQL DB and how data is being populated in HSQLDB, i want to see data and i do not have any gui client to see how HSQL Database is populated and what data goes in what table. Do we have a good client for the same and how can I connect to HSQL Database using it and also how can I actually see things happening under HSQL Cover rather than just assuming that HSQL is being populated properly with what we have in SQL Server?

Any suggestions would go long way?

Rachel
  • 91,207
  • 112
  • 255
  • 361
  • 2
    Look here: http://stackoverflow.com/questions/4990864/best-sql-browser-for-hsqldb – Perception Aug 13 '11 at 22:46
  • @Perception: I am not able to see my database, I am providing all connection details properly. – Rachel Aug 13 '11 at 22:58
  • Have you verified that you are actually able to write to the database, then read back from it? – Perception Aug 13 '11 at 23:04
  • Yes, before running the unit test, I am populating HSQL database with all data present in MS Server, but I am not able to go and check it – Rachel Aug 13 '11 at 23:09
  • Can you show the jdbc url you are using? – Clark Bao Aug 14 '11 at 05:32
  • I think you should also verify in the test case, you are able to write to the database, then read back from it. – Clark Bao Aug 14 '11 at 05:39
  • I guess the problem you met is due to the jdbc url. – Clark Bao Aug 14 '11 at 05:45
  • @Clark, that is what i want to verify if data is being properly loaded into the HSQL database and to do that I have to see tables inside HSQL DB but right now I am not able to see and am not sure if am connecting in correct manner also, issue here is that I am connecting via HSQL Database Manager and passing in all the information, also think to note here is that i am passing standard username sa and blank password for the connection and passing my database name as hsql:mem:database name in url and it is not connecting and there are not tables inside it and so am not sure what's happening. – Rachel Aug 14 '11 at 13:54
  • @Rachel You are using the embed mode which means only in the same JVM , you can connect to your in memeory data base. That's why I said you have to load your data just in the same process of your test case. And in this mode , you cannot use those client to connect from network. I didn't find out how to set up server mode url for hsql. BUT it will be similiar to jdbc:derby://myhost:1527/memory:myDB. – Clark Bao Aug 14 '11 at 13:59

3 Answers3

10

Here I add a detailed steps of

How To Running a memory-only HSQLDB in server mode?

1) Download the latest version hsqldb-2.2.5.zip

2) unzip it,open the bin folder

3) modify the runServer.bat like cd ..\data @java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server -database.0 mem:aname -dbname.0 aliasdb and click the runServer.bat to start server

4) run the runManager.bat, select Server type, enter the url jdbc:hsqldb:hsql://localhost/aliasdb,connect, create some test table and test data

5) run the runManager.bat again to start another client,select Server type, enter the url jdbc:hsqldb:hsql://localhost/aliasdb, connect, you will find the data you created.

Ali Seyedi
  • 1,627
  • 1
  • 19
  • 23
Clark Bao
  • 1,683
  • 3
  • 21
  • 37
2

The best way to use HSQLDB for development is running a Server instance (which could store data purely in memory). While the server is on, you can connect from multiple clients, including GUI, to test and browse the data.

The URL form jdbc:hsqldb:hsql://localhost is used to access the server, while the server itself is using jdbc:hsqldb:mem:test as its internal memory database. The server must be started first with a command like this:

java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 mem:test
fredt
  • 22,590
  • 3
  • 37
  • 60
  • i entered url pointing to localhost in url enter box but nothing happened, not sure why? – Rachel Aug 14 '11 at 01:55
  • You should start the server first. Follow the example in HSQLDB Guide here http://hsqldb.org/doc/2.0/guide/running-chapt.html#running_modes-sect – fredt Aug 14 '11 at 14:50
  • Caused by: java.lang.ClassNotFoundException: org.hsqldb.server.Server at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:247) Could not find the main class: org.hsqldb.server.Server. Program will exit. – Rachel Aug 14 '11 at 15:26
  • It seems you are using version 1.8. I have edited the command to work with all versions. – fredt Aug 14 '11 at 15:29
  • [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) entered [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) exited [Server@83cc67]: Startup sequence initiated from main() method [Server@83cc67]: Loaded properties from [C:\server.properties] [Server@83cc67]: Initiating startup sequence... – Rachel Aug 14 '11 at 15:32
  • can i chat with you on the issue? – Rachel Aug 14 '11 at 15:33
  • Sure, if chat is on another location, let me know where. – fredt Aug 14 '11 at 15:38
  • @Rachel let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2464/discussion-between-fredt-and-rachel) – fredt Aug 14 '11 at 15:38
0

You can use Derby In-memory-database and H2 in-memory-database. They are good.but H2 has a better performance. H2 has a web console GUI and Derby has eclipse plugin GUI. You can try it. I have used them in the project. H2 Database has a tag in SO and its auther will support you in SO. I think fredit is right for using server mode instead of embed mode. But his url doesn't seem to be right. At least it should use mem. For Derby, the url is jdbc:derby://myhost:1527/memory:myDB;create=true. This is the exmple url for H2 jdbc:h2:tcp://localhost/mem:db1.

You need to use server mode, so client can connect to it from network. One thing you need take care is ,in memory mode, since it's in memory, when your java process is over,the JVM is not there, the data in DB is also lost. But for testing,it becomes an advantage. You don't need to clear the test data.

H2 has a very good feature for test purpose.It has SQL support which runs compatibility SQL for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, MySQL, Oracle, and PostgreSQL. Which means even you use special SQL in MS SQL Server can get the same result in H2. I believe you will like it.

the links

I know HSQL has in memory mode, but i haven't used it.

Good luck.

UPDATE

I believe if you instead write query in your test case, it will return results. I have done this a lot in my test case. Even in embed mode as long as you create your JDBC connection in your test case. If you feel this is inconvenient, you can even dump the data output to files.But you must load the data into the HSQL in your test case, you can use BeforeClass to do it.If you cannot do this,You have to use server mode.

The hsql seems to be a bit different in JDBC url for server memory mode

for details refer to http://hsqldb.org/doc/guide/guide.html#N108D2 Connections

You have to deal with some server configs.

@Rachel, I Finally find a link to help you set up server mode with in memory mode. Look at this. It's a bit complex.

"Running a memory-only HSQLDB in server mode"

NOTE

The second link didn't tell in detail how to connect correctly. So try the first one.

Community
  • 1
  • 1
Clark Bao
  • 1,683
  • 3
  • 21
  • 37
  • Thanks Clark for the suggestion, our project is using HSQL database and so I need to find a way wherein I can go ahead and find options to browse through while test is being run in debug mode. – Rachel Aug 14 '11 at 01:49
  • Rachel try this Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", ""); – Clark Bao Aug 14 '11 at 02:28
  • I believe in this SO topic, it has some clue http://stackoverflow.com/questions/199598/100-in-memory-hsql-database you don't need to browse it. Just run query use JDBC. – Clark Bao Aug 14 '11 at 02:28
  • Where should I enter `Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");` becuase i am using HSQL Database Manager, also am using database.properties which has information for test database along with it's details and password information but again that is main database which we are using and HSQL contains same data which I am hitting in my test case, am I making some sense here? – Rachel Aug 14 '11 at 13:56
  • You need to first use this Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", ""); to create your database in your test case normally in @BeforeClass. Then use JDBC to load data into it. If you want to let hsql running standlone like network server. Try a server mode. – Clark Bao Aug 14 '11 at 14:04
  • @Clark Bao : If you have never used HSQLDB's in-memory mode and do not recognize the URL to connect to a HSQLDB server, you shouldn't be answering this question with infomration about other databases. Your "update" part is valid, but the original answer is OT. – fredt Aug 14 '11 at 15:06
  • @Fredit But it doesn't matter it's such a similiar thing with only a little difference. – Clark Bao Aug 14 '11 at 21:56
  • @Fredit Atually I am thinking that if she can switch to another database, then it will only cost several minutes. And it will be a work around at least. – Clark Bao Aug 14 '11 at 22:08
  • @Fredit I find you are from hsql.org.No offence,just curious, Could you tell me why hsql server mode with in memory in hsql use a different from Derby H2 to config? – Clark Bao Aug 14 '11 at 22:20
  • @Clark - I am still not able to get data in HSQL DB – Rachel Aug 15 '11 at 00:05
  • @Rachel I am in Shanghai Time, for now it's 10 am. We can discuss in SO when you are free. – Clark Bao Aug 15 '11 at 02:12
  • @Clark, I would like to chat with you on this, if that is an possible option. – Rachel Aug 17 '11 at 16:04
  • @Rachel Can you show the error stack trace and jdbc url you tried? In your last error message and the version of hsql you tried? – Clark Bao Aug 18 '11 at 22:42