How to use SEQUENCE in Apache Derby?

Question

I would like to use SEQUENCE support in Apache Derby 10.7. I created a sequence with the following statement:

CREATE SEQUENCE SAMPLE_SEQ AS INT MAXVALUE 999999 CYCLE; 

How to select next / current value from SAMPLE_SEQ ? Could you help me with the request?

Answer 1

Apache Derby Doc says: Use the NEXT VALUE FOR expression to express

It should be something like

 SELECT NEXT VALUE FOR SAMPLE_SEQ; 

Answer 2

Use NEXT VALUE FOR as described in the manual:

http://db.apache.org/derby/docs/10.7/ref/rrefsqljnextvaluefor.html#rrefsqljnextvaluefor

Answer 3

To get the current value of the sequence, you must run the following SQL:

 SELECT CURRENTVALUE FROM SYS.SYSSEQUENCES WHERE SEQUENCENAME='SAMPLE_SEQ' 

Answer 4

At the SQL command line, you can query the following value with this statement:

 values NEXT VALUE FOR <sequence_name> 

This will work as an expression embedded in the INSERT statement. For example:.

 INSERT INTO <table_name> (IDFIELD) VALUES (NEXT VALUE FOR <sequence_name>) 

Answer 5

If you want to get the "current value" from the "sequence":

• values ​​(next value for <sequence>)

Same thing in Java using JDBC:

 ResultSet rs = conn.prepareStatement("values (next value for <sequence>)").executeQuery(); rs.next(); int seqValue = rs.getInt(1); 

Source: Derby-user Mailing List Archive