--- In firebird-support@yahoogroups.com, "rebel_rob_98"
<rebel_rob_98@y...> wrote:

> Hello All,
>
> This is probably an easy questions, so please be forgiving :)
>
> I have a stored procedure that takes in info, inserts it into the DB
> and I want to return the ID of the record that was just inserted.
> Here is a sample chunk of code:
>
> SELECT gen_id(TMP_SOME_GEN, 1) as Result
> FROM RDB$Database
> INTO :tcbid;
> INSERT INTO tmp_some_table
> (field1,
> field2,...
>
> This code works and does not throw an error however the ID (tcbid)
> does not match that of the one just inserted (it's one digit

behind).

> So, my solution was this:
>
> ...
> decalre variable finalid integer;
> ...
> SELECT gen_id(TMP_SOME_GEN, 0) as Result
> FROM RDB$Database
> INTO :tcbid;
>
> finalid = tcbid + 1;
>
> INSERT INTO tmp_some_table
> (field1,
> field2,...
>
> The method listed above gets me the correct ID of the record being
> inserted, however I am not sure this is the best course of action.
>

Robert,

The method above risks inserting duplicates unless you change all
inserts into that method (which is backwards, so dont).

Your original SP was the better way to do things

SELECT gen_id(TMP_SOME_GEN, 1)
FROM RDB$Database
INTO :tcbid;

INSERT INTO tmp_some_table
(field1,
field2,...

(you don't need the AS here because it is ignored inside PSQL anyway).

Check the table for before insert triggers. My guess is there is one
that is assigning a new ID value. To fix this trigger, change the
logic to read something like

IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(TMP_SOME_GEN,1);
END

This allows the trigger to effectively emmulate an autoinc field, yet
still use the ID that you generate.

Adam