Hi Jaume,

I just asked the same question last week on the firebird-java group but the
answers are not Java related and should work for you.

The basic answer is you have to get the generated key first then insert it
into your record.

To get the key use: SELECT gen_id(my_generator, 1) FROM RDB$DATABASE

To be sure that a value is alway inserted into your primary key define your
generator like so:

create trigger myTrigger for myTable
active before insert
as
begin
if (new.YourPrimaryKey is null) then
new.YourPrimaryKey = gen_id(YourGenerator, 1);
end

That will use the value you pass in an INSERT unless that is null then it
will use the generator to get a new value and put it into your primary key
field.

Another method is to use a stored procedure Ken Richard sent me the snippet
below that returns a key value after inserting. Note that generated numbers
in Firebird are Int64 instead of integers as in Interbase, so you may need
to adjust this.

CREATE PROCEDURE "TABLE_INSERT"
(
"V_FIELD1" INTEGER,
"V_FIELD2" VARCHAR(2000) CHARACTER SET WIN1252
)
RETURNS
(
"PK" INTEGER
)
AS
BEGIN
PK = gen_id(Table_no_gen, 1);
insert into Team ( PK_Table, Field1, Field2)
values ( :PK,:v_Field1,:v_Field2);
SUSPEND;
END

Hope this helps,

Rick

----- Original Message -----

> Hi everyone.
>
> I have got these tables: NEWS - NEWS_LANGUAGE - LANGUAGES, N-M
cardinality.
>
> I want to insert a row in table NEWS, then multiple rows in table
> NEWS_LANGUAGE using the primary key just created on NEWS as foreign key on
> NEWS_LANGUAGE.
>
> I have got a stored procedure, POST_NEWS, which first inserts in table
NEWS.
> A trigger on event "before insert" raises and, using a generator, creates
> an integer value which is used as primary key.
>
> Now I would like to know how to get that "just created" value without
having
> to launch a SELECT query after that INSERT.
>
> Finally, using that integer value, I have to launch multiple INSERT
> statements on table NEWS_LANGUAGE, which shouldn't give me any trouble if
a
> owe the primary key integer value.
>
> Thanks in advance.
>
> --
> Jaume Andreu Sabater Malondra
> jsabater@...
> Registered linux user #209072
>