Subject Re: [ib-support] Re: generator of generators
Author Helen Borrie
At 09:58 AM 2/03/2003 +0000, you wrote:
> > No, it's not possible. You cannot perform any DDL in server code
> >I think your idea is really bad-bad-bad!!!
>frankly I see no other way to do it.
>Maybe I did not make the problem clear enough.
>I have N companies accessing a (common) database.
>Every time a record is inserted by company X, it must be uniquely
>As M users of company X can access the database at the same time, the
>only safe way to get a unique ID is to use a generator.
>Theoretically, a single generator could be used by all users of all
>companies, but this is not wanted.
>So every company must have its own generator.
>If you see another solution, please let me know.
> >How could you possibly know which generator generated a particular
>every company is identified by a number, say N.
> >Are you planning to read Gen_id (generatorname, 0) to count records?
>I am planning to read
>Gen_id (generator_N, 1)
>to get the number of the next record inserted by company N.
>I am eager to hear how you would solve the problem and why you judge
>so bad (3 times bad) my idea :)

Where do I begin?

First. Every number generated by a generator is unique. So, as long as
the correct company code is present on the record you can (a) get all of
the records belonging to company N and (b) select count (*) where
companyid='N' (if you must!). No record count will be guaranteed outside
of the current transaction context, however.

Second. Gen_id(generator_n, 1) actually generates the next number. The
number does not tell you how many records exist, however. Generated
numbers are lost when a transaction rolls back. So you can NOT use a
generator number as a record enumerator and expect it to be
accurate. (That is NOT what generators are designed for).

Third. If you have one generator for each company, you will have multiple
records in the table having the same ID. There is nothing special about a
generated number that tells you what generator it came from.

That's three bads.

How are these numbers to be used? Knowing that might shed more light on
why you think you need different generators for each company...