(since the mod did not approve my prevoius message, neither did Steve reply, I subscribed and send it again)
(I bet 5 seconds after I push SEND, the moderator will approve my pending message and we will have two copies...)

Hi!

I had a problem with HSQL and hibernate, which boils down to this line
in org.hibernate.dialect.HSQLDialect :
public String[] getCreateSequenceStrings(String sequenceName) {
return new String[] {
    "create table dual_" + sequenceName + " (zero integer)", // this one
          "insert into dual_" + sequenceName + " values (0)", // and also this one
          "create sequence " + sequenceName + " start with 1"
};
}

If the mapping file has something like this :

<id name="id" type="long" column="ID">
    <generator class="sequence">
        <param name="sequence">
            seq_FOR_MY_TABLE
        </param>
    </generator>
</id>

then the concatenation " ... dual_" + sequenceName will produce a name with a space inbetween,
which will break both hbm2ddl schema export and regular DML (when persisting the object):

create table dual_ seq_FOR_MY_TABLE (zero integer)
Unexpected token SEQ_FOR_MY_TABLE, requires ( in statement [create table dual_ seq_OFFER_MEMBER_OFFERS_ID]
insert into dual_ seq_FOR_MY_TABLE values (0)
Unsuccessful: insert into dual_ seq_FOR_MY_TABLE values (0)
...
Hibernate: select next value for seq_FOR_MY_TABLE from dual_ seq_FOR_MY_TABLE
SQL Error: -22, SQLState: S0002
Table not found in statement [select next value for seq_FOR_MY_TABLE from dual_ seq_FOR_MY_TABLE]

the second error comes from getSequenceNextValString(String sequenceName) {
return "select next value for " + sequenceName + " from dual_" + sequenceName;
}

I see 3 solutions:

- declare blank characters in <param name="sequence"> as illegal and be done with it :-(

- trim blank characters from the sequenceName argument in method
org.hibernate.dialect.HSQLDialect.getCreateSequenceStrings(String) (and others)
or maybe before, when it is read from Configuration, in org.hibernate.id.SequenceGenerator.configure

- do not create a table for each sequence. As I see it they are not really used, just needed as a dummy
table for the "SELECT NEXT VALUE FOR sequenceName FROM ..." statement.
A single table could be used for all sequences. Some "MY_COMMON_DUAL" thing.

Regards,
David Balazic

PS: Sorry if OutLook introduced any formatting nonsense; I use the webmail
version and it can't do plain text :-(