Hello Olaf,

> I need the last ID from a table. Now I can get this with select max(id)
> from
> table into :maxlnr. I thought, it is a better way to read the last
> generator-id with maxlnr = gen_id(gen_t_dt_id,0); This results a not
> indexed
> read of my table.
>
>
>
> Now I have read the following in firebird faqs: Unless you know very well
> what you are doing, using GEN_ID() with step values lower than 1 may
> compromise your data's integrity.
>
>
>
> What is the best way to get the last value?

Generators are incremented -outside- transaction control. That means if
two transactions insert a new item into your table, the GEN_ID(..., 1)
function will fire twice and your generator is incremented twice. This also
means that if you have to increment and READ the value of the generator,
it might already have been incremented by the second transaction between
your first insert and your read to get the current value.

The best way of doing things depends on your Firebird version, for example,
the following works in all Firebird versions:

Method 1
-----
In your client application, use:

select gen_id(mygenerator, 1) as new_id from rdb$database

insert into ... values (:thenewid, ...)
insert into childtable values (:thenewparentid)

etc...

So, basically: get the value yourself and use it for new data.

If you like to avoid it sometimes because you don't need the new value,
create a trigger that says:

IF ( (NEW.IDCOLUMN IS NULL) OR (NEW.IDCOLUMN = 0) )
THEN NEW.IDCOLUMN = GEN_ID(mygenerator, 1);


Method 2
-----
New versions of Firebird support INSERT INTO ... RETURNING.

So, keep the trigger and use:

INSERT INTO ... VALUES (all_other_columns_except_id_column)
RETURNING IDCOLUMN INTO :mynewid

Here, you get the newly created ID value from IDCOLUMN into client
side parameter "mynewid".



Hope this helps.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!