11:29 a.m.
(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 :-(
11:31 a.m.
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 :-(
1:41 p.m.
On Monday 10 September 2007 11:29:36 am David Balazic wrote:
(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!
Sorry, I generally do not reply to these type of emails sent
directly to me.
If you want to discuss Hibernate development, joining the development mailing
list is not an undue hardship.
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.
Hibernate historically has not handled xml text very well imo. We used to
have this same issue with stuff in cfg.xml, although I changed all that to
use the trimmed text. However, blindly using trimmed text from a mapping
file is not as viable.
So certainly we could have SequenceGenerator account for trimming the sequence
name. I obviously have no objections to that.
As for not not creating the dual_* tables:
1) I do not like (and will not code for) a "MY_COMMON_DUAL" approach; this is
a simple oversight in HSQLDB (at least "back then", #2 alleviates that
shortcoming)
2) since when was the "call NEXT VALUE FOR [sequence_name]" syntax available?
Depending on the answer to that, I have no issues with going this route;
create a patch and attach to a JIRA request.
1:50 a.m.
From: Steve Ebersole on behalf of Steve Ebersole
So certainly we could have SequenceGenerator account for trimming the
sequence
name. I obviously have no objections to that.
As for not not creating the dual_* tables:
1) I do not like (and will not code for) a "MY_COMMON_DUAL" approach; this is
a simple oversight in HSQLDB (at least "back then", #2 alleviates that
shortcoming)
2) since when was the "call NEXT VALUE FOR [sequence_name]" syntax available?
Depending on the answer to that, I have no issues with going this route;
create a patch and attach to a JIRA request.
2) not sure, it works with hsqldb 1.8.0.1. I will check with hsqldb guys.
Regards,
David
6:14 a.m.
Created JIRA issue HHH-2839
Regards,
David
________________________________
From: Steve Ebersole on behalf of Steve Ebersole
Sent: Mon 10-Sep-07 20:41
To: hibernate-dev(a)lists.jboss.org
Cc: David Balazic
Subject: Re: [hibernate-dev] HSQLDialect - sequences - problems with white space
On Monday 10 September 2007 11:29:36 am David Balazic wrote:
(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!
Sorry, I generally do not reply to these type of emails sent
directly to me.
If you want to discuss Hibernate development, joining the development mailing
list is not an undue hardship.
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.
Hibernate historically has not handled xml text very well imo. We used to
have this same issue with stuff in cfg.xml, although I changed all that to
use the trimmed text. However, blindly using trimmed text from a mapping
file is not as viable.
So certainly we could have SequenceGenerator account for trimming the sequence
name. I obviously have no objections to that.
As for not not creating the dual_* tables:
1) I do not like (and will not code for) a "MY_COMMON_DUAL" approach; this is
a simple oversight in HSQLDB (at least "back then", #2 alleviates that
shortcoming)
2) since when was the "call NEXT VALUE FOR [sequence_name]" syntax available?
Depending on the answer to that, I have no issues with going this route;
create a patch and attach to a JIRA request.
6311
days inactive
6314
days old
4 comments
2 participants
participants (2)
-
David Balazic -
Steve Ebersole