RE: [hibernate-dev] HSQLDialect - sequences - problems with white space

Ah, I forgot this: There is a simple way to query sequences that needs no tables at all : call NEXT VALUE FOR seq_STEIN; (again, sorry for any Outlook nonsense ...) Regards, ________________________________ From: hibernate-dev-bounces(a)lists.jboss.org on behalf of David Balazic Sent: Mon 10-Sep-07 18:29 To: hibernate-dev(a)lists.jboss.org Subject: [hibernate-dev] HSQLDialect - sequences - problems with white space (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 :-(