I loaded the execute block in IB Expert Script Executive and made
modification. The script is executing and I committed. But I am not
getting any return from the script as well my generator values are
not changed.

My Generator Name syntax is GEN_TABLENAME_ID
My primary id for all table syntax is ID

Here is my modified script

--------------------------
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, 'GEN_' || T.RDB$RELATION_NAME || '_ID'
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 = 'GEN_' || T.RDB$RELATION_NAME || '_ID')
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 FOR 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;

-------------------------

Can you correct my mistake

Thanks!

Muthu Annamalai

--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@...> wrote:

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