Tim,
At 10:42 AM 13/05/2003 +0200, you wrote:

>okokok - I've changed it, although for other reasons. However, your reason
>makes sense. :-)
>
>Each terminal in a retail environment has a terminal ID, and I need a
>seperate TXN seq number for each. So now I have a table with the Terminal
>ID and a TXN seq no for that terminal, and will increment them "manually"
>
>So now I have a stored proc that goes like this :
>
>CREATE PROCEDURE GETNEXTTXNSEQNO
>(
>TERMID VARCHAR(16)
>)
>RETURNS
>(
>RNEXTTXNSEQNO INTEGER
>)
>AS
>BEGIN
> FOR SELECT NEXTTXNSEQNO
> FROM
> TXNSEQNO
> WHERE
> TERMID = :TERMID
> INTO
> RNEXTTXNSEQNO
> DO
> BEGIN
> RNEXTTXNSEQNO = RNEXTTXNSEQNO + 1;
> UPDATE TXNSEQNO
> SET
> NEXTTXNSEQNO = :RNEXTTXNSEQNO
> WHERE
> TERMID = :TERMID;
> SUSPEND;
> END
>END

The problem of non-concurrency that exists for generators is replaced by
one of concurrency if you use this strategy, viz., How will you know that
another user didn't update TXNSEQNO? The differences between using a
generator and going this way are
a) that you can use dynamic SQL to reset the starting number. Horribly
dangerous unless you have exclusive control of TXNSEQNO for the whole
duration of the transaction.
b) You cannot guarantee that the number you work on is unique, since
another transaction may also be working with it on the same wrong
assumption that it's the only transaction able to read it.

You can set up a pessimistic lock on your number by "updating" it to its
current value at the start of the transaction. This guarantees that, if
you can get this lock, then no other transaction can. It also means that,
if another transaction already has this lock, then you can't get it.

If this pure sequence is really important, then there are things you can do
with a generator, that involve using events and triggers. You'll find a
paper on this technique at http://www.ibobjects.com/TechInfo.html (How to
maintain a pure sequence).

heLen