DB2 Sequence value using Java

Question

I need to get the next value from a DB2 sequence. This is one approach I've tried

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

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

The error I get is as follows:

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 retrieve the next value from the sequence?

Jayp · Accepted Answer · 2013-02-12 13:23:49Z

5

Managed to solve this by myself.

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

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

Basically the preceding db2 in the query string was causing the issue. Removed it and was able to get the sequence value.

edited Feb 12, 2013 at 13:23

answered Feb 12, 2013 at 12:57

Jayp

8322 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Xavi López · Accepted Answer · 2013-02-12 12:22:05Z

1

You can use the following sentence to retrieve the next 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 specified in the DB2 Reference chapter on Sequences.

NEXT VALUE FOR sequence-name
A NEXT VALUE expression generates and returns the next value for the sequence specified by sequence-name.
...
- NEXT VALUE and PREVIOUS VALUE 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)

edited Feb 12, 2013 at 12:22

answered Feb 12, 2013 at 12:16

Xavi López

27.9k11 gold badges100 silver badges167 bronze badges

3 Comments

Jayp Over a year ago

Thanks for the hint! Tried it out and stumbled onto the next error: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;READ FOR <sequence_name>;<table_expr> . Tried quickly google for solution but haven't found one so far.

user330315 Over a year ago

@Jayp: Do not include the ; in your SQL string from Java. And only execute a single SQL statement per call to executeQuery()

Jayp Over a year ago

The string is exactly like Xavi's above.

Collectives™ on Stack Overflow

DB2 Sequence value using Java

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related