Obtaining the Next Number in a Sequence

Tony Marston - 1st May 2003


There is sometimes the need to obtain the next number in a sequence, usually to provide a value for a primary key. There are various different methods available, as described below.

1. UNIFACE counters

UNIFACE provides the ability to maintain sets of counters inside the UOBJ file. These can be created by the NUMSET command and incremented by the NUMGEN command, as shown in the following sample procedure:-

entry GET_NEXT_NUMBER
params
  string  PI_COUNTER_NAME   : IN
  numeric PO_COUNTER_VALUE  : OUT
endparams

numgen PI_COUNTER_NAME, 1, $variation    ; increment counter
if ($status < 0)                         ; name not found
  numset PI_COUNTER_NAME, 1, $variation  ; create counter, start at 1
  if ($procerror)
    call PROC_ERROR($procerrorcontext)
    rollback "$UUU"
    return(-1)
  endif
endif

commit "$UUU"               ; update UOBJ

PO_COUNTER_VALUE = $result  ; return result

return(0)

end; GET_NEXT_NUMBER

This procedure can be called using code similar to the following:-

call GET_NEXT_NUMBER("INVOICE_ID", invoice_id.invoice)
if ($procerror)
  call PROC_ERROR($procerrorcontext)
  return(-1)
endif  

However, using UNIFACE counters does have the following drawbacks:-

Due to these problems it is strongly recommended that UNIFACE counters not be used.

2. Runtime Retrieval

This method does not use a separate field on the database to hold the counter value. Instead it uses the selectdb statement at the appropriate time to identify the highest number used so far on the application database so that it can be incremented, as in the following example:-

$1 = 0
selectdb max(customer_no) from "customer"         %\
  u_where (sman_id.customer = sman_id.salesman)   %\
  to $1
customer_no.new_customer = $1+1

This method does, however, have the following drawbacks:-

If the field name used is indexed then the DBMS may scan the index rather than the table, which will reduce the response time. In the example where the primary key of CUST_ADDRESS is a combination of CUST_ID and ADDRESS_NO, the following code can be used to provide the next available value for ADDRESS_NO:

$1 = 0
selectdb max(address_no) from "cust_address" %\
  u_where (cust_id.cust_address = cust_id.customer) to $1
address_no.cust_address	= $1 + 1

3. Database Triggers

The underlying DBMS may support a function within a trigger that can generate the next available value for a primary key field. This may be an efficient method, but it does present the following disadvantages:-

4. Database Procedures

The underlying DBMS may support a function within a procedure that can generate the next available value for a primary key field. Unlike a trigger a procedure is called from within a UNIFACE component and returns its result back to that component. However, it does present the following disadvantages:-

5. Database Sequences

It may be possible to access a sequence number which is maintained by your database engine using an SQL command such as the following:-

SQL "SELECT <seq_name>.NEXTVAL FROM DUAL","DEF"

This method does have the following disadvantages:-

6. Application Counters

This method uses counters that are located within the application database itself, thus avoiding some of the problems with UNIFACE counters in a separate UOBJ file. These counters can be located in any of the following places:-

6.1 Single Control record

The control table contains a single record, but this contains a separate field for each counter. Every time a new number is required from any counter the single control record must be locked, which will cause delays and conflicts in a system with a large number of users. This method should therefore be avoided.

6.2 Multiple Records in a Single Control File

There is still a single control file, but this time it contains a separate record (occurrence) for each counter. Each record contains an identity (counter name) and value (last number used). This method causes fewer locking conflicts as it is only when the same counter is updated that the same record is locked.

Refer to A flexible method of storing control data for more details.

6.3 Multiple Records in Multiple Files

When a counter value is used as a single-item primary key because that number must be unique across the whole database (eg: a customer number, an invoice number) then that counter must be obtained from a single source.

When a counter value is combined with another field (or fields) to produce a compound key (as in a ONE-to-MANY relationship, for example) the location of this counter should be on the foreign/ONE entity.

Figure 1 - Using sequence number from foreign entity

one-to-many (1K) Contains the field LAST_SEQ_NO
 
Related to ONE entity via field FOREIGN_KEY
Primary Key = FOREIGN_KEY + SEQ_NO

In this example the primary key of the MANY entity is comprised of the foreign key field (which relates it to the ONE entity) and a field called SEQ_NO. New values for SEQ_NO (on MANY) are obtained by incrementing the current value in LAST_SEQ_NO (on ONE). Note the use of LAST instead of NEXT - when an occurrence of ONE is created the initial value of LAST_SEQ_NO is zero (or null). The code to obtain values for SEQ_NO should be similar to the following:-

<accept> trigger

retrieve/e "one"                          ; ** only if not already retrieved **
last_seq_no.one = (last_seq_no.one + 1)   ; increment
seq_no.many = last_seq_no.one             ; use

call OK_PROC                              ; update both MANY and ONE entities

This processing should take place at the last possible moment (ie: immediately prior to the store command), therefore it may be necessary to assign a temporary value to SEQ_NO to avoid the problem caused by leaving a required field with a null value. This may be done with code similar to the following:-

<occurrence gets focus> trigger

if (seq_no.many = "")
  seq_no.many/init = 0    ; assign dummy value
endif

This method has the following advantages:-

7. Conclusion

If somebody ever says that there are limited ways in which sequence numbers for use in primary keys can be obtained you can now wave this article under their noses and ask them to think again. Choosing a method simply because it is the only one you know about is a poor excuse in my book. It is the designer's responsibility to research all the possible alternatives so that the best one for the particular circumstances in question can be selected. The "one size fits all" approach shows a distinct lack of experience.


Tony Marston
1st May 2003

mailto:tony@marston-home.demon.co.uk
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter