generator return value not set

[inageib]

Hi,
I have a Firebird database has table with two auto generated fields. the first one is the PK and second is only a unique number. I set at the dataset level the property "keyGenerator" to the PK generator and it work fine after insert the new value was gnerated.

But for the second field (called No in the screenshot) nothing is generated. I test at database level using IBExpert and the value generate after insert normally but in dataset level it was not and stay null. each filed has its own "before insert" trigger to call the field generator.



Please advise for possible way(s) to solve this

Thanks

[AndreyZ]

Hello,

If you are using a trigger for the No field, you should refresh the new record after its inserting. For example, you can set the TIBCQuery.RefreshOptions property to roAfterInsert. Here is code example:

Code: Select all

IBCQuery1.RefreshOptions := [roAfterInsert];

If it does not help, please specify the script to create your table and all its triggers. After this, I will be able to give you a more valid answer.

[inageib]

yes this work I already use it but but by calling the refresh method of the table on after insert event. is there any other solution than use refresh for performance ?

Thanks

[inageib]

I read in previous post that I can use this code to get generator value without refreshing.

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('ID2').AsInteger := Params.ParamByName('RET_ID1').AsInteger;
  end;
end;

but when I tried I get error when I try to post changes

Parameter 'RET_ID2' not found.

Also after insert the dblabel does not display "ID2" value. please advise

[AndreyZ]

You can get new values of fields which were set in a trigger using the RETURNING clause. Here is a code example:

Code: Select all

IBCQuery1.Options.RequiredFields := False;
IBCQuery1.Options.ReturnParams := True;
IBCQuery1.SQL.Text := 'select * from dept';
IBCQuery1.SQLInsert.Text := 'insert into dept(dname, loc) values(:dname, :loc) returning deptno';
IBCQuery1.Open;

, where dept is the following table:

Code: Select all

CREATE TABLE DEPT (
    DEPTNO  INTEGER PRIMARY KEY,
    DNAME   VARCHAR(14),
    LOC     VARCHAR(13)
);

, and it has the following BEFORE INSERT triggger:

Code: Select all

CREATE OR ALTER TRIGGER TR_BEF_INS_DEPT FOR DEPT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if ((new.deptno is null) or (new.deptno = 0)) then
  begin
    new.deptno = gen_id( gen_dept_deptno, 1 );
  end
end

Using this code, the value for the deptno field will be automatically filled by IBDAC after inserting a new record. For more information about the RETURNING clause, please refer to http://www.firebirdsql.org/refdocs/lang ... -returning

[inageib]

Many Thanks.
Just for sake of learning is there any other way using the code for example ?

[AndreyZ]

It is not clear what other way you mean, please explain it in details. What you mean by "other way using the code for example" ?

[inageib]

I meant using Pascal code like the code I posted in previous post here (4th post) that uses event AfterUpdateExecute

[AndreyZ]

You do not need to use such code. Parameters with names starting with 'RET_' are created for fields for which the RETURNING clause is specified. Using the approach I wrote in my previous post, IBDAC does all the job for you, automatically filling fields for which the RETURNING clause is specified.
Additionally, if you want to execute the INSERT statement explicitly and return field values from the server, you can use the following code:

Code: Select all

IBCQuery1.SQL.Text := 'insert into dept(dname, loc) values(:dname, :loc) returning deptno';
IBCQuery1.Execute;
ShowMessage(IntToStr(IBCQuery1.ParamByName('ret_deptno').AsInteger));

[inageib]

Many thanks for the great help. I confirm it is working

I have two questions:
I use db label bind to the return parameter and after call to insert method nothing is display on the label. what I should do to make the return value appear ?

Can I execute this SQL: Select max/sum.... with TIBCSQL component and get returned value ?

Thanks

[AndreyZ]

Please note that all visual database components work with fields, not with parameters. You can bind TDBText to show any field value of the current record using the code similar to the following:

Code: Select all

