<HTML dir=ltr><HEAD>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2800.1597" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText20952 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Ah, I forgot this: There is a simple way to query sequences that needs no tables at all :</FONT></DIV>
<DIV dir=ltr><FONT size=2>
<P>call </FONT><B><FONT color=#7e004b size=2>NEXT</B></FONT><FONT size=2> </FONT><B><FONT color=#7e004b size=2>VALUE</B></FONT><FONT size=2> </FONT><B><FONT color=#7e004b size=2>FOR</B></FONT><FONT size=2> seq_STEIN;</FONT></P>
<P><FONT size=2>(again, sorry for any Outlook nonsense ...)</FONT></P>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>Regards,</FONT></P></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> hibernate-dev-bounces@lists.jboss.org on behalf of David Balazic<BR><B>Sent:</B> Mon 10-Sep-07 18:29<BR><B>To:</B> hibernate-dev@lists.jboss.org<BR><B>Subject:</B> [hibernate-dev] HSQLDialect - sequences - problems with white space<BR></FONT><BR></DIV>
<DIV dir=ltr>
<DIV><FONT face=Arial color=#000000 size=2>(since the mod did not approve my prevoius message, neither did Steve reply, I subscribed and send it again)<BR>(I bet 5 seconds after I push SEND, the moderator will approve my pending message and we will have two copies...)</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>Hi!</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>I had a problem with HSQL and hibernate, which boils down to this line<BR>in org.hibernate.dialect.HSQLDialect :<BR>public String[] getCreateSequenceStrings(String sequenceName) {<BR> return new String[] {<BR> "create table dual_" + sequenceName + " (zero integer)", // this one<BR> "insert into dual_" + sequenceName + " values (0)", // and also this one<BR> "create sequence " + sequenceName + " start with 1"<BR> };<BR> }</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>If the mapping file has something like this :</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2><id name="id" type="long" column="ID"><BR> <generator class="sequence"><BR> <param name="sequence"><BR> seq_FOR_MY_TABLE<BR> </param><BR> </generator><BR></id></FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>then the concatenation " ... dual_" + sequenceName will produce a name with a space inbetween,<BR>which will break both hbm2ddl schema export and regular DML (when persisting the object):</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>create table dual_ seq_FOR_MY_TABLE (zero integer)<BR>Unexpected token SEQ_FOR_MY_TABLE, requires ( in statement [create table dual_ seq_OFFER_MEMBER_OFFERS_ID]<BR>insert into dual_ seq_FOR_MY_TABLE values (0)<BR>Unsuccessful: insert into dual_ seq_FOR_MY_TABLE values (0)<BR>...<BR>Hibernate: select next value for seq_FOR_MY_TABLE from dual_ seq_FOR_MY_TABLE <BR>SQL Error: -22, SQLState: S0002<BR>Table not found in statement [select next value for seq_FOR_MY_TABLE from dual_ seq_FOR_MY_TABLE]</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>the second error comes from getSequenceNextValString(String sequenceName) {<BR> return "select next value for " + sequenceName + " from dual_" + sequenceName;<BR> }</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>I see 3 solutions: </FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2> - declare blank characters in <param name="sequence"> as illegal and be done with it :-(</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2> - trim blank characters from the sequenceName argument in method<BR> org.hibernate.dialect.HSQLDialect.getCreateSequenceStrings(String) (and others)<BR> or maybe before, when it is read from Configuration, in org.hibernate.id.SequenceGenerator.configure</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2> - do not create a table for each sequence. As I see it they are not really used, just needed as a dummy<BR> table for the "SELECT NEXT VALUE FOR sequenceName FROM ..." statement.<BR> A single table could be used for all sequences. Some "MY_COMMON_DUAL" thing.</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>Regards,<BR>David Balazic</FONT></DIV>
<DIV><FONT face=Arial color=#000000 size=2>PS: Sorry if OutLook introduced any formatting nonsense; I use the webmail<BR>version and it can't do plain text :-(</FONT></DIV></DIV></BODY></HTML>