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 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
>
>