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.