Thanks for your reply, your solution is an option we could use, but we would like to use a sql script only and not java code.
Do you have a sql-only-solution? Is there something planned to implement in next derby versions like it's implemented in H2 (nextval/currval)? On Tue, Jan 24, 2012 at 2:29 PM, Rick Hillegas <[email protected]> wrote: > On 1/24/12 12:09 AM, squidy78 wrote: >> >> hello, actually we use oracle and would like to use derby as the >> embedded db for our junit testing. >> >> now we have a problem with sequences and I haven't found a solution >> yet, maybe we use it the wrong way?! I hope somebody can help... >> >> we have the following sql script which creates two sequences and two >> tables. after that we insert some data using the sequences, but there >> is something wrong with the currentvalue... >> >> >> CREATE sequence seq_type AS int start WITH 1; >> CREATE sequence seq_text AS int start WITH 1; >> >> CREATE TABLE t_type (id int PRIMARY KEY, label varchar (255)); >> CREATE TABLE t_text (id int PRIMARY KEY, text varchar(255), typeid int >> constraint type_fk REFERENCES t_type(id)); >> >> INSERT INTO t_type (id, label) VALUES (next value FOR seq_type, 'sample >> type'); >> SELECT * FROM t_type; >> -- data is inserted and id is 1 >> >> SELECT currentvalue FROM sys.syssequences WHERE sequencename='SEQ_TYPE'; >> -- this returns 6 but should be 1 !? >> >> INSERT INTO t_text (id, text, typeid) VALUES (next value FOR seq_text, >> 'first text', (SELECT currentvalue FROM sys.syssequences WHERE >> sequencename='SEQ_TYPE')); >> -- this is not working --> violation of foreign key constraint >> 'TYPE_FK' because currentvalue of SEQ_TYPE returns 6 and not 1 !? >> >> >> the problem is that when calling "next value for seq_type" the >> sequence is ok and is incremented by 1, but the currentvalue is wrong >> and contains the value 6? when the sequence reaches 6, the >> currentvalue changes to 11. >> >> can someone telling me what's going on and how must the script looks >> like to work correctly with sequences? >> >> we're using derby 10.8.2.2 for this tests. >> >> thanks for helping! >> > As a performance optimization, Derby 10.8.2 pre-allocates 5 sequence values > from a counter at a time. After Derby uses the 5 values (via NEXT VALUE FOR > clauses), Derby pre-allocates the next 5 values, and so on. Pre-allocation > improves the performance of sequences in multi-threaded applications. The > endpoint of a pre-allocation range is stored in SYSSEQUENCES.CURRENTVALUE. > > It looks to me like what you want to do is get the next sequence value and > then insert it into both a primary key table and a referencing foreign key > table. Something like this should work: > > 1) Get the next value from the sequence via this statement: > > ResultSet rs = conn.prepareStatement( "values ( next value for seq_type > )" ).executeQuery(); > int seqValue = rs.getInt( 1 ); > > 2) Then insert it into the primary key table, using a ? parameter: > > PreparedStatement ps = conn.prepareStatement( "INSERT INTO t_type (id, > label) VALUES (?, ?)" ); > ps,setInt( 1, seqValue ); > ps.setString( 2, "sample type" ); > > 3) Then insert it into the foreign key table using the same pattern. > > Hope this helps, > -Rick > >
