From: "Ionut Ichim" <ionut_ykm@...>

> I've created a table (table1) with IDcod as pk numeric(18,0),then a
> generator
> CREATE GENERATOR GEN_N;
> SET GENERATOR GEN_N TO 1;
> I've inserted 3 records for IDcod values 5,6,7.
> With IBExpert Personal,in a script I put this :
> SELECT gen_id(gen_N,1) FROM table1;
> Result :
> 2 3 4
> run again
> 5 6 7
> Why generator creates such result, for each record a value generated.
> Where is the value i look for ?
> Wouldn't it be slow for a big database ?
> Why generator creates values that can duplicate pk,or I have to use
> generator from the start ?

You are mixing your apples and coconuts. :)

A generator is a detached counter which you can use to generator primary
keys for one or more tables. However, there is no way to "connect" it with a
table directly. It is done through client side code or triggers. You haven't
mentioned any of that, nor did you mention how you got the key values when
you inserted data into the table.

Every time you execute the statement gen_id(generator_name, 1) you are
telling the generator to increment and return the current value. But it has
no way of knowing "where" you use that value. That's up to you. In the
select statement above, the gen_id command is executed for each record in
TABLE1. Since you inserted 3 records, it returns 3 results, each one an
increment higher than the last.

Some components allow you to specify key fields and their associated
generators that you want to use when inserting new records. They handle all
the background stuff of incrementing the generator and getting the value and
then putting it in the key field of the new record. If you aren't using
components that do that, you need to control it yourself.

Method 1: Call the gen_id command to get the next increment when you insert
a new record using a separate query. Use the resultant number as the value
for the key field by assigning it in code.

Method 2: Create a BeforeInsert trigger in the database on that table. Check
to see if the key field is null and if so, assign it the next generator
value using gen_id.

Method 2 means that you won't have access to the newly inserted key field's
value until you commit the record and refresh it.

HTH
Woody (TMW)