problem with sequences in schema

[Limbic System]

If I create a sequence inside a schema I can't seem to "find" it:

public static void main(String[] args) throws Exception {
String jdbcUrl =
"jdbc:h2:mem:;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=4";
String driver = "org.h2.Driver";

Class.forName(driver);
Connection conn = DriverManager.getConnection(jdbcUrl, "DBA", "");
Statement st = conn.createStatement();
st.executeUpdate("drop schema conf if exists; create schema conf;");
st.executeUpdate("create sequence conf.report_id_seq;");
st.executeQuery("select nextval('conf.report_id_seq');");
}

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Sequence
CONF.REPORT_ID_SEQ not found; SQL statement:
select nextval('conf.report_id_seq'); [90036-101]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.schema.Schema.getSequence(Schema.java:423)
at org.h2.expression.Function.getSequence(Function.java:1125)
at org.h2.expression.Function.getValueWithArgs(Function.java:1025)
at org.h2.expression.Function.getValue(Function.java:406)
at org.h2.command.dml.Select.queryFlat(Select.java:499)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:560)
at org.h2.command.dml.Query.query(Query.java:233)
at org.h2.command.CommandContainer.query(CommandContainer.java:81)
at org.h2.command.Command.executeQueryLocal(Command.java:140)
at org.h2.command.Command.executeQuery(Command.java:121)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:75)

If I simply remove the ".conf" from the create/select, it works fine.

Thanks.

[Thomas Mueller]

Hi,

This is an incompatibility with PostgreSQL. I will fix that in the
next release. A workaround is to use:

select nextval('CONF', 'REPORT_ID_SEQ');

Unfortunately this is not supported by PostgreSQL.

Regards,
Thomas

[Limbic System]

That new syntax does the trick, thanks. I notice that the case seems
to matter. If I use lowercase for either the schema name or the table
name in the call to 'nextval' it fails to locate the object. The case
does not seem to matter in "create sequence" or "create schema". I
confess ignorance as to how this should actually work in standard SQL,
but it is a surprise.

I work out of svn, so I'll keep an eye out for that fix.

Thanks!

[Thomas Mueller]

Hi,

> That new syntax does the trick, thanks. I notice that the case seems
> to matter.

Yes, this syntax is case sensitive. The regular nextval('...') is not
however (and will not be).

> The case
> does not seem to matter in "create sequence" or "create schema".

That's because the database converts unquoted names to uppercase. See also:

http://www.h2database.com/html/grammar.html#name
http://www.h2database.com/html/grammar.html#quotedname

> I confess ignorance as to how this should actually work in standard SQL,
> but it is a surprise.

As far as I know, H2 works like defined by the SQL standard. Other
databases work differently.

> I work out of svn, so I'll keep an eye out for that fix.

It is now in the SVN.

Regards,
Thomas

[Limbic System]

Thomas,

Thanks for the fix. There seems to be a similar issue with the
following syntax:

select last_value from conf.report_id_seq

This is also likely a PostgreSQL-specific syntax.

org.springframework.jdbc.UncategorizedSQLException: Hibernate
operation: could not execute query; uncategorized SQLException for SQL
[select last_value from conf.report_id_seq]; SQL state [90079]; error
code [90079]; Schema CONF not found; SQL statement:
select last_value from conf.report_id_seq [90079-102]; nested
exception is org.h2.jdbc.JdbcSQLException: Schema CONF not found; SQL
statement:
select last_value from conf.report_id_seq [90079-102]
Caused by: org.h2.jdbc.JdbcSQLException: Schema CONF not found; SQL statement:
select last_value from conf.report_id_seq [90079-102]

at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)

at org.h2.command.Parser.getSchema(Parser.java:596)
at org.h2.command.Parser.readTableOrView(Parser.java:4203)
at org.h2.command.Parser.readTableFilter(Parser.java:967)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1496)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1593)
at org.h2.command.Parser.parseSelectSub(Parser.java:1490)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1335)
at org.h2.command.Parser.parseSelect(Parser.java:1323)
at org.h2.command.Parser.parsePrepared(Parser.java:392)
at org.h2.command.Parser.parse(Parser.java:288)
at org.h2.command.Parser.parse(Parser.java:260)
at org.h2.command.Parser.prepareCommand(Parser.java:232)
at org.h2.engine.Session.prepareLocal(Session.java:352)
at org.h2.engine.Session.prepareCommand(Session.java:313)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1042)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:219)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1538)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
at com.example.ips.report.data.dao.hibernate.conf.ReportConfHibernate$8.doInHibernate(ReportConfHibernate.java:239)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
at com.example.ips.report.data.dao.hibernate.conf.ReportConfHibernate.getLastReportId(ReportConfHibernate.java:233)
at com.example.ips.report.data.dao.conf.ReportConfDaoTest.getLastReportId(ReportConfDaoTest.java:74)

[limbic...@gmail.com]

There seems to also be a similar upper/lower case issue swith "call
next value for admin.job_seq" syntax.
This is generated by Hibernate, so I can't employ the work-around you
posted above. I am using the latest
from SVN with hibernate 3.3.1.GA, and hibernate-annotations 3.4.0.ga.


org.springframework.jdbc.UncategorizedSQLException: Hibernate
operation: could not get next sequence value; uncategorized
SQLException for SQL [call next value for admin.job_seq]; SQL state
[90079]; error code [90079]; Schema ADMIN not found; SQL statement:
call next value for admin.job_seq [90079-102]; nested exception is
org.h2.jdbc.JdbcSQLException: Schema ADMIN not found; SQL statement:
call next value for admin.job_seq [90079-102]
Caused by: org.h2.jdbc.JdbcSQLException: Schema ADMIN not found; SQL
statement:
call next value for admin.job_seq [90079-102]

at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)

at org.h2.command.Parser.getSchema(Parser.java:596)
at org.h2.command.Parser.readSequence(Parser.java:4224)
at org.h2.command.Parser.readTerm(Parser.java:2222)
at org.h2.command.Parser.readFactor(Parser.java:1835)
at org.h2.command.Parser.readSum(Parser.java:1822)
at org.h2.command.Parser.readConcat(Parser.java:1795)
at org.h2.command.Parser.readCondition(Parser.java:1668)
at org.h2.command.Parser.readAnd(Parser.java:1648)
at org.h2.command.Parser.readExpression(Parser.java:1640)
at org.h2.command.Parser.parserCall(Parser.java:3540)
at org.h2.command.Parser.parsePrepared(Parser.java:323)

at org.h2.command.Parser.parse(Parser.java:288)
at org.h2.command.Parser.parse(Parser.java:260)
at org.h2.command.Parser.prepareCommand(Parser.java:232)
at org.h2.engine.Session.prepareLocal(Session.java:352)
at org.h2.engine.Session.prepareCommand(Session.java:313)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1042)
at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:
72)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:
219)
at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:
497)
at

org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:
123)
at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:
73)
at
org.hibernate.id.SequenceHiLoGenerator.generate(SequenceHiLoGenerator.java:
58)
at
org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:
99)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:
187)
at
org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:
33)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:
172)
at
org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:
27)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:
70)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.orm.hibernate3.HibernateTemplate
$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1202)
at $Proxy42.save(Unknown Source)
at com.example.ips.report.data.dao.admin.JobDaoHib
$5.doInHibernate(JobDaoHib.java:90)

at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:
372)
at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:
338)
at

com.example.ips.report.data.dao.admin.JobDaoHib.createJob(JobDaoHib.java:
86)
at
com.example.ips.report.data.dao.admin.JobDaoUnitTest.testGetAllJobs(JobDaoUnitTest.java:
67)


Thanks

On Oct 27, 5:02 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > That new syntax does the trick, thanks.  I notice that the case seems
> > to matter.
>
> Yes, this syntax is case sensitive. The regular nextval('...') is not
> however (and will not be).
>
> > The case
> > does not seem to matter in "create sequence" or "create schema".
>
> That's because the database converts unquoted names to uppercase. See also:
>

> http://www.h2database.com/html/grammar.html#namehttp://www.h2database.com/html/grammar.html#quotedname

[Thomas Mueller]

Hi,

> Schema ADMIN not found

It looks like the schema and the sequence was not created. In that
case it's not possible to get the next value. I don't think this is a
bug in H2. Do you have a reproducible test case?

Regards,
Thomas

[Limbic System]

Thomas,

This was my mistake. Sorry to have wasted your time.

Thanks,
L