At 09:47 AM 21/02/2003 +0100, you wrote:

>Hello
>
>I'm about to port my application from using mysql to firebird.
>Although Im going to do some testing first. In mysql there are a sql
>function called last_insert_id() the returns the last inserted primary key
>(if the column got the type AUTO_INCREMENT).
>
>Are there a similiar function in firebird? That is, auto_increment and
>last_insert_id()?

There is no auto-increment type. Declare the column as a NUMERIC(18,0)
type which is the 64-bit integer and use a generator to populate it. To
make the engine automatically insert the next value of the generator each
time a row is inserted, write a Before Insert trigger, e.g.
CREATE GENERATOR GEN_ATABLE;

commit;

SET TERM ^;
CREATE TRIGGER ATABLE_GETPK
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.PKCOLUMN IS NULL) THEN
NEW.PKCOLUMN = GEN_ID(GEN_ATABLE, 1);
END ^
commit ^
SET TERM ;^


You can call the GEN_ID() function to fetch the last value of the
generator, although, because Firebird is a transactional, multi-user RDBMS,
there is no common situation where it makes any sense to do that.

Here's an example of a query which will fetch that value into a one-column,
one-row output set:

SELECT GEN_ID(GEN_ATABLE, 0) FROM RDB$DATABASE;

Just *don't* use it in your application to do things like
calculate/extrapolate/assume that the just-generated number is the number
which was applied by your trigger. All client operations are isolated from
all others by the transaction they are in. The generation of triggers is
the *single* exception to this rule. Once a number has been generated, it
cannot be generated again. So, if you query the database for the most
recently generated number, there is no guarantee that it is the number just
generated for your transaction.

If you application needs to *know* what number is generated, then you need
to get the value *before* the new row is posted to the database. There are
two distinct ways to go about this...
1) Application writers often do a query similar to the one above, but using
an incrementor of 1 or greater, viz.

SELECT GEN_ID(GEN_ATABLE, 1) FROM RDB$DATABASE;
or
2) write a selectable stored procedure to do the same thing, viz.
SET TERM ^;
CREATE PROCEDURE GET_GEN_ATABLE
RETURNS (PK_VALUE NUMERIC(18,0)) AS
BEGIN
PK_VALUE = GEN_ID(GEN_ATABLE, 1);
SUSPEND;
END ^
COMMIT ^
SET TERM ;^

Then, your client query can just

SELECT PK_VALUE FROM GET_GEN_TABLE;

Helen
p.s. PLEASE do not post HTML messages in our lists. Thks.