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

The update of UOBJ is committed before any updates to the application database, therefore if there is any failure with the application updates the counter cannot be rolled back to its previous value, especially if other users have incremented the same counter in the mean time.
Each time a counter is incremented the UOBJ file must be locked - this may cause problems if there are a large number of users all trying to increment counters at the same time.
If the UOBJ file is ever replaced or updated (eg: following the release of a new version of the application) all the counters are lost. These may be re-instated manually, but this presupposes that the latest counter values were obtained before the UOBJ file was replaced. It may be wise to create a special form that uses SELECTDB statements to identify the highest values found on the application database and to reset the counters accordingly.
Users of the same application database must access the same set of counters in the same UOBJ file, otherwise the same value will eventually be obtained from different sequences, resulting in a duplicate primary key error when trying to update the database. If, for example, groups of users in a large network are accessing different (local) copies of UOBJ then UNIFACE counters cannot be used without the possibility of producing a duplicate number.
Users who share the same UOBJ file must also share the same application database. In the situation where there are different copies of the application database (eg: for development, system testing, user training, live) there will be gaps in the numbers used on any one copy of the application database. This may cause confusion to the users, or may cause the field size to be overflowed sooner than expected.

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

Where the field is not indexed multiple database records will have to be scanned in order to identify the highest number used, and the time taken to complete this statement will increase in direct proportion to the number of entries on the specified database table.
If the form is capable of creating more than one new occurrence at a time then this increases the areas that need to be searched in order to identify which is the highest value used so far - the database and the form's own external structure. This increases the complexity of the code, and therefore increases the possibility of error.
It is possible that in the interval between executing the selectdb and actually performing the store another user has crept in and used the same number. In order to avoid the update failing with a duplicate primary key error the following code could be inserted into the <write> trigger:
```
write
while ($status = -7)             ; duplicate primary key
  customer_no = customer_no + 1  ; try next number
  write
endwhile
```

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

Not all DBMS's support this functionality, therefore the choice of database engines for this application would be limited.
The value supplied by the DBMS would not be passed back to the UNIFACE form as part of the store operation, therefore this mechanism cannot be used if the value is to be used for another entity's foreign key within the same operation.
As database triggers are defined manually within the database and are outside the control of UNIFACE this means that application logic is split across separate environments, which could lead to possible documentation and maintenance difficulties.
If a database trigger has been turned off or deactivated this fact would not become apparent until a database update failed, and the error message may not make it immediately obvious as to the origin of the error.

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

Not all DBMS's support this functionality, therefore the choice of database engines for this application would be limited.
As database procedures are defined manually within the database and are outside the control of UNIFACE this means that application logic is split across separate environments, which could lead to possible documentation and maintenance difficulties.

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

The sequence name must be created in the database before it can be accessed otherwise an error will be generated.
Once the statement has been executed and the sequence number is incremented it is not possible to rollback the update.
Care must be taken when allocating sequence names - if different developers accidentally use different names for the same sequence number this will eventually lead to duplicate values.
This method is acceptable when you require a single number at a time, but if you require a batch of N numbers then you must issue the statement N times.

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

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

If is is required to create multiple occurrences of MANY in a single transaction it is not necessary to perform a separate database I/O operation to obtain the next sequence number - the ONE entity is read in once at the start of the transaction, so new sequence numbers can be obtained simply by incrementing LAST_SEQ_NO.
Because the primary key of MANY is made up of PKEY.ONE + SEQ_NO it is not necessary to have a separate index for the foreign key relationship. Searches on particular values of PKEY.ONE can be adequately satisfied from the primary key index. Contrast this against the design where the primary key of MANY is a single number which is obtained from its own sequence - the foreign key linking back to the ONE entity would have to have its own separate index rather than being able to share the primary key index. This matter is discussed in greater detail in Technical Keys - Their Uses and Abuses.
Multiple users can create occurrences of MANY for different occurrences of ONE without any locking conflicts or duplicate keys.
Sequence numbers can be reused, if required. If you delete the last occurrence of MANY it is possible for the code to decrement LAST_SEQ_NO on the ONE entity.
If it is ever required to obtain the first occurrence of MANY then rather than searching through all occurrences to find the one with the earliest date you can simply use where SEQ_NO=1.
If it is ever required to obtain the last occurrence of MANY then rather than searching through all occurrences to find the one with the latest date you can simply use where SEQ_NO=LAST_SEQ_NO.ONE.

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@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

counter

Obtaining the Next Number in a Sequence