IBCQuery1.Options.RequiredFields := False;
IBCQuery1.Options.ReturnParams := True;
IBCQuery1.SQL.Text := 'select * from dept';
IBCQuery1.SQLInsert.Text := 'insert into dept(dname, loc) values(:dname, :loc) returning deptno';
IBCDataSource1.DataSet := IBCQuery1;
DBText1.DataSource := IBCDataSource1;
DBText1.DataField := 'deptno';
IBCQuery1.Open;

You can use the TIBCSQL component to execute the 'SELECT MAX' and 'SELECT SUM' statements, but not directly. These statements must be put to stored procedures, for example like this:

Code: Select all

SET TERM ^ ;

create or alter procedure SELMAXDEPTNO
returns (
    MAXDEPTNO integer)
as
begin
  select max(deptno) from dept into :maxdeptno;
end^

SET TERM ; ^

And here is a code example that uses this stored procedure in TIBCSQL:

Code: Select all

IBCSQL1.CreateProcCall('SELMAXDEPTNO');
IBCSQL1.Execute;
ShowMessage(IntToStr(IBCSQL1.ParamByName('maxdeptno').AsInteger));

It is easier to execute the 'SELECT MAX' and 'SELECT SUM' statements in the TIBCQuery component, like this:

Code: Select all

IBCQuery1.SQL.Text := 'select max(deptno) from dept';
IBCQuery1.Open;
ShowMessage(IntToStr(IBCQuery1.Fields[0].AsInteger));

[inageib]

Many Thanks appreciate your help

but for DBText1 I already use that code but it does not returning the deptno after call to insert. I need to show the returned value in DBText1 once insert method is called.

About using TIBCSQL I am asking because I think it is more optimized than TIBCQuery ? if so then I hope in IBDac future release TIBCSQL support return value from select functions (max, sum, etc...)

[AndreyZ]

I cannot reproduce the problem. Here is code example that shows the correct usage of the TDBText component:

Code: Select all

IBCQuery1.Options.RequiredFields := False;
IBCQuery1.Options.ReturnParams := True;
IBCQuery1.SQL.Text := 'select * from dept';
IBCQuery1.SQLInsert.Text := 'insert into dept(dname, loc) values(:dname, :loc) returning deptno';
IBCDataSource1.DataSet := IBCQuery1;
DBText1.DataSource := IBCDataSource1;
DBText1.DataField := 'deptno';
IBCQuery1.Open;
IBCQuery1.Insert;
IBCQuery1.FieldByName('dname').AsString := 'test';
IBCQuery1.FieldByName('loc').AsString := 'test';
IBCQuery1.Post;
ShowMessage(DBText1.Caption);

Please change this code to make it demonstrate the problem.

The TIBCSQL component is intended for executing SQL statements and calling stored procedures on the server. It cannot be used to retrieve rows from the database. The 'SELECT MAX' and 'SELECT SUM' statements retrieve rows from the database, that is why you cannot use them in TIBCSQL directly. The only way to execute these statements using TIBCSQL is to put them in a stored procedure as I described in my previous post.
The TIBCQuery component can retrieve rows from the database and that is why it is better to use it for executing the 'SELECT MAX' and 'SELECT SUM' statements.

[inageib]

After this line for code:

IBCQuery1.Insert;

I call to show a form and that form have the dbtext1 which does not display the new returned value unless I called post.

about TIBCSQL I was using another library "FIBPlus" that have similar component able to return values from select function. this is why I asked is it better to have the same (performance wise).

[AndreyZ]

Such behaviour is correct. The Insert method of a dataset inserts a new empty record to the dataset, it does not do any requests to the server. The trigger that you have on the server fires only when you execute the INSERT statement. The INSERT statement is executed when you call the Post method of the dataset.

To retrieve rows from the database, any component must support it. Both 'SELECT MAX' and 'SELECT SUM' statements return one row with one column that contains the result of the statement. In this case, performance depends on then speed of the database server, but not on the speed of the components, because all work is performed on the server. So, using any component that supports retrieving rows for execution of the 'SELECT MAX' or 'SELECT SUM' statement, you will have the same performance.