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 with sequences and currentvalue
Date Tue, 24 Jan 2012 16:15:16 GMT
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,
> On Tue, Jan 24, 2012 at 2:29 PM, Rick Hillegas<rick.hillegas@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 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

View raw message