Command to get identity of newly inserted record in Interbase 2007

Question

In Interbase (I'm using 2007, I don't know if it matters) is there a command to get the identity of a newly-inserted record, similar to SCOPE_IDENTITY() in SQL Server?

Answer 1

No, InterBase doesn't really have an identity feature.

What InterBase has, instead, is a generator feature. Generators are kind of like identity, but they are logically separated from the primary key column. A generator, in other words, will give you a guaranteed unique value, but what you do with that value is up to you.

You could use that value as the primary key values for a single table, or for multiple tables. But actually assigning the primary key value is something you must do yourself.

In addition to not having a feature like SCOPE_IDENTITY, InterBase does not have any kind of feature to return values from an INSERT statement. So not only can you not get a generated primary key value back from an INSERT statement, you also cannot get any other values, such as values set by a trigger.

Workarounds

One possible workaround for this is to generate the primary key value in advance. So you could do something like the following (I'm going to use InterBase stored procedure syntax for this example, since I don't know what programming language you are using, but you can do the same thing in any programming language):

DECLARE VARIABLE ID INTEGER;
BEGIN
    ID = SELECT GEN_ID(MY_GENERATOR, 1) FROM RDB$DATABASE;
    INSERT INTO MY_TABLE (ID, DESCRIPTION) VALUES (:ID, "Foo");

RDB$DATABASE is a system table which has only one record. Knowing the value of ID, you can return it from the proc.

A second workaround is to SELECT the record using an alternate key and read the generated ID that way.