Progress
Programming
Handbook
Accessing and Incrementing Sequences
Figure 9–8 lists Progress statements and functions you can use to access and increment sequence values from the 4GL.
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:
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.
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.
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 following example gets the the most recent customer number in the default database (maintained by the cust–num sequence), and displays each order record for that customer:
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:
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:
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 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.
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 the Progress Database Administration Guide and Reference.
Compiling Procedures That Reference Sequences
When compiling procedures that use sequence statements and functions, do not run Progress 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 Progress.
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 © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |