Release 10.1A: OpenEdge Development:
Programming Interfaces

> ## Accessing and incrementing sequences

> Figure 1–8 lists Progress statements and functions you can use to access and increment sequence values from the 4GL.

> Table 1–8: Sequence statements and functions
> Statement or function | Description
> --- | ---
> `CURRENT–VALUE` function | Returns the current value of a sequence.
> `DYNAMIC–CURRENT–VALUE` function | Returns the current value of a dynamically-specified sequence.
> `NEXT–VALUE` function | Increments and returns the incremented value for a sequence.
> `DYNAMIC–NEXT–VALUE` function | Increments and returns the incremented value for a dynamically-specified sequence.
> `CURRENT–VALUE` statement | Sets a new current value for a sequence.
> `DYNAMIC–CURRENT–VALUE` statement | Sets a new current value for a dynamically-specified sequence.

> Whenever a `CURRENT–VALUE` statement or `NEXT–VALUE` function changes the value of a sequence, the new value persists in the database where the sequence is defined until it is changed again, or the sequence is deleted from the database.

> The sequence statements and functions have the following syntax:

> Syntax
>     CURRENT-VALUE
>       (sequence [ , logical-dbname ] ) [ = expression ]

> Syntax
>     NEXT-VALUE
>       (sequence [ , logical-dbname ] )

> `sequence`

> An identifier that specifies the name of a sequence defined in the Data Dictionary. Note that a sequence can have the same name as a database field, but they are distinct entities.

> `logical-dbname`

> An identifier that specifies the logical name of the database in which the sequence is defined. The database must be connected. You can omit this parameter only if you have a single database connected. Otherwise, you must specify the database in which the sequence is defined.

> `expression`

> An expression that evaluates to an integer value. The `CURRENT–VALUE` statement assigns the value of `expression` to the specified sequence. The value of `expression` must be within the range defined for the specified sequence.

> The dynamic sequence statement and functions have the same general syntax, but allow you to specify the sequence and database dynamically at run time. Their syntax is as follows:

> Syntax
>     DYNAMIC-CURRENT-VALUE
>       (sequence-expression, logical-dbname-expression ) [ = expression ]

> Syntax
>     DYNAMIC-NEXT-VALUE
>       (sequence-expression, logical-dbname-expression )

> `sequence-expression`

> A character expression which evaluates to the name of a sequence defined in the Data Dictionary. Note that a sequence can have the same name as a database field, but they are distinct entities.

> `logical-dbname-expression`

> A character expression which evaluates to the logical name of the database in which the sequence is defined. The database must be connected. This argument is required and cannot be Unknown value (`?`).

> `expression`

> An expression that evaluates to an integer value. The `DYNAMIC–CURRENT–VALUE` statement assigns the value of `expression` to the specified sequence. The value of `expression` must be within the range defined for the specified sequence.

> In general, use the `CURRENT–VALUE` and `NEXT–VALUE` functions for mission-critical applications that depend on access to reliable and orderly sequence values. Use the `CURRENT–VALUE` statement only for database maintenance and initialization, primarily during the development cycle. For more information on these functions and statements, see OpenEdge Development: Progress 4GL Reference .

> ##### Using the CURRENT–VALUE function

> Use the `CURRENT–VALUE` function in any integer expression to retrieve the current value of a sequence without incrementing it. The current value of a sequence can be any one of the following:

> - The Initial value specified in the Dictionary.

> - The last value set with either the `CURRENT–VALUE` statement or the `NEXT–VALUE` function.

> The following example gets the most recent customer number in the default database (maintained by the cust–num sequence), and displays each order record for that customer:

>     DEFINE VARIABLE current–cust AS INTEGER.
>                   .
>                   .
>                   .
>     current-cust = CURRENT-VALUE (cust-num).
>     FOR EACH order WHERE cust-num = current-cust:
>         DISPLAY order.
>                   .
>                   .
>                   .

> ##### Using the NEXT–VALUE function

> Use the `NEXT–VALUE` function to increment a sequence by its defined positive or negative increment value. If the sequence cycles and `NEXT–VALUE` increments it beyond its Upper or Lower limit, the function sets and returns the defined Initial value for the sequence. If the sequence terminates and `NEXT–VALUE` tries to increment it beyond its Upper or Lower limit, the function returns the Unknown value (`?`) and leaves the sequence value unchanged.

