Muthu Annamalai wrote:

> Is there a way to set all generators to a value, for example i use the
> following code to set a single table generator
>
> SELECT MAX(ID) FROM ADDRESS INTO :ID;
> ID = GEN_ID (GEN_ADDRESS_ID, ID - GEN_ID(GEN_ADDRESS_ID,0));
>
> I want to scan all tables in the database and set all generators to the
> table max value.All my generators are named as GEN_TABLENAME_ID
>
> Thanks!
>
> Muthu Annamalai
>

Take a look at this piece of code...


-------- Original Message --------
Subject: [firebird-support] EXECUTE BLOCK - Testing new FB 2.0 features
Date: Wed, 22 Nov 2006 01:07:05 -0300
From: Alexandre Benson Smith <iblist@...>
Reply-To: firebird-support@yahoogroups.com
To: firebird-support@yahoogroups.com



Folks,

I have a database full of information and I'd like to empty some tables,
others I will just delete part of the data and so on.

After I did the clean-up I want to reset the generators values to avoid
starting it at a high value for an empty or with few records table.

To achive this I did a simple EXECUTE BLOCK code

execute block returns (
TableName varchar(50),
GeneratorName varchar(50),
GeneratorValue integer,
MaxID integer,
PKName varchar(50)) as
begin
for
/* get the table name and the generator name for that table*/
select
t.rdb$Relation_Name, 'G_' || t.rdb$Relation_Name
from
rdb$Relations T
where
rdb$View_BLR is null and
rdb$System_Flag = 0 and
exists (select 1 from rdb$Generators g where
g.rdb$Generator_Name = 'G_' || t.rdb$Relation_Name)
into
:TableName, :GeneratorName
do begin
PKName = '*';

/* Get The PK Field Name */
select
s.rdb$Field_Name
from
rdb$Relation_Constraints pk join
rdb$Indices i on (i.rdb$Index_Name = pk.rdb$Index_Name) join
rdb$Index_Segments s on (s.rdb$Index_Name = i.rdb$Index_Name)
where
pk.rdb$Constraint_Type = 'PRIMARY KEY' and
pk.rdb$Relation_Name = :TableName
into
:PKName;

if (PKName <> '*') then begin
/* Get the Generator actual value, the maximum ID fro the table
and reset the generator */
execute statement 'select cast(gen_id(' || GeneratorName || ',
0) as integer) from rdb$Database' into :GeneratorValue;
execute statement 'select Coalesce(max(' || PKName || '), 0)
from ' || TableName into :MaxID;
execute statement 'set generator ' || GeneratorName || ' to ' ||
MaxID;
end

suspend;
end
end;

This of course will just work because the name pattern I have adopted,
but I think this could be a usefull piece of code that could serve as a
skeleton for someone with a similar task.

The pattern I adopt is:
Table Name Generator Name PK Field
Customer G_Customer CustomerID
Invoice G_Invoice InvoiceID

I could have derived the PK Field from the Table Name appending 'ID' at
the end, but I have 2 or 3 tables that break this rule, so I had to get
the PK Name trough RD$Index_Segments (wich is of course a better/safer
approach).


Known limitations:
Will only work for PK's composed of a unique integer field (a typical
surrogate key)

I could have created an empty database from scratch from the source
metadata, then extract the data and run the insert script, but I liked
to make a proof of concept of the EXECUTE BLOCK feature.

see you !



--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br