Migration from MySQL III. - AUTO_INCREMENT and LAST_INSERT_ID()

Source: http://www.dotnetfirebird.org/blog/2005/01/migration-from-mysql-iii-autoincrement.html


I mentioned already in Migration from MySQL I. that in Firebird you should use a generator instead of AUTO_INCREMENT:

CREATE GENERATOR GEN_MYTABLE_ID;

SET TERM ^ ;
CREATE PROCEDURE SP_MYTABLEINSERT (
MYTEXT VARCHAR(20))
AS
DECLARE
VARIABLE ID INTEGER;
BEGIN
ID = GEN_ID(GEN_MYTABLE_ID,1);
INSERT INTO MYTABLE (ID, MYTEXT) VALUES (:ID, :MYTEXT);
END^
SET TERM ; ^

In that example we used a stored procedure SP_MYTABLEINSERT to insert the data. If you need to get the number that was returned by the generator you can modify this stored procedure to return the generator value:

SET TERM ^ ;
CREATE PROCEDURE SP_MYTABLEINSERT (
MYTEXT VARCHAR(20))
RETURNS (
ID INTEGER)
AS
BEGIN
ID = GEN_ID(GEN_MYTABLE_ID,1);
INSERT INTO MYTABLE (ID, MYTEXT) VALUES (:ID, :MYTEXT);
SUSPEND;
END^
SET TERM ; ^

We increase the generator value by one and store the result in ID variable. After modifying the stored procedure header we now return the inserted id.