Spring and hsqldb

[marcmorrison]

Hi,
I've recently been working through the Spring-MVC-step-by-step example.
I got everything working successfully but I don't know how to make hsqldb start up automatically rather than manually using a .bat script which is how it's done in the walk-through.

I need to do this so that I can deploy a war file to any tomcat server and know that hsqldb will be started up.

Any help much appreciated.

Thanks,

Marc

[denis.zhdanov]

Look at In-Process Mode

[marcmorrison]

Thanks for the link!

I've made a change to my build.properties and stopped the hsqldb server:

db.user=sa
db.pw=
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:file:test, ${db.user}, ${db.pw}
#db.url=jdbc:hsqldb:hsql://localhost [previous configuration]

So I've changed the url but I'm getting the following error when I try the ant script that prints a tables contents:

[echo] PRINT DATA USING: org.hsqldb.jdbcDriver jdbc:hsqldb:file:test, sa,

[sql] java.sql.SQLException: Table not found in statement...

Can you tell me where I'm going wrong here? Am i on the right track?

Thanks,

Marc

[denis.zhdanov]

1. Remove ${db.user} from the url;
2. Remove ${db.pw} from the url;
3. Make sure correct database path is used at the url ('test' is used at the moment);

[marcmorrison]

Thanks again for the response.

I've cleared out my db folder for a clean start and made the changes:

db.user=sa
db.pw=
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:file:/springapp/db/test

I then ran 3 ant commands:

1. Create table
2. Add data to table
3. Print Data

This worked initially. However I then ran a separate print command and again it said no table found. If I go to my file system /springapp/db/ I can see there is no 'test' db here. does this need to be set up again?

Thanks for your patience,

Marc

[marcmorrison]

update: if I add the full filepath I see the 'test' files created, e.g. test.log, test.properties, test.script

However, if I then try to add or print data i get:

[sql] Failed to execute: SELECT * FROM ff_user_info
[sql] java.sql.SQLException: Table not found in statement [ SELECT * FROM ff_user_info]

It's as if my table is not persisting?

[denis.zhdanov]

So, the problem is that your data don't survive application restart? Or you can't access the data written to the table before?

Hsqldb specifies the following contract for the default table type (MEMORY):

Memory tables are the default type when the CREATE TABLE command is used. Their data is held entirely in memory but any change to their structure or contents is written to the <dbname>.script file. The script file is read the next time the database is opened, and the MEMORY tables are recreated with all their contents. So unlike TEMP table, the default, MEMORY tables are persistent.

[marcmorrison]

Hi - my problem appears to be the latter: can't access the data written to the table before.

So, in theory as I have not specified otherwise I have a MEMORY table and the table itself and changes made to it should be persisted via the .script file. This doesn't appear to be the case however...

What I can't understand is why the same scripts created persistent data when i used db.url=jdbc:hsqldb:hsql://localhost rather than jdbc:hsqldb:file:C:/Projects/SpringProject/Server/ServerCode/springapp/db/test

Maybe I should try a TEXT table?

[marcmorrison]

The following seemed to fix the persistency problem:

jdbc:hsqldb:file:../springapp/db/test; type=cached; shutdown=true


Thanks,

Marc