More than one generator for one table [FB 2.5]

[inageib]

Hi,
I have a table (Firebird 2.5) that use right outer join for two tables and I update only one of them, however the table I update has need to have two generators one for ID and other for different purposes.

I set the two generators and every one has its own trigger (before insert) and test it in a db tool and both fields receive values successfully

the problem inside Delphi I have one property "keygenerator" for TIBCQery and when I test I get error that the second field must has a value (I set it to not null)

so how I use two generators in IBDAC ?

Thanks

[AndreyZ]

Hello,

To work with two generators for one table, you should use code like:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
  IBCQuery.SQL.Text := 'select * from test_gen'; // table test_gen has three fields: ID, ID1, and NAME
  IBCQuery.SQLInsert.Clear;
  IBCQuery.SQLInsert.Add('INSERT INTO test_gen (ID, ID1, NAME)');
  IBCQuery.SQLInsert.Add('VALUES (NEXT VALUE FOR GEN_TEST_GEN_ID, NEXT VALUE FOR GEN_TEST_GEN_ID1, :NAME)');
  IBCQuery.SQLInsert.Add('RETURNING ID, ID1');
  IBCQuery.Options.RequiredFields := False; // to avoid the "field must have a value" error
  IBCQuery.Open;
  IBCQuery.Append;
  IBCQuery.FieldByName('NAME').AsString := 'test';
  IBCQuery.Post;
end;

To put generated values to the IBCQuery component, you should also use the AfterUpdateExecute event in the following way:

Code: Select all

procedure TMainForm.IBCQueryAfterUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if stInsert in StatementTypes then begin
    Sender.FieldByName('ID').AsInteger := Params.ParamByName('RET_ID').AsInteger;
    Sender.FieldByName('ID1').AsInteger := Params.ParamByName('RET_ID1').AsInteger;
  end;
end;

[inageib]

as usual you give me the professional help I need

I just want to point that I was able to get the value when I disabled Options.RequiredFields without manually set insert sql or use afterupdate event

so do I must use insert sql like you did ?

Another question please:
when I use insert/append method and my form open , I want to display the new value of the field "ID1" inside the form while in insert mode - how I do that as it is currently appear only after I use post then refresh !!

also if possible to eliminate refresh would be great.

Thanks

[AndreyZ]

Because you are using triggers, you can get generators' values the way you do. But in this case, as you pointed, you should perform refreshing to obtain these values from the server.
Using the approach that I described above, you don't need to perform refreshing, because generators' values are returned as output parameters. Also in this case you shouldn't use triggers.
You can obtain a new generator's value from the server using second TIBCQuery component in the following way:

Code: Select all

procedure TMainForm.IBCQuery1AfterInsert(DataSet: TDataSet);
var
  newid1: integer;
begin
  IBCQuery2.SQL.Text := 'SELECT GEN_ID(GEN_TEST_GEN_ID1, 1) FROM RDB$DATABASE';
  IBCQuery2.Open;
  newid1 := IBCQuery2.Fields[0].AsInteger; // here you can display newid1 on your form
  IBCQuery2.Close;
end;

[inageib]

Thanks alot but if I use your sql does the generator value will be increased automatically ?

[AndreyZ]

Yes, the generator value will be increased automatically. The GEN_ID(GEN_TEST_GEN_ID1, 1) function increments a generator and returns its new value. Also, if you don't need to increase the generator value with step values higher than 1, you can use the NEXT VALUE FOR syntax in the following way:

Code: Select all

procedure TMainForm.IBCQuery1AfterInsert(DataSet: TDataSet);
var
  newid1: integer;
begin
  IBCQuery2.SQL.Text := 'SELECT NEXT VALUE FOR GEN_TEST_GEN_ID1 FROM RDB$DATABASE';
  IBCQuery2.Open;
  newid1 := IBCQuery2.Fields[0].AsInteger; // here you can display newid1 on your form
  IBCQuery2.Close;
end;

In this case the generator value will be increased automatically too.

[inageib]

Thanks alot I really appreciated all the help here and I bookmarked this page

one suggestion if possible to add the feature of using more than one generator into the component without writing code, it will save alot of time when there are 50+ tables - just an idea.

Thanks again

[AndreyZ]

Thank you for your suggestion. We will investigate the possibility of adding this functionality in the future.