db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: derby 10.8.2.2 with sequences and currentvalue
Date Tue, 24 Jan 2012 13:29:28 GMT
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



Mime
View raw message