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

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

Thanks Fred:

I was trying:

 INSERT INTO SIMPLE VALUES ((NEXT VALUE FOR SIMPLE_SEQ),'Name1')

which does not work



On 5/12/06, fredt <fr...@us...> wrote:
>
>   INSERT INTO SIMPLE VALUES (NEXT VALUE FOR SIMPLE_SEQ,'Name1')
>
>  ----- Original Message -----
> *From:* Nitin Uchil <nit...@gm...>
> *To:* hsq...@li...
>  *Sent:* 12 May 2006 22:47
> *Subject:* Re: [Hsqldb-developers] Re: Issue with SEQUENCE
>
>
> Hello Fred:
>
> Understanding SEQUENCES
>
> Premise:
> I have a simple table containing two fields:
>
> CREATE TABLE SIMPLE(SEQ_ID INTEGER NOT NULL,NAME VARCHAR(100) NOT
> NULL,CONSTRAINT SIMPLE_PK_1 PRIMARY KEY(SEQ_ID),CONSTRAINT SIMPLE_UK_1
> UNIQUE(NAME))
>
> I want the primary key of the table to be a sequence. So I define a
> SEQUENCE
>
> CREATE SEQUENCE SIMPLE_SEQ AS INTEGER START WITH 1
>
> Now I want to Insert like so:
>
> 1. INSERT INTO SIMPLE VALUES ((CALL NEXT VALUE FOR SIMPLE_SEQ),'Name1')
>
> does not work.
>
> 2. INSERT INTO SIMPLE VALUES ((SELECT NEXT VALUE FOR SIMPLE_SEQ FROM
> DUAL),'Name1')
>
> works where DUAL is a table I created that has 1 record.
>
> Is there a more elegant way of inserting the sequence without using the
> DUAL table?
>
>
>
>
>
>
>
>
> On 4/21/06, fredt <fr...@us...> wrote:
> >
> >  If you just want the next value for a sequence, you can use a CALL
> > statement:
> >
> > CALL NEXT VALUE FOR sequence_seq_id
> >
> >
> >
> >  ----- Original Message -----
> > *From:* Nitin Uchil <nit...@gm...>
> > *To:* hsq...@li...
> >  *Sent:* 21 April 2006 19:57
> > *Subject:* Re: [Hsqldb-developers] Re: Issue with SEQUENCE
> >
> >
> > 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 seq=
ences
> > 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 ha=
ve 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 tim=
e
> > > 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
> >
> >
>
>
> --
> Nitin Uchil
> (734)945-6463
>
>


--=20
Nitin Uchil
(734)945-6463