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