I use the DataSet to insert a record into the database. But I also want to return the id of this entry in the insert request. For this, I wrote a query for the InsertSQL property

insert into FEATURES_ETALON (CIMP_MRA, CODE_CLASSIF, DEVICE_METHOD, DIAPASON_MAX, DIAPASON_MIN, FORMAT, ID, MEASURAND, MEASURING_APPARATUS, PARAMETER_CONDITION_MEAS, UNIT, VALUE_AMBIGUITY, VALUE_CONDITION_MEAS) values (:CIMP_MRA, :CODE_CLASSIF, :DEVICE_METHOD, :DIAPASON_MAX, :DIAPASON_MIN, :FORMAT, GEN_ID(GEN_ID_FEATURES_ETALON, 1), :MEASURAND, :MEASURING_APPARATUS, :PARAMETER_CONDITION_MEAS, :UNIT, :VALUE_AMBIGUITY, :VALUE_CONDITION_MEAS) returning ID into :ID

but since there is no ID parameter, then in the place “into: ID”, the error is: “" Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 5, column 19.?. "

Next in the AfterPost (DataSet) event, I want to assign a value to the ID field .

 procedure TFMain.DataSetAfterPost(DataSet: TDataSet); begin DataSet.FieldByName('ID').AsInteger := DataSet.ParamByName('ID').AsInteger; DataSet.Transaction.CommitRetaining; end;
  • How do I create a parameter in the DataSet?
  • Am I assigning an ID in the right place?

    1 answer 1

    You can BeforePost id in BeforePost , AfterInsert , OnNewRecord . Then you will not need to return it, because it will already lie in the appropriate field.
    It may still be BeforeInsert , but I'm not sure that by this time the DataSet is already in edit mode.

    To set the id in the event handler, you will need a query to retrieve the next value of the generator. It is executed in the handler.

    Did Delphi know how to use interbase generators before? It was necessary only to indicate from which generator to take the value for the field. And that's all. No event handlers. Is it wrong now or is it all wrong?

    The AfterPost happens after the record is stored in the database. It's too late to assign something here.
    I think if your handler worked, he would write a DataSet is not edit or insert mode error on the first line.

    As for returning , Delphi does not support it on the go (I don’t remember this). After all, this is a relatively recent feature of firebird, and delphi has always been guided by interbase, where there was no such returning and a bet on preliminary id generation. We'll have to write our own UpdateSQL components.

    Usually there are still opportunities to find out what id was used in the new record. To do this, after writing to the database (in the AfterPost handler), perform a special request. But how then to transfer this value back to the DataSet? The same DataSet is not edit or insert mode error DataSet is not edit or insert mode .

    • Then, as it was, and leave. In principle, the query above returns the desired ID, but it seems to me that it is too late to insert it into the dataset. - Konstantin78
    • In truth, it seemed to me that the error "Invalid token ..." indicates that the request was not made correctly. and it is not executed in principle. 100% working version in the first 2 paragraphs. To facilitate the task itself clarify the 3rd. - Sergey
    • Yes, the request is incorrect only if you use " ... into: ID " without first creating a parameter in the DataSet , so it works. But here I was prompted by another solution for obtaining the ID — via the GeneratorField method ( IBDataSet ) with " Apply Event = OnPost ", which is just right for me - Konstantin78
    • @ Konstantin78 Well, that found this option. I wanted to put it on him in the 3rd paragraph. - Sergey