db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From squidy78 <squid...@gmail.com>
Subject derby with sequences and currentvalue
Date Tue, 24 Jan 2012 08:09:02 GMT
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
-- 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 for this tests.

thanks for helping!

View raw message