How to recover the last record inserted in Firebird database?

Question

How to recover the last record inserted in Firebird database?

Navigation

#1 by (1 votes)

0

My Sales table has a Code field with auto-increment and I need to retrieve this code to insert into an associative table.

string sql = $"INSERT INTO VENDA(NRO_VENDA,DATA_VENDA,VALOR_TOTAL_PRODUTOS,VALOR_ACRESC‌IMO,VALOR_DESCONTO,V‌ALOR_TOTAL_VENDA,COD‌IGO_CLIENTE)VALUES({‌entidade.nroVenda},'‌{entidade.DataVenda}‌',{entidade.ValorTot‌alProdutos},{entidad‌e.ValorAcrescimo},{e‌ntidade.ValorDescont‌o},{entidade.ValorTo‌talVenda},{entidade.‌Cliente.Codigo})";
ConnectionFirebird.ExecComand(sql);

My table Venda has the Codigo field with auto-increment in which I want to retrieve this value.

Ref: I'm looking for something like Mysql's Last_Insert_Id

sql c# firebird

asked by anonymous 04.01.2017 / 13:15

1 answer

How to get the marker total in google maps? Avoid leakage of memory as a function of return

score 1 · Accepted Answer

If you are using Firebird 2, you already have RETURNING with the INSERT clause:

INSERT INTO t1 (...) values (...) RETURNING pk;

PK
===========
32

If it is a previous Firebird, and if you have columns of auto increment, you can recover with gen_id() :

select gen_id(GENERATOR_NAME, 0)
  from rdb$database;

However, it is not certain that you will have the value of the ID of the registry that you want, you will have the last ID. But if it's a concurrent bank, you'll have the most recently created ID. The only way to have the newly created registry ID is to retrieve the "next ID that will be created in sequence" and use it in INSERT :

declare variable id bigint;
select gen_id(GENERATOR_NAME, 1)
  from rdb$database
  into :id;

INSERT INTO t1 (pk, ...) values (:id, ...);