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

[David Balazic]

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 at lists.jboss.org on behalf of David Balazic
Sent: Mon 10-Sep-07 18:29
To: hibernate-dev at 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 :-(
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-dev/attachments/20070910/d463e751/attachment.html