Re: [Hsqldb-developers] Re: Issue with SEQUENCE

[Nitin Uchil]

I assumed that SYSTEM_SEQUENCES was similar to DUAL in Oracle in that you can run NEXTVAL for a sequence name.

 

Based upon your suggestion, here is what I did:

 

1. Created SEQUENCE

CREATE SEQUENCE sequence_SEQ_ID
AS INTEGER
START WITH 1
INCREMENT BY 1;

 

2. Create TABLE

CREATE TABLE test_sequence (
sequence_seq_id      INTEGER NOT NULL
,description     VARCHAR(50) NOT NULL
);

 

Notice that the first field of the table has the same name as the sequence.

 

3. Next value?

SELECT NEXT VALUE FOR sequence_seq_id FROM test_sequence;

does not return anything

 

4. Insert a record into test_sequence table

INSERT INTO test_sequence VALUES (1,'test');

 

5. Try again

SELECT NEXT VALUE FOR sequence_seq_id FROM test_sequence;

This time it returns 1 (shouldn't it be 2).

 

Please help.

 

Nitin

On 4/21/06, fredt <[EMAIL PROTECTED]> wrote:

SYSTEM_SEQUENCES should not be used for getting sequence values. Use your own tables. For example

 

SELECT NEXT VALUE FOR login_password_SEQ FROM  my_table;

 

Sequences are designed mainly for inserting new rows, or for updating existing rows, e.g.

 

INSERT INTO my_table (a, b, c) VALUES (NEXT VALUE FOR login_password_seq, 10, 'a string');

 

Fred

----- Original Message -----

From: Nitin Uchil

To: hsqldb-developers@lists.sourceforge.net ; [EMAIL PROTECTED]

Sent: 21 April 2006 18:31

Subject: Issue with SEQUENCE

 

I have recently started using SEQUENCES in HSQLDB (version 1.8.0.4)

 

CREATE SEQUENCE login_password_SEQ
AS INTEGER
START WITH 1
INCREMENT BY 1;

 

When I issue:

SELECT NEXT VALUE FOR login_password_SEQ FROM  INFORMATION_SCHEMA.SYSTEM_SEQUENCES

 

It produces 46 values! The first time it creates 1 thru 46, the second 47 thru 92......

 

This is true for any other SEQUENCE also.

 

What am I doing wrong?

--
Nitin Uchil
 

--
Nitin Uchil
(734)945-6463