Progress
Version 9
Product Update Bulletin


Progress SQL-92 Support For Progress Sequences

With the release of Version 9.1D, Progress SQL-92 supports the sequence generator used in Progress 4GL applications. Sequences are database objects that generate unique integers. Because the SQL-92 sequence generator generates numbers independently of tables, the same sequence can be used for multiple tables.

In order to support the generation of sequences, the following additions were made to Progress SQL-92:

The following sections detail the above additions to Progress SQL-92.

CREATE SEQUENCE Statement

Use the following syntax to generate a sequence:

SYNTAX
CREATE SEQUENCE [schema_name.]sequence_name 
     [INCREMENT BY value], 
     [START WITH value], 
     [MAXVALUE value | NOMAXVALUE], 
     [MINVALUE value | NOMINVALUE], 
     [CYLCE | NOCYLCE] 

schema_name

Specifies the schema to contain the sequence. If schema_name is not specified, the sequence generator creates the sequence in the current schema.

NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.

sequence_name

Specifies the name of the sequence to be created.

INCREMENT BY

Specifies the interval between sequence numbers. The value can be any positive or negative integer, but cannot be 0. When the value is positive, the sequence ascends. If it is negative, the sequence descends. The default value is 1.

START WITH

Specifies the first sequence number generated. In an ascending sequence, the value must be greater than or equal to the MINVALUE. In a descending sequence, the value must be greater than or equal to the MAXVALUE. For ascending sequences, the default value is MINVALUE. For descending sequences, the default value is MAXVALUE.

MAXVALUE

Specifies the maximum value for the sequence to generate. For an ascending sequence, the default value is 2,147,483,647. For a descending sequence, the default value is -1.

NOMAXVALUE

Specifies -1 as the MAXVALUE for descending sequences and 2,147,483,647 as the MAXVALUE for ascending sequences.

MINVALUE

Specifies the minimum value the sequence can generate. For an ascending sequence, the default value is 0. For a descending sequence, the default value is -2,147,483,648.

NOMINVALUE

Specifies 0 as the MINVALUE for ascending sequences and -2,147,483,648 as the MINVALUE for descending sequences.

CYLCE

Indicates that the sequence will continue to generate values after reaching the value assigned to MAXVALUE (if sequence ascends) or MINVALUE (if sequence descends).

NOCYCLE

Indicates that the sequence cannot generate more values after reaching the value assigned to MAXVALUE (if sequence ascends) or MINVALUE (if sequence descends). The SQL-92 sequence generator uses NOCYCLE as the default if CYCLE is not specified.

In the following example, a sequence is used to generate unique customer numbers when a new customer is inserted into the table pub.customer.

EXAMPLE

CREATE SEQUENCE pub.customer_sequence
     START WITH 100,
     INCREMENT BY 1,
     NOCYLCE;

DROP SEQUENCE Statement

The DROP SEQUENCE Statement removes a sequence from a schema. You must have Database Administration privileges to remove a sequence.

Use the following syntax to remove a sequence:

SYNTAX
DROP SEQUENCE [schema_name.]sequence_name 

schema_name

Specifies the schema name that contains the sequence. If this is not specified, Progress SQL-92 drops the sequence, if present, from the current schema.

NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.

sequence_name

Specifies the sequence to be dropped.

In the following example, a sequence named customer is dropped from the public (PUB) schema.

EXAMPLE

DROP SEQUENCE pub.customer

CURRVAL and NEXTVAL In a Statement

Sequences contain two methods: CURRVAL and NEXTVAL. As detailed in the "CREATE SEQUENCE Statement" section, when you create a sequence you can define its initial value and the increment between its values. CURRVAL returns the current value of the sequence and NEXTVAL returns the sequence’s next value. References to NEXTVAL increment the sequence value by the defined increment and return the new value. References to CURRVAL always return the sequence’s current value, which is the value returned by the last reference to NEXTVAL.

Use the following syntax to reference the current value of a sequence:

SYNTAX
schema.sequence.CURRVAL 

schema

Specifies the schema that contains the sequence. To refer to the current value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.

NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.

sequence

Specifies the name of the sequence whose current value you want.

Use the following syntax to reference the next value of a sequence:

SYNTAX
schema.sequence.NEXTVAL 

schema

Specifies the schema that contains the sequence. To refer to the next value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.

NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.

sequence

Specifies the name of the sequence whose next value you want.

NOTE: A statement referencing NEXTVAL for a noncycling sequence returns an error after reaching the maximum value. For more information on cycling and noncycling sequences, see the "CREATE SEQUENCE Statement" section.

Use CURRVAL and NEXTVAL in the:

CURRVAL and NEXTVAL cannot be used in:

EXAMPLES

In the following example, the Progress SQL-92 sequence generator returns the current value of the customer sequence:

SELECT customer_sequence.CURRVAL FROM pub.customer;

NOTE: Sequences are not associated with tables. This example provides reference to a table in order to conform to correct SQL-92 syntax.

In the following example, the sequence generator increments the customer sequence and uses its value for a new customer inserted into the table pub.customer:

INSERT INTO pub.customer VALUES (customer_sequence.NEXTVAL,’USA’,’BackCountry Equipment’,’Sugar Hill Road’,’12A’,’Franconia’,’NH’,’03242’,’Dan Egan’,’603-762-2121’,’Kirsten Ulmner’, 10000.00, 500.00,’net 10’, 0,’contact monthly’);

GRANT Statement

Use the GRANT statement to assign privileges for accessing sequences. As database administrator, you can assign SELECT and UPDATE privileges to specific sequence objects.

Use the following syntax to assign sequence privileges:

SYNTAX
GRANT [SELECT | UPDATE] 
ON SEQUENCE schema.sequence 
TO user_name [,user_name]... 

SELECT

Allows specified user to read data from the sequence

UPDATE

Allows specified user to modify data for the sequence

EXAMPLE

In this example, the sequence generator grants user slsadmin the ability to modify the customer number sequence.

GRANT UPDATE
     ON SEQUENCE pub.customer_sequence
     TO slsadmin;


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095