> The following example creates a new customer record with the next available customer number generated by the cust-num sequence:

>     DEFINE VARIABLE input-cust-num AS INTEGER.
>     input-cust-num = NEXT-VALUE (cust-num, sports).
>     FIND sports.customer WHERE cust-num = input-cust-num NO-ERROR.
>     DO WHILE (AVAILABLE customer):
>       input-cust-num = NEXT-VALUE (cust-num, sports).
>       FIND sports.customer WHERE cust-num = input-cust-num
>         NO-ERROR.
>     END.
>     CREATE sports.customer.
>     customer.cust-num = input-cust-num.
>     UPDATE sports.customer EXCEPT cust-num.
>                       .
>                       .
>                       .

> Because this example does not check the cust-num sequence for termination, it implies that cust-num is a cycling sequence. Because it does check for and ignore existing records containing the generated cust-num value, the example can reuse previously deleted (or otherwise skipped) customer numbers after the sequence cycles.

> ##### Using the CURRENT–VALUE statement

> Use the `CURRENT–VALUE` statement to explicitly set a sequence to a new value. You can assign the defined initial value of a sequence, its upper or lower limit, or any integer value in between. Trying to set a value outside these bounds causes an error. Note that you cannot assign the Unknown value (`?`) to a sequence. Unlike the `NEXT–VALUE` function, the `CURRENT–VALUE` statement always sets a new sequence value in a transaction, starting one, if necessary.

> Caution: Avoid using this statement in mission-critical applications. Use of this statement, especially in a multi-user context, can compromise the referential integrity of your database. Any database application designed to rely on the orderly values provided by sequences cannot reliably reset existing database fields according to potentially unexpected sequence values.

> The purpose of the `CURRENT–VALUE` statement is to maintain a database off-line, under program control. Note that the Data Dictionary uses this statement when you create a new sequence to set the initial value. Possible uses include:

> - Restarting a terminating sequence that has terminated.

> - Otherwise, resetting a sequence value for a specific test condition.

> Keep in mind that all such uses must respect and might require changes to the _Sequence table. This table contains one record for each sequence defined in the database. Creating a record in this table automatically allocates and assigns the data storage for a new sequence. Deleting a record in this table automatically releases the data storage for the specified sequence.

> Progress provides fault detection that prevents a sequence from being created with inconsistent attribute values in the _Sequence table (such as, the minimum value greater than the maximum value). It also prevents the assignment of a new current value that is inconsistent with the corresponding attribute values in the _Sequence table (such as, a current value outside the minimum and maximum value boundary).

> The following code fragment sets a new current value for sequence used for testing:

>     DEFINE VARIABLE SeqValue AS INTEGER.
>     DO TRANSACTION ON ERROR UNDO, RETRY:
>         SET SeqValue LABEL "TEST-Stepper Start" WITH SIDE-LABELS.
>         CURRENT-VALUE(TEST-Stepper) = SeqValue.
>     END. /* TRANSACTION */
>                       .
>                       .
>                       .

> Progress also uses this statement in the Data Administration tool to load sequence definitions and values from dump-formatted text files. You can accomplish the same task implemented in the code fragment above using this tool. For more information, see OpenEdge Data Management: Database Administration .

> ##### Compiling procedures that reference sequences

> When compiling procedures that use sequence statements and functions, do not run OpenEdge with the Time Stamp (-`tstamp`) startup parameter. All references to `CURRENT-VALUE` and `NEXT–VALUE` statements and functions in the r-code depend on CRC calculations made with the _Sequence metaschema table, and are not available with time stamping. CRC validation is now the default data consistency option for OpenEdge.

> ##### Resetting a terminating sequence

> When a terminating sequence stops at its upper or lower limit, you can reset it by assigning a valid sequence value to it with the `CURRENT–VALUE` statement. You can also reset a stopped terminating sequence by changing it to a cycling sequence. The first use of the `NEXT–VALUE` function for the new cycling sequence resets the sequence to its initial value.

> ##### Referencing sequences within a WHERE clause

> You cannot invoke sequence functions from within a `WHERE` clause. This generates a compiler error, because sequence expressions do not participate in the index resolution and optimization phase of the Compiler.

> If you want to use a sequence value within a `WHERE` clause, set a variable or field to the sequence value, and reference the variable or field in your `WHERE` clause. See the sections that describe each sequence function for examples.

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