Recent

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

mobilevil

  • Jr. Member
  • **
  • Posts: 57
    • 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

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

GetMem

  • Hero Member
  • *****
  • Posts: 3744
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: 297
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

  • Jr. Member
  • **
  • Posts: 57
    • 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: 600
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

  • Jr. Member
  • **
  • Posts: 57
    • 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!

TJC

  • Newbie
  • Posts: 2
Re: reading generator value from firebird automatically?
« Reply #7 on: November 14, 2019, 08:51:37 pm »
Just came across this and though it is an old post I have seen some incorrect advice in other places
so thought I would post a clarification.

I have seen people doing the following:
  • Insert new row, with trigger setting the generated value
  • Use 'select GEN_ID(<gen_name>, 0) from RDB$DATABASE' to get the value just inserted value
This work fine only if it is a single user and single process application,

However, if there are multiple users/proceses inserting rows the generator value could easily
have been updated between the time you inserted and the time when you get the next value....
it is generator value is NOT transaction bound.

There are two ways of doing it correctly:
  • Get the next value yourself, instantly updating the generator using
    ''select GEN_ID(<gen_name>, 0) from RDB$DATABASE'', assigning the
    value returns to you id field, then doing the insert
  • Use the insert returning syntax, much more elegant IMHO:
    insert into the_table(col1, col2) values ('val1', 'val2') returning id;
    The trigger gets the new row id from the generator, inserts the row,
    and returns that value back to you in case you need for further work

tonyw

  • Full Member
  • ***
  • Posts: 173
    • MWA Software
Re: reading generator value from firebird automatically?
« Reply #8 on: November 15, 2019, 12:28:37 pm »
For the record, IBX allows both styles for using a generator. See IBX example applications for examples of use.

You can either use a generator client side, in which case IBX executes the 'select GEN_ID(<gen_name>, 1) from RDB$DATABASE' for you and puts the returned value into the inserted row before writing it to the database, or

IBX supports insert returning for server side use of a generator. This allows you to include the GEN_ID function in the INSERT SQL. When IBX executes an insert SQL query, it looks for any values returned and updates the fields identified in the returning clause with the returned values. All this happens within IBX without a need for additional code. The same is true of UPDATE.. RETURNING.

You can also process a DELETE..RETURNING clause - but you need to provide your own event handler to process the returned values.

devEric69

  • Sr. Member
  • ****
  • Posts: 314
Re: reading generator value from firebird automatically?
« Reply #9 on: November 15, 2019, 03:19:10 pm »
See the screenshot...
use: Linux 64 bits (Ubuntu 18.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

Zoran

  • Hero Member
  • *****
  • Posts: 1573
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: reading generator value from firebird automatically?
« Reply #10 on: November 15, 2019, 05:27:42 pm »
There are two ways of doing it correctly:
  • Get the next value yourself, instantly updating the generator using
    ''select GEN_ID(<gen_name>, 0) from RDB$DATABASE'', assigning the
    value returns to you id field, then doing the insert

You mean GEN_ID(<gen_name>, 1)
(notice 1 instead of 0)

 

TinyPortal © 2005-2018