Firebird: how read autoincrement value?
lazarus / 16-Databases

----------------------------------------------------------------------
Borneq · 2015-08-18 13:43

I have tables BOOKS,AUTHORS and BOOKS_AUTHORS
select a.*
from books_authors ba
  join authors a on ba.author=a.id
where ba.book=:books;

where parameter :books is newly crated BOOK.ID which has autoincrement
How read last BOOK.ID?

----------------------------------------------------------------------
balazsszekely · 2015-08-18 14:08

Firebird use generators for auto increment. You should do something
like this:

Code: [Select]function GetIDFromGenerator(Gen_Name: string;  const
Increase: boolean = True): integer;
begin
  with qGen do
  begin
    sql.Clear;
    if Increase then
      sql.Add('select gen_id(' + Gen_Name + ', 1) from rdb$database')
    else
      sql.Add('select gen_id(' + Gen_Name + ', 0) from rdb$database');
    Open;
    First;
    Result := FieldByName('GEN_ID').AsInteger;
  end;
end;
Where qGen is a TSQLQuery. The param increase is self explanatory.

----------------------------------------------------------------------
Groffy · 2015-08-18 14:20

If I understood your correctly, you would like to execute the select
statement for the last inserted book Id?

Try this one :

select a.*
from books_authors ba
join authors a on ba.author=a.id
where ba.book = (select max(ID) from books)

The last inserted Id is always the Id with the highest value, in that
case you don't need to know the value


Best regards

----------------------------------------------------------------------
balazsszekely · 2015-08-18 14:23

Quote@Groffy
The last inserted Id is always the Id with the highest valueYes, it is
the highest value for the current transaction. If multiple users are
connected, your method might fail.

----------------------------------------------------------------------
Groffy · 2015-08-18 14:40

Quote from: GetMem on August 18, 2015, 04:23:08 pmYes, it is the
highest value for the current transaction. If multiple users are
connected, your method might fail.


@GetMem

Hm, ok - you are right that might happen in a multiuser environment.
So you suggest to inquire the next generator value for using it to
insert a new book record?

----------------------------------------------------------------------
balazsszekely · 2015-08-18 16:53

"select max(ID) from books" will work perfectly fine in most cases,
but if you want to be absolutely sure, use the GetIDFromGenerator
function.

Code: [Select]function GetIDFromGenerator(Gen_Name: string;  const
Increase: Boolean = True): Integer;
begin
  Result := -1;
  with qGen do
  begin
    sql.Clear;
    if Increase then
      sql.Add('select gen_id(' + Gen_Name + ', 1) from rdb$database')
    else
      sql.Add('select gen_id(' + Gen_Name + ', 0) from rdb$database');
    Open;
    First;
    Result := FieldByName('GEN_ID').AsInteger;
  end;
end;     

//...

var
  New_BookID: Integer;
begin
  New_BookID := GetIDFromGenerator('BOOKS_GEN', True);
  if New_BookID <> -1 then
  begin
    qBooks.ParamByName('PID').AsInteger := New_BookID;
    qBooks.ParamByName('PNAME').AsString := 'aaaaa'
    qBooks.ExecSQL;
  end;
end;                      

where qBooks.SQL looks like this:
Code: [Select]insert into BOOKS(ID, NAME)
values(:PID, :PNAME)

----------------------------------------------------------------------
hallenberger · 2015-08-19 05:34

Hello!

If you want the retourning ID then this is possible:

Code: [Select]    Sql := 'insert into ' + Fbtabelle + ' (autor, bild)'
+
      ' values(:autor, :bild) returning id;';

where id is generated by a generator in firebird.

regards

----------------------------------------------------------------------
rvk · 2015-08-19 08:03

Quote from: GetMem on August 18, 2015, 06:53:21 pm"select max(ID) from
books" will work perfectly fine in most cases, but if you want to be
absolutely sure, use the GetIDFromGenerator function.
That's all good and well but might still return an ID which is not
from a record you just inserted. It does answer the question exactly
but I think OP wants the ID from the record which the user just
inserted. When in multi-user environment you could get a higher ID if
someone inserted a record after you. And if you wanted to use this ID
to insert detail records is the wrong way to do it.

You should first get a new ID from the generator (via
GetIDFromGenerator) and set it manually when you insert your master
record. Subsequently youcan use that ID to insert detail records.

I've never used the Returning clause like hallenberger described but
that might also be a way to go (retrieving the ID when inserting the
master-record).

----------------------------------------------------------------------
hallenberger · 2015-08-19 08:26

Hello rvk.

The good thing is: the generated id is unique (no problem with
transactions).

Here the complete example:

Code: [Select]  Try
    Sql := 'insert into ' + Fbtabelle + ' (autor, bild)' +
      ' values(:autor, :bild) returning id;';
    dm.tFSBild.StartTransaction;
    dm.qFSBild.sql.Text := sql;
    dm.qFSBild.Params.ByNameAsString['autor'] := UTF8Decode(Autor);
    dm.qFSBild.ParamsSetBlob('bild', Fdatei);
    dm.qFSBild.Execute;
    ID := dm.qFSBild.fields.AsInt64[0];
    dm.tFSBild.Commit;
  Except
    on E: Exception Do
    Begin
      dm.tFSBild.RollBack;
      ShowMessage(E.Message);
    End;
  End;

----------------------------------------------------------------------
balazsszekely · 2015-08-19 09:13

Quote@rvk
That's all good and well but might still return an ID which is not
from a record you just inserted. It does answer the question exactly
but I think OP wants the ID from the record which the user just
inserted. When in multi-user environment you could get a higher ID if
someone inserted a record after you. And if you wanted to use this ID
to insert detail records is the wrong way to do it.

You should first get a new ID from the generator (via
GetIDFromGenerator) and set it manually when you insert your master
record. Subsequently youcan use that ID to insert detail records.
Please scroll down my example(last post). 

PS: hallenberger solution is also perfectly fine. I let the OP choose
whatever method he/she likes better.

----------------------------------------------------------------------
rvk · 2015-08-19 09:20

Quote from: GetMem on August 19, 2015, 11:13:46 amQuote@rvk
That's all good and well but might still return an ID which is not
from a record you just inserted. It does answer the question exactly
but I think OP wants the ID from the record which the user just
inserted. When in multi-user environment you could get a higher ID if
someone inserted a record after you. And if you wanted to use this ID
to insert detail records is the wrong way to do it.

You should first get a new ID from the generator (via
GetIDFromGenerator) and set it manually when you insert your master
record. Subsequently youcan use that ID to insert detail records.
Please scroll down my example(last post). 

PS: hallenberger solution is also perfectly fine. I let the OP choose
whatever method he likes better.
Woops   Sorry. Yeah, I didn't sroll enough.

(It's not convenient that the code-tags don't display the entire code
but just a small part. Showing complete code would be much clearer.)