Ann, thanks for the pointer I will keep a check on the rdb$generator_id
counter.

As for missing numbers using generators. I have put in code in my app
framework which will bring a number from the generator without
incrementing it. At the time of saving if the number is still valid it
will bring that number from the generator again without incrementing. In
case of conflict, where some one (or more) have taken up numbers since
it was shown to the user, the user is then allowed to use the latest
number available or do nothing (essentially cancel) to the record. If no
conflict call the generator once again allowing it to update. But this
is all client side code.

Thanks for the code Ann. Is it possible to have a table that I can make
it work like generators by doing the manipulations I am doing above. I
just have to repointer my code to use this table instead?

Kind regards
Bhavbhuti

On 29/04/2010 09:24 pm, Ann W. Harrison wrote:
>
> venussoftop wrote:
> > Thanks Ann, I will try gstat. Is there something stored in the
> database system tables that I can directly SQL-Pass-Through from my
> application?
>
> You don't really care how long it's been since a backup/restore.
> What you care about is the number of generators that have been
> issued, which will be the max value of rdb$generator_id in
> rdb$generators. If you check that weekly or monthly and if
> it's higher than 30,000, backup and restore the database.
>
> That doesn't solve the potential problem of lost numbers. If
> a transaction gets a value from a generator and rolls back or
> for some other reason doesn't use the value, that value is
> lost. There are lots of ways around the problem, some of which
> introduce the problem of numbers being assigned out of sequence.
> If your number sequence must be contiguous, and in order of
> assignment, and assigned to several concurrent users, generators
> will not work.
>
> This will work
>
> create table number_generator (
> thing_to_be_numbered varchar(50) not null,
> active_year integer not null,
> generated_number integer,
> primary key (thing_to_be_numbered, active_year));
>
> create procedure get_number
> (thing varchar(50), active_year integer)
> returns (generated_number integer)
> as begin
> select count(*) from number_generator
> where thing_to_be_numbered = :thing and
> active_year = :active_year
> into :generated_number;
>
> if (:generated_number = 0)
> then begin
> insert into number_generator
> (thing_to_be_numbered, active_year, generated_number)
> values (:thing, :active_year, 1);
> generated_number = 1;
> end
> else begin
> update number_generator
> set generated_number = generated_number + 1
> where thing_to_be_numbered = :thing and
> active_year = :active_year;
> select generated_number from number_generator
> where thing_to_be_numbered = :thing and
> active_year = :active_year
> into :generated_number;
> end
> suspend;
> end
>
> You'll get an error if two people try to run the procedure at
> the same time.
>
>

--


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://venussoftop.tripod.com

venussoftop@...
venussoftop@...
___________________________________________

Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made. Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest. We do not take any responsibility for data loss of any type and kind. Data safety remains the sole the responsibility of the users of our softwares.
___________________________________________

Internet email confidentiality:

This message may contain information that may be privileged or confidential. If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.