Orhan TURHAN wrote:

> table1
> id
> -----
> 100
>
> table2
> id
> -----
> 23
>
>
> ...
> ..
>
> Generators
> gen_table1_id = 0
> gen_table2_id = 0
>
> i want to set all generator values from table id's.
>
> thanx.
>
>

Look at this message:



-------- 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