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.
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.
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:-
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
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:-
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:-
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:-
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:-
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.
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.
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 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.
1st May 2003