Re: Next Number
In article <25dnghINN7b0@emory.mathcs.emory.edu> cheryl@rrad05.army.mil ("cheryl@redriverad-emh1.army.mil") writes:
>When I create a table for use in a database, I tried to have
>a unique numeric key to that table.
>
>for instance:
...
>$ select nxt_nbr into $nxtn
>from next_n where nxt_desc = "nsn_table";
...>/* with nxtn being the next sequential number */
>$ insert into nsn values
>($nxtn, $stock, $mfg, $quantity, ...other fields)>
>then whatever you select the nxt_nbr into
>nxtn=nxtn+1 (whatever the syntax in your version, isql,4gl, esql-c)
>or
>$ update next_n set nxt_nbr to nxt_nbr + 1 where nxt_desc = "nsn_table";
This is the test-and-set problem. If you can't use SERIALS, the answer
is to use transactions (with wait locking or a loop):
$begin work
$update next_n
set nxt_nmbr = nxt_nmbr + 1
where nxt_desc = "nsn_table";
$select nxt_nbr into :nxtn
from next_n
where nxt_desc = "nsn_table";
$commit work
Now you have an internal-id you can use in your other objects.
This works because the update locks the row until the transaction
is closed, thereby preventing another process from dirtying your
value.
Andrew Shaw
AT&T Bell Labs
andrew.shaw@att.com