You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by squidy78 <sq...@gmail.com> on 2012/01/24 09:09:02 UTC

derby 10.8.2.2 with sequences and currentvalue

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!

Re: derby 10.8.2.2 with sequences and currentvalue

Posted by Rick Hillegas <ri...@oracle.com>.
On 1/24/12 7:42 AM, squidy78 wrote:
> 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?
If your application is single-threaded (which would support 
nextval/currval usage), then the insert into the foreign table could 
look like this:

INSERT INTO t_text (id, text, typeid) VALUES (next value FOR seq_text,
'first text', (SELECT max( id ) FROM t_type ));
> Is there something planned to implement in next derby versions like
> it's implemented in H2 (nextval/currval)?
I'm not familiar with H2's system functions, but a quick glance at H2's 
online documentation suggests that you can write corresponding Derby 
functions today. Your nextval() function would wrap a call to NEXT VALUE 
FOR and would remember the result in a static variable for use by the 
next call to currval(). See the section on CREATE FUNCTION in the Derby 
Reference Guide: http://db.apache.org/derby/docs/10.8/ref/

Hope this helps,
-Rick
>
> On Tue, Jan 24, 2012 at 2:29 PM, Rick Hillegas<ri...@oracle.com>  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
>>
>>

Re: derby 10.8.2.2 with sequences and currentvalue

Posted by squidy78 <sq...@gmail.com>.
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 <ri...@oracle.com> 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
>
>

Re: derby 10.8.2.2 with sequences and currentvalue

Posted by Rick Hillegas <ri...@oracle.com>.
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