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

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

Thanks Fred:

I finally figured it out:

If I run:
SELECT NEXT VALUE FOR sequence_seq_id FROM test_sequence;

It returns as many values as there are records in the table starting from
the next available. This explains the 46 I got since I have 46 seqences
defined, the SYSTEM_SEQUENCES table had 46 entries.

NEXT VALUE FOR sequence_seq_id
placed by itself does not work - needs a FROM clause!

So I created a DUAL table with just one record and am running by next value
like so:

NEXT VALUE FOR sequence_seq_id FROM dual.

But I am concerned that the dual table might have additional records in
which case it will create a problem. Is there a way to lock it or to have a
SYSTEM table that always has 1 record that I can use the NEXT VALUE FOR
clause?


CREATE TABLE dual (
test  INTEGER NOT NULL
);

INSERT INTO dual VALUES (1);



On 4/21/06, fredt <fr...@us...> wrote:
>
>  >> was similar to DUAL in Oracle
>
>  The dual table is Oracle is just a table with a single row. You can make
> your own.
>
> >>  the first field of the table has the same name as the sequence
>
>  Same name or not, it is completely irrelevant.
>
> >> SELECT NEXT VALUE FOR sequence_seq_id FROM test_sequence; does not
> return anything
>
>  Returns nothing because test_sequence has no rows.
>
> >> SELECT NEXT VALUE FOR sequence_seq_id FROM test_sequence; This time it
> returns 1 (shouldn't it be 2).
>
>  It returns 1 because it is the first time a value from the sequence has
> been returned. Next time you use the same SELECT, it will return 2.
>
> Fred
>
>  ----- Original Message -----
> *From:* Nitin Uchil <nit...@gm...>
> *To:* hsq...@li...
>  *Sent:* 21 April 2006 19:21
> *Subject:* Re: [Hsqldb-developers] Re: Issue with SEQUENCE
>
>
> 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
> > 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 <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
>
>


--
Nitin Uchil
(734)945-6463