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

[Nitin U. <nit...@gm...>]

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 <fr...@us...> wrote:
>
>  SYSTEM_SEQUENCES should not be used for getting sequence values. Use you=
r
> 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 <nit...@gm...>
> *To:* hsq...@li... ;
> fr...@us...
> *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