Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: DB2/390: Problem with getting the next value from a sequence
PostPosted: Thu Sep 06, 2007 5:09 am 
Newbie

Joined: Thu Sep 06, 2007 4:32 am
Posts: 4
Hello everybody,

we get the following error in the server log of the jboss server, if we try to insert a new entry into the database:
Code:
2007-09-06 10:34:21,628 DEBUG [org.hibernate.SQL] values nextval for RESKGD.KGQP1H01
2007-09-06 10:34:21,644 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: -142, SQLState: 42612
2007-09-06 10:34:21,644 ERROR [org.hibernate.util.JDBCExceptionReporter] [IBM][CLI Driver][DB2] SQL0142N  The SQL statement is not supported.  SQLSTATE=42612

2007-09-06 10:34:21,644 ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException in method: public abstract java.lang.Object de.volkswagen.cypris.server.modules.managereferencedata.ejb.DOTStore.saveOrUpdateDOT(java.lang.Class,de.volkswagen.cypris.client.core.domain.DOTTableDOT) throws java.rmi.RemoteException:
org.hibernate.exception.SQLGrammarException: could not get next sequence value


If we try to perform the insert statement on our own by QMF, it works:
Code:
INSERT INTO RESKGD.KGVP1H01(MARKT_PK, BEZEICHNUNG)
VALUES (nextval for RESKGD.KGQP1H01, 'Test');


But here, we used the
Code:
VALUES (nextval for RESKGD.KGQP1H01, 'Test')
explicitly in an INSERT statement and it looks like Hibernate doesn't so:
(hibernate_sql.log)
Code:
values nextval for RESKGD.KGQP1H01


If we try this statement in QMF we get an not supported error, too.

In the IBM SQL Reference documentation we found this:
Quote:
NEXTVAL and PREVVAL expressions can be specified in the following
places:
– select-statement or SELECT INTO statement (within the select-clause,
provided that the statement does not contain a DISTINCT keyword, a
GROUP BY clause, an ORDER BY clause, a UNION keyword, an
INTERSECT keyword, or EXCEPT keyword)
– INSERT statement (within a VALUES clause)
– INSERT statement (within the select-clause of the fullselect)
– UPDATE statement (within the SET clause (either a searched or a
positioned UPDATE statement), except that NEXTVAL cannot be
specified in the select-clause of the fullselect of an expression in the SET
clause)
– SET Variable statement (except within the select-clause of the fullselect of
an expression; a NEXTVAL expression can be specified in a trigger, but a
PREVVAL expression cannot)
– VALUES INTO statement (within the select-clause of the fullselect of an
expression)
– CREATE PROCEDURE statement (within the routine-body of an SQL
procedure)
– CREATE TRIGGER statement within the triggered-action (a NEXTVAL
expression may be specified, but a PREVVAL expression cannot)

NEXTVAL and PREVVAL expressions cannot be specified (SQLSTATE
428F9) in the following places:
– join condition of a full outer join
– DEFAULT value for a column in a CREATE or ALTER TABLE statement
– generated column definition in a CREATE OR ALTER TABLE statement
– summary table definition in a CREATE TABLE or ALTER TABLE
statement
– condition of a CHECK constraint
– CREATE TRIGGER statement (a NEXTVAL expression may be specified,
but a PREVVAL expression cannot)
– CREATE VIEW statement
– CREATE METHOD statement
– CREATE FUNCTION statement

In addition, a NEXTVAL expression cannot be specified (SQLSTATE 428F9)
in the following places:
– CASE expression
– parameter list of an aggregate function
– subquery in a context other than those explicitly allowed above
– SELECT statement for which the outer SELECT contains a DISTINCT
operator
– join condition of a join
– SELECT statement for which the outer SELECT contains a GROUP BY
clause
– SELECT statement for which the outer SELECT is combined with
another SELECT statement using the UNION, INTERSECT, or EXCEPT
set operator
– nested table expression
– parameter list of a table function
– WHERE clause of the outer-most SELECT statement, or a DELETE or
UPDATE statement
– ORDER BY clause of the outer-most SELECT statement
– select-clause of the fullselect of an expression, in the SET clause of an
UPDATE statement
– IF, WHILE, DO ... UNTIL, or CASE statement in an SQL routine


The mapping file for the class looks like this:
Code:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
  <class table="KGVP1F01" name="de.volkswagen.cypris.client.modules.managereferencedata.domain.Marker">
    <id column="MARKE_PK" name="markePK">
      <generator class="sequence">
        <param name="sequence">KGQP1F01</param>
      </generator>
    </id>
    <property name="markeKurz" not-null="true" column="MARKE_KURZ" unique="true"/>
    <property name="markeLang" column="MARKE_LANG"/>
    <many-to-one column="FK_SYMBOL_PK" lazy="false" name="symbol" class="de.volkswagen.cypris.client.modules.managereferencedata.domain.Symbol"/>
  </class>
  <query name="Marker.SelectAll"><![CDATA[from Marker]]></query>
</hibernate-mapping>


Is there a known workaround for this problem?

With best regards :-)

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:
DB2 08.01.0005 IBM DB2 JDBC 2.0 Type 2 08.02.0007

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html[/quote][/code]


Top
 Profile  
 
 Post subject: BUG?
PostPosted: Thu Sep 06, 2007 6:18 am 
Newbie

Joined: Thu Sep 06, 2007 4:32 am
Posts: 4
Hello again,

i have done some research on this topic and i think the method:
Code:
public String getSequenceNextValString(String sequenceName) {
   return "values nextval for " + sequenceName;
}

in the class DB2Dialect (or the corresponding subclass) have to be overriden to something like this (so it works for me in QMF):
Code:
public String getSequenceNextValString(String sequenceName) {
   return "SELECT nextval FOR " + sequenceName
                  + " FROM sysibm.sysdummy1";
}


Does anyone agree?


Top
 Profile  
 
 Post subject: FIXED
PostPosted: Thu Sep 06, 2007 7:54 am 
Newbie

Joined: Thu Sep 06, 2007 4:32 am
Posts: 4
Hello again,

i have subclassed DB2Dialect and tried my own fix as described below and it works for us.

With best regards :-)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 07, 2007 4:17 am 
Regular
Regular

Joined: Fri May 12, 2006 4:05 am
Posts: 106
Hi,

there's no need to do your own Dialect (at least not because of this issue) - DB2390Dialect as provided by hibernate should work just fine!

Best Regards

piet


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 24, 2007 5:12 am 
Newbie

Joined: Thu Sep 06, 2007 4:32 am
Posts: 4
Hi piet,

the built-in DB2390Dialect doesn't work for us ... in fact it asserts that this dialect doesn't support sequences as you can read in the source code:

Code:
public boolean supportsSequences() {
    return false;
}


With best regards ...

p.h.o.e.n.i.x[/code]


Top
 Profile  
 
 Post subject: Re: DB2/390: Problem with getting the next value from a sequence
PostPosted: Wed Oct 12, 2011 3:01 am 
Newbie

Joined: Wed Apr 19, 2006 12:50 pm
Posts: 8
hi
We have encountered the same issue as phoenix. Are there any plan to support sequences in db2390/db2400 dialects..?

best regards
Rasmus


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.