How to use SEQUENCE in Apache Derby?

Question

I'd like to use SEQUENCE support in Apache Derby 10.7. I've created the sequence with the following statement:

CREATE SEQUENCE SAMPLE_SEQ AS INT MAXVALUE 999999 CYCLE;

How can I select next/current value from the SAMPLE_SEQ? Could you please help me out with the query?

score 8 · Accepted Answer · answered Apr 20 '11 at 10:59

8

Apache Derby Doc says: Use a NEXT VALUE FOR expression

Should be something like

SELECT NEXT VALUE FOR SAMPLE_SEQ;

answered Apr 20 '11 at 10:59

Andreas Dolk

113,398
19
180
268

11

Derby complains about the query you've suggested. It doesn't like premature ending of the query (EOF). What works for me is `VALUES NEXT VALUE FOR SAMPLE_SEQ` – Tomasz Błachowicz Apr 20 '11 at 11:26
@pregzt - that was my concern - I didn't find a working example for a query to get a single value from a sequence - the fragment was used *inside* a more complex query... – Andreas Dolk Apr 20 '11 at 12:57
Why was this upvoted when it doesn't work? Please could someone edit the SQL to make it work? – Adam Gawne-Cain Feb 20 '23 at 10:26
Here is SQL that actually works: "select next value for SAMPLE_SEQ from (values 1) t". – Adam Gawne-Cain Feb 20 '23 at 10:47

score 3 · Answer 2 · answered Apr 20 '11 at 10:58

3

Use NEXT VALUE FOR as documented in the manual:

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

answered Apr 20 '11 at 10:58

score 1 · Answer 3 · answered May 17 '11 at 03:45

1

To get the current value of the sequence the following SQL should be executed:

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

answered May 17 '11 at 03:45

user611544

19
1

That is not the current value. – Aseem Bansal Jul 12 '14 at 09:09
Querying `SYSSEQUENCES` can cause concurrency issues. Derby provides the built-in system function `SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE` to read the next number from the sequence generator; see the [Derby Ref Guide](https://db.apache.org/derby/docs/10.14/ref/rrefsyscspeeksequence.html) and [Derby Tuning Guide](https://db.apache.org/derby/docs/10.14/tuning/ctunperfsequences.html) for details. – MikeOnline Aug 14 '23 at 20:55

score 0 · Answer 4 · answered Jul 11 '13 at 10:14

In the SQL command prompt, you can query the next value with this statement:

values NEXT VALUE FOR <sequence_name>

This will work as an expression embedded into an INSERT statement. E.g.:

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

ankurrc · Answer 5 · 2018-02-08T21:14:17.657

0

In case you want to fetch the 'current value' from the 'sequence':

values ( next value for <sequence> )

Same 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

edited Feb 08 '18 at 21:14

answered Oct 29 '15 at 13:22

ankurrc

1,072
9
12

score 0 · Answer 6 · answered Feb 20 '23 at 10:53

The following SQL returns the next value of a SEQUENCE in Derby:

select next value for SAMPLE_SEQ from (values 1) t

The part "(values 1) t" makes a table with 1 row and 1 column, which then gets substituted with the next value from the sequence. In Oracle you can use the standard DUAL table, and in other databases you can execute SELECT without a table.

How to use SEQUENCE in Apache Derby?

6 Answers6

Linked