Clever Geek Handbook

DB2 sequence value using Java

I need to get the next value from a DB2 sequence. This is one of the approaches I have tried

stmt = con.createStatement();
rs = stmt.executeQuery("db2 VALUES NEXTVAL FOR <sequence_name>"); 

rs.close();
stmt.close();

The error I am getting looks like this:

com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10103][10941] Method executeQuery cannot be used for updates.
           at com.ibm.db2.jcc.c.qh.a(qh.java:2390)
           at com.ibm.db2.jcc.c.qh.a(qh.java:1751)
           at com.ibm.db2.jcc.c.qh.a(qh.java:478)
           at com.ibm.db2.jcc.c.qh.executeQuery(qh.java:462)
           at test.pack.SequenceConn.getNextSequenceValue(SequenceConn.java:59)
           at test.pack.SequenceConn.main(SequenceConn.java:22)

How can I get the next value from the sequence?

+3


source to share


2 answers


I managed to solve it myself.

stmt = con.createStatement();
rs = stmt.executeQuery("VALUES NEXTVAL FOR <sequence_name>"); 

rs.close();
stmt.close();


Basically the previous line db2

in the query string was causing the problem. Removed it and was able to get the sequence value.

+3


source


You can use the following clause to get the following value for a sequence in DB2:

stmt = con.createStatement();
rs = stmt.executeQuery("SELECT NEXT VALUE FOR <sequence_name>"); 
if(rs.next()) {
    Long sequenceValue = rs.getLong(1);
}
rs.close();
stmt.close();


As noted in the DB2 Sequence Reference Book .

NEXT VALUE FOR a sequence name
                   , the NEXT VALUE expression generates and returns the next value for the sequence specified by the sequence name.
...
  - Expressions NEXT VALUE and PREVIOUS VALUES can be specified in the following places:
          Select-statement or SELECT INTO (in the select clause, provided that the statement does not contain the DISTINCT keyword, GROUP BY clause, ORDER BY clause, UNION, INTERSECT keyword or EXCEPT keyword)

+1


source






More articles:


All Articles