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

[fredt <fr...@us...>]

INSERT INTO SIMPLE VALUES (NEXT VALUE FOR SIMPLE_SEQ,'Name1')
  ----- Original Message -----=20
  From: Nitin Uchil=20
  To: hsq...@li...=20
  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?


  =20




  =20
  On 4/21/06, fredt <fr...@us...> wrote:=20
    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 -----=20
    From: Nitin Uchil=20
    To: hsq...@li...=20
    Sent: 21 April 2006 19:57
    Subject: Re: [Hsqldb-developers] Re: Issue with SEQUENCE

    =20
    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=20
    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?=20

    CREATE TABLE dual (
    test  INTEGER NOT NULL
    );

    INSERT INTO dual VALUES (1);



    =20
    On 4/21/06, fredt <fr...@us... > wrote:=20
      >> was similar to DUAL in Oracle=20

      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 -----=20
      From: Nitin Uchil=20
      To: hsq...@li...=20
      Sent: 21 April 2006 19:21
      Subject: Re: [Hsqldb-developers] Re: Issue with SEQUENCE

      =20
      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:=20
        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 -----=20
          From: Nitin Uchil=20
          To: hsq...@li... ; =
fr...@us...=20
          Sent: 21 April 2006 18:31
          Subject: Issue with SEQUENCE

          =20
          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?

          --=20
          Nitin Uchil
          =20



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



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



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