Subject Re: [IBO] Absolute unique value/generator/SP problem
Author Paul Vinkenoog
Hi Kevin,

> I have an order entry system that requires an absolutely unique
> order number to be issued when sales people enter an order.
> In remembering all the posts over the years, using a generator is
> the way to go as it's my understanding it "operates" outside of
> the transaction thus assuring a unique value.

That's right. The only ways you can get duplicate values from a
generator are:

- Explicitly resetting the generator so that it covers the same
range of numbers again;
- Exhausting it, i.e. using it up, so that it will wrap around
(no warning or error message given here) and start over again.

> I am getting reports of duplicate order numbers being issued (a very
> bad thing).

I don't see anything in your code that causes this, but:

- Since sp_get_nextid doesn't return multiple rows, don't make it a
selectable stored procedure. Drop the SUSPEND. If you want to call
it with IBO, use a TIB_DSQL. But...

- Why call sp_get_nextid at all? In IBO, you can use GeneratorLinks
to connect a field to a generator: as soon as a new record is created,
IBO retrieves the value from the linked generator and puts it in the
field. This saves a lot of steps in your code, and reduces the chance
of errors. To do this, set the GeneratorLinks property of qryOH to:


- If GeneratorLinks is too automatic for you, you can also get the
value like this (in GetPermOrderNo):

qryOH.FieldByName('OrderNo').AsString :=
qryOH.Gen_ID('GEN_ORDERNO', 1);

Still no need for qryGetID or sp_get_nextid.

Your code will become much cleaner like this. If you still get
duplicate values, the cause will be easier to track down. But you

Paul Vinkenoog