* * *

Author Topic: reading generator value from firebird automatically?  (Read 623 times)

mobilevil

  • New member
  • *
  • Posts: 27
    • http://www.kachun.com
reading generator value from firebird automatically?
« on: August 20, 2017, 07:47:10 pm »
Lazarus 1.8 RC4, Zeos 7.2 RC1 and firebird 3.
table with PK generated by genereator.

Imagine I am adding records to a simple grid based table editor.

10+ years ago, with delphi and interbase I had to query the generator separately and fill in the primary key by code before inserting, or use a server side trigger to fill in the primary key for me, but the client application will lose track of the new record because the app doesn't know the value for the primary key. Now that firebird 2 can return the new primary key by the "returning" clause, is there any way to have the query component read back the primary key from returning clause?

I see people are talking about setting the  AutoGenerateValue property in the field object  to arAutoInc, but I don't even see the text in zeos nor fcl-db, seems the discuss is pretty outdated.





hrayon

  • Jr. Member
  • **
  • Posts: 68
Re: reading generator value from firebird automatically?
« Reply #1 on: September 01, 2017, 01:04:18 pm »

GetMem

  • Hero Member
  • *****
  • Posts: 2253
Re: reading generator value from firebird automatically?
« Reply #2 on: September 01, 2017, 02:23:05 pm »
@mobilevil
I never used Zeos before, but it looks like the Sequence/SequenceField property is what are you after(TZQuery). If not, you can always manually query the last value of the generator like this:
Code: Pascal  [Select]
  1. function GetIDFromGenerator(const AGen_Name: string; const AIncrease: Boolean = True): Integer;
  2. begin
  3.   with qGen do
  4.   begin
  5.     if Active then
  6.       Close;
  7.     SQL.Clear;
  8.     if AIncrease then
  9.       SQL.Add('select gen_id(' + AGen_Name + ', 1) from rdb$database')
  10.     else
  11.       SQL.Add('select gen_id(' + AGen_Name + ', 0) from rdb$database');
  12.     Open;
  13.     First;
  14.     Result := FieldByName('GEN_ID').AsInteger;
  15.   end;
  16. end;  
« Last Edit: September 01, 2017, 02:26:25 pm by GetMem »

goodname

  • Sr. Member
  • ****
  • Posts: 288
Re: reading generator value from firebird automatically?
« Reply #3 on: September 02, 2017, 03:48:39 pm »
Have not used firebird but the following works with other database engines. Have not checked the syntax but this should give you an idea of the approach.

Code: Pascal  [Select]
  1. SQLQuery.SQL.Text:='insert into journal default values  returning entry_id';
  2. SQLQuery.Open;
Read the return value as you would for any other select dataset.
Code: Pascal  [Select]
  1. SQLQuery1.SQL.Text:='insert into tbl1 default values ';
  2. SQLQuery2.SQL.Text:='insert into tbl2 (foreignKey) values (gen_id('+ AGen_Name  +', 1 ) ) ';
  3. SQLQuery1.ExecSQL;
  4. SQLQuery2.ExecSQL;

This way you do not need the returning value.

mobilevil

  • New member
  • *
  • Posts: 27
    • http://www.kachun.com
Re: reading generator value from firebird automatically?
« Reply #4 on: September 03, 2017, 09:34:38 am »
Thanks guys. I didn't know IBX is available in lazarus.
I am on a low budget project so I have to be lazy and avoid code as much as possible, yet I am curious on things.

LacaK

  • Hero Member
  • *****
  • Posts: 543
Re: reading generator value from firebird automatically?
« Reply #5 on: September 06, 2017, 12:44:21 pm »
fcl-db/sqlDB supports it. See https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.refreshsql.html
You are not required to write RefreshSQL it will be constructed autommatically ...

mobilevil

  • New member
  • *
  • Posts: 27
    • http://www.kachun.com
Re: reading generator value from firebird automatically?
« Reply #6 on: September 06, 2017, 04:28:44 pm »
thanks for the info!

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus