Many thanks to Paul Vinkenoog <paul@...> and "Woody" <woody-
tmw@...> for helping me. GeneratorLinks works fine. It solves
my problem. Now there is another related thing come into sight.

The story
=========
I noticed that when I do an insert, IBO increments the generator
value on the server. If I cancel the insert, the increment of
generator value does not cancelled, so I end up with a skipped number
each time I cancel.

It looks like the increment of the generator value happens outside my
transaction, since I also can't undo the increment with a rollback.

I have a need for an uninterrupted autonumbering sequence. This means
I have to negate the increment of the generator each time I perform a
cancel.

A proposed work-around
======================
After some experiments, I come up with this :

1. Create a stored procedure to decrease the generator value. It goes
like this:

CREATE PROCEDURE P_DEC_GEN
AS
declare variable GEN_VAL Integer;
declare variable MY_STATEMENT Char(100);
begin
/*
DUMMY is the table of concern,
ID is the autoincrement field of concern,
G_DUMMY_ID is the generator of concern.
*/
SELECT MAX(ID) FROM DUMMY INTO :GEN_VAL;

/*
I wonder why SET GENERATOR name TO int;
do not work inside a stored procedure.
*/
MY_STATEMENT = 'SET GENERATOR G_DUMMY_ID TO '|| CAST (:GEN_VAL AS
CHARACTER);
EXECUTE STATEMENT :MY_STATEMENT;
end

2. Put an IB_DSQL on the form, which has this sql :

EXECUTE PROCEDURE P_DEC_GEN;

3. Write an event handler for IB_Query's AfterCancel event to execute
the stored procedure via IB_DSQL :

procedure TForm1.IB_Query1AfterCancel(IB_Dataset: TIB_Dataset);
begin
IB_DSQL1.Execute;
end;


The work-around above seems to work for now.


The problem
===========
I am not confident with my own work-around above :) For example, what
will happen when users concurrently access the same table? What about
the processing cost of the SELECT MAX(ID) statement if the table has
many records?

Could someone please show me how to do this safely, simpler and more
efficient? Any tips & tricks would be really appreciated.


Thank you very much.

JRP