How to recover last record inserted in Firebird database?

Asked

Viewed 1,334 times

0

My Sale table has a Code field with auto-increment and need to recover this code to insert in 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 field Codigo with auto-increment in which I want to recover that value.

Ref: looking for something like Mysql Last_insert_id

c# sql firebird

  • Post the excerpt of this code!?

  • last record? select using MAX(CODE) since it is auto increment

  • I copied your comment to the body of the question.

  • 1

    @Totallyuncool, Using MAX() is not good practice. First, it does not guarantee that you will have the last record created, especially if it is a popular bank, following that if you have many records, it will be extremely time-consuming to return.

1 answer

1


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

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

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

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

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

However, it is not certain that you will have the value of the registration ID you want, you will have the last ID. But if it’s a competing 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 the INSERT:

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

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

Browser other questions tagged c# sql firebird

You are not signed in. Login or sign up in order to post.