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

Wed, 17 May 2006 05:29:05 -0700

Thanks Fred:
 
I was trying:
 
INSERT INTO SIMPLE VALUES ((NEXT VALUE FOR SIMPLE_SEQ),'Name1')
 
which does not work


 
On 5/12/06, fredt <[EMAIL PROTECTED]> wrote:
INSERT INTO SIMPLE VALUES (NEXT VALUE FOR SIMPLE_SEQ,'Name1')
----- Original Message -----
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 <[EMAIL PROTECTED] > 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 -----
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 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 -----
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 -----
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



--
Nitin Uchil
(734)945-6463

Reply via email to