> In my metadata, I create a generator & a trigger. Now, before the db
> is to be used normally, I need to import x amount of records from
> the old Pdox database.
> As I do not know how many records will be imported, how can I
> specify the SET GENERATOR MY_GEN TO X, to correctly reflect the
> latest ID to be used, after the import?
> I'm assuming that if I don't, the generator will try to use '1', and
> theres a very good chance that will have been used already.

That is correct.

> Can I do an ALTER GENERATOR, or something?

SET GENERATOR MY_GEN TO 100000;

> I'm aware I could allow the generator to be involved during the
> import process, but as I'm trying to import detail records also, I'm
> worried that inconsistency will happen, do to not being able to
> retrieve the last generated value. (or is this possible)

As you have already determined, using the generator during import is
only of use when there are no relationships to maintain.

What you depends on the situation. Just keep in mind that generator
values are created outside the transaction context, which means that
other users can impact the generator value outside your own
processing. So...

If you are doing an effectively once-off import the easiest way to
manage is to either:

A. Simply offset the generator to a large value you know is out
of the way of what you are importing.

B. Do the import, then do a SELECT MAX(PK) FROM ... to discover
the largest used value and set the generator above that value.
(But only rely on this if you are the only user currently
connected.)

If you are trying to support a facility where users can import records
from some external source then life gets a little more complicated...

One way to manage this is to simply use totally different ranges of
values between the main database and the source database(s). Assuming
you are using IB6 or FB1 then you have generators supporting 64bit
integer values, and this gives you quite a lot of room to allocate
large ranges of numbers.

Another way is to have multiple key fields. Either as a combined
primary key, or as separate indexes. New PK values are generated
during import and you setup a stored procedure that uses the alternate
(import key) index to rematch all the imported records after each
import.


--
Geoff Worboys
Telesis Computing