Hi JRP,

> 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.

That's right, and it's meant to be that way. It's the only way to
guarantee that every number is unique.

> 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.

That's dangerous (like you indicate yourself in your message), because
if there are multiple connections the generator may already have been
incremented several times between the moment you get the value and the
moment you decide to turn it back.

[ But if you still want to do it, the SQL is simple:
MyDummyVar = gen_id( MyGenerator, -1 ); ]

> SELECT MAX(ID) FROM DUMMY INTO :GEN_VAL;

This is unsafe because IDs may have been assigned that are not (yet)
visible to your transaction. To get the current value, call
gen_id( MyGenerator, 0 )
...but that's still not safe, because by the time you set the
generator back it may have been called again by other clients.

> I wonder why SET GENERATOR name TO int;
> do not work inside a stored procedure.

Because it's a DDL (Data Definition Language) statement. In SPs you
may only execute DML (Manipulation).

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

If you *really* must have an uninterrupted sequence, I'm afraid
there's no simple way. But it can be done; there's an article on
this subject here:

http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP


Greetings,
Paul Vinkenoog