Apologies, Roman, I'm answering this even though it's
off-topic. This person is posting generator questions all over the place.

At 07:26 PM 15/06/2006, you wrote:

>Hi,
>
>I am using generator with my Firebird database.

But you don't understand how they work.

> 1. When there are no records in the database and i am trying to get the
>value of the generator, it doesnot return any thing to me.

The first thing to understand is that generators are not tied to any
table that uses them. Indeed, some people use a single generator to
generate the PKs for all of the tables in their database.

>2. The second one is that I need to get the incremented value of the
>generator whenever a new record is inserted in to the database.

This is not necessarily a good idea. If two transactions insert a
record and trigger the generator one after the other, which one
belongs to your record? You have no way to know.

>
> When I insert the first record into the database and
>trying to get the generator by using select gen_id(testGen,1) from
>table1, it returns the value 1.

That is because table1 contains 1 record and the PK has the first
number from the generator.

> When I insert one more record in to the same table, the same query
>returns two values 2 and 3

That is because the query you are using is firing once for every row
in the table. When you have 500,000 rows, you will get 500,000 new
values generated.

>If I insert one more record it returns me 3 values 4, 5 and 6.
>
>My requirement is to get a single value whenever a new recor is inserted
>and that value should be the next positive integer of the previous
>generator value.

You are going about things in a totally wrong way.

This query gets the next number from the generator:

select gen_id(MyGenerator, 1) from RDB$Database

As has been explained to you elsewhere, we use RDB$Database because
it has exactly one row. (You could create your own table for this
purpose, if you like, and just insert one row into it.)

create table DUAL (
data char);
COMMIT;
insert into DUAL values('X');
COMMIT;

select gen_id (MyGenerator, 1) from DUAL will return the next value
of the generator.

select gen_id (MyGenerator,0) from DUAL will return the last-generated number.

But you must not use this to deduce the value that *you* just
got. As mentioned before, you have no way to know whether the
last-generated number was the one that was used for your new record.

>
> Is this possible in Firebird.

In Firebird 2.0, there is syntax to return inserted values:

INSERT INTO ... VALUES (...) [RETURNING <column_list> [INTO <variable_list>]]

In Firebird 1.5 you have two choices:

1) Fetch the next generator value *before* you pass your INSERT
request and include the value in the statement (recommended)
or
2) Write a stored procedure for your inserts and make it return the
value in a RETURNS argument

For 1) make sure that your insert trigger only fires the generator if
the PK is null:

create trigger bi_table1 for table1
active before insert position 0
as
begin
if (new.PK is null) then
new.PK = gen_id(MyGenerator, 1);
end

Helen