HSQL db

evhecke · **Posted:** Tue Apr 03, 2007 11:52 am

Hi!
I need some help with hibernate and hsqldb.
I'm trying to save some data, but it seems that the insert data isn't written into the script file of my hsqldb.
I can read from the db without a problem, so the mapping seems to be ok. When I add a Question object, it is displayed (second part of the code), but when I restart my application the new Question is gone again.
Who can help a dansle in distress?

This is my code:

public static void main(String[] args) {

Question q=new Question("user4");
Session session = HibernateUtil.currentSession();
Transaction tx = null;
tx = session.beginTransaction();
session.save(q);
tx.commit();

Session sessionNew = HibernateUtil.currentSession();
Transaction tx2 = sessionNew.beginTransaction();
List result = sessionNew.createQuery("from Question").list();

Question resultQ=null;
System.out.println("Query resultaat: ");
for(int i=0;i<result.size();i++)
{
resultQ=(Question)result.get(i);
System.out.println(resultQ.getDescr());
System.out.println(resultQ.getId());
}
HibernateUtil.closeSession();
}

shirish_sakhare · **Posted:** Wed Apr 04, 2007 3:16 am

Hello,

Check for the property hbm2ddl in the hibernate.cfg.xml file.

This property should be commented out.Other wise it will drop and recreate the database schemas every time you restart the application (Suitable for Unit testing not for development).

Regards,
Shirish

evhecke · **Posted:** Wed Apr 04, 2007 11:51 am

Hi Shirish

unfortunately, this doesn't solve my problem. I didn't have the hbm2ddl property in my config file. Even if I add it and put it on false, it doesn't work. The data isn't saved.

Els

steckemetz · **Posted:** Thu Apr 05, 2007 11:13 am

Are you sure that

Code:

HibernateUtil.closeSession();

does a commit ?

evhecke · **Posted:** Fri Apr 06, 2007 4:27 am

tx.commit(); should do the commit, no?

christian · **Posted:** Fri Apr 06, 2007 4:29 am

Please don't use that old HibernateUtil class, use the patterns described here: http://hibernate.org/42.html

And use the code samples and snippets from the tutorial: http://hibernate.org/hib_docs/core/refe ... orial.html

plavacek · **Joined:** Wed Apr 11, 2007 12:51 am **Posts:** 2

I may have a solution for you - making a few assumptions.
Bottom line on top: if you're using file:/ HSQL URLs, try changing the SET WRITE_DELAY 10 line in the .script to SET WRITE_DELAY 0.

Full details for the curious:
I think there's a problem with Hibernate using HSQL 1.8 drivers and file:/ URLS.
Test procedure:
1) Download new HSQL drivers - 1.8.0 or later (mine are 1.8).
2) Download hibernate-3.2.3-ga and unzip.
3) Make 1 change to the /doc/tutorial/src/hibernate.cfg.xml file - change
jdbc:hsqldb:hsql://localhost
to
jdbc:hsqldb:file:/db/dbtest
4) make a /db directory.
5) Add the appropriate jars
6) Run ant run -Daction="store"
Everything appears to go OK - no errors:

Code:

[java] 00:01:36,312 DEBUG SchemaExport:303 - create table EVENTS (EVENT_ID bigint generated by default as identity (start with 1), EVENT_DATE timestamp, title varchar(255), primary key (EVENT_ID))
     [java] 00:01:36,328 DEBUG SchemaExport:303 - create table PERSON (PERSON_ID bigint generated by default as identity (start with 1), age integer, firstname varchar(255), lastname varchar(255), primary key (PERSON_ID))
     [java] 00:01:36,328 DEBUG SchemaExport:303 - create table PERSON_EMAIL_ADDR (PERSON_ID bigint not null, EMAIL_ADDR varchar(255))
     [java] 00:01:36,328 DEBUG SchemaExport:303 - create table PERSON_EVENT (EVENT_ID bigint not null, PERSON_ID bigint not null, primary key (PERSON_ID, EVENT_ID))
     [java] 00:01:36,328 DEBUG SchemaExport:303 - alter table PERSON_EMAIL_ADDR add constraint FKA54215FE7708282F foreign key (PERSON_ID) references PERSON
     [java] 00:01:36,328 DEBUG SchemaExport:303 - alter table PERSON_EVENT add constraint FKAD91D9107708282F foreign key (PERSON_ID) references PERSON
     [java] 00:01:36,328 DEBUG SchemaExport:303 - alter table PERSON_EVENT add constraint FKAD91D910F96D1A45 foreign key (EVENT_ID) references EVENTS
     [java] 00:01:36,328  INFO SchemaExport:196 - schema export complete
     [java] Hibernate: insert into EVENTS (EVENT_ID, EVENT_DATE, title) values (null, ?, ?)
     [java] Hibernate: call identity()
     [java] 00:01:36,484  INFO SessionFactoryImpl:769 - closing
     [java] 00:01:36,484  INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:hsqldb:file:/db/dbtest

The standard HSQL files are created in /db/dbtest - lck, log, properties, and script. However, they are all basically empty - no matter how many times I run it, and whether or not the ddl export is enabled. After a few runs, the contents of .script is:

Code:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

and it never goes beyond that.

So finally, after checking EVERYTHING twice or three times, I changed the SET WRITE_DELAY line to 0. Hand-hacking the .script isn't recommended, but it sure is useful sometimes =) In this case, VERY useful. After doing that, all of the DDL and INSERTS actually work! I need to read up on the overall effect of this on HSQL ... may not be something you want to do for production.

plavacek · **Joined:** Wed Apr 11, 2007 12:51 am **Posts:** 2

Update: Checked here
http://hsqldb.org/doc/guide/ch09.html#set_write_delay-section

started playing with the parameter. Anything over 80 MILLIS, (the default is 10 SECONDS) I don't get anything written out.
I am concerned with the write up linked above - it makes it seem like Hibernate is not properly shutting down HSQL in this case (otherwise this shouldn't be an issue). Am I misreading? Can anyone confirm my test result?

basm · **Joined:** Fri May 08, 2009 11:15 am **Posts:** 5

@plavacek
I have had the same problem and solved it by applying your hack. But later on I found the underlaying problem (in my case at least).
The property hbm2ddl in the hibernate.cfg.xml file was specified in the persistence.xml file. Removing this property solved my problems.