Using sequences or control tables

Programming Interfaces

Data Management : Database Access : Sequences : Choosing between sequences and control tables : Using sequences or control tables

Before Progress Version 7, the only way ABL could generate sequential values was to maintain integer fields in a table record created specifically for this purpose. This is often done in a control table, separate from all other application tables.

For example, you might have a control table named syscontrol that contains the field last–cus–num. This field holds the value of the CustNum field for the last Customer record. The following code fragment generates a new Customer record with a unique Customer number:

. . .
DEFINE VARIABLE next-custnum NO-UNDO LIKE Customer.CustNum.

DO FOR syscontrol:
  DO TRANSACTION:
    FIND FIRST syscontrol EXCLUSIVE-LOCK.
    next-custnum = syscontrol.last-cus-num + 1.
    syscontrol.last-cus-num = next-custnum.
  END. /* transaction */
  RELEASE syscontrol.
END.

DO TRANSACTION:
  CREATE Customer.
  Customer.CustNum = next-custnum.
  DISPLAY Customer.
  UPDATE Customer.
END. /* transaction */
. . .

Note that access to the syscontrol table must be made within a small transaction to avoid lock contention problems.

Sequences provide a built-in means for generating incremental values, but they are not suitable for all applications. In certain situations (described in the following sections), you might need to use a control table, instead.

The following table compares sequences and control tables.

Table 8. Comparison of sequences and control tables
Capability	Sequences	Control tables
Access speed	Fast	Slow
Transaction independent	Yes	No
Guaranteed order	Yes	No
Auto initializing	Yes	No
Auto cycling	Yes	No
Bounds checking	Yes	No
Database limit	250 per 1K database block size	Field limit