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

[Nitin Uchil]

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 <[EMAIL PROTECTED]> 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

To: hsqldb-developers@lists.sourceforge.net

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 <[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

--
Nitin Uchil
(734)945-6463