Active record after insert of a new record is not always the inserted

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Active record after insert of a new record is not always the inserted

Post by StefanDWC » Tue 04 Sep 2018 14:16

M$SQLServer
UniDac 7.3.9

If i post a new record into a table using a TUniQuery is not always the inserted record the active record after the post is done.
I open the Query with SELECT * FROM Table
There is no filter active.
KeyFields is the identity field of the table.

It works well for tables without insert trigger. But if there is a insert trigger on the table the active record is always the first record.
As far as i understood it, you manage the insert by another query, why add you not a script line to retrieve the identitiy?
So, just add a property to switch this on as an option to get the id of the latest inserted record.
Then is it easy for you to use it to move the cursor of the visible query to the new inserted record.
Or, provide information under which field name of a result table you expect the identitiy value for the inserted record and provide a chance to modify the insert script before you send it.
There are lots of options to get it easily done.
An insert trigger is not uncommon, and in UniDac is already 99,9% of the programming done to do a perfect insert in each case.
Why stop you there and let us/the user try to find a not properly working solution only to get it working?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Active record after insert of a new record is not always the inserted

Post by Stellar » Thu 06 Sep 2018 14:02

UniDAC automatically generates an SQL statement to add a record to the database. The query includes obtaining the identifier value using the system function SCOPE_IDENTITY(). If, when adding a record to the table, the trigger is executed, in which the record is added to some other table, then the SCOPE_IDENTITY() function will return a correct value of the identifier for the added record in any way. Because the SCOPE_IDENTITY() function is limited to the scope.
However, if you don't want to use an automatic query, you can set a custom one. You can use the SQLInsert property to specify a SQL statement that will be used when applying an insertion to a dataset. To specify an SQL query that will be used when inserting a record, you can use the design-time editor of the component for this:
- call the TUniQuery component editor (double click on the component);
- go to the Update SQLs tab;
- select Insert in Statement Type, and enter the SQL query to insert the record.
You can also use the SQL query generator (the SQL Generator tab) to create a default sql query to edit a record, and then edit the query if needed.

You can also set a value for the SQLInsert programmatically:
UniQuery1.SQLInsert.Text := 'INSERT INTO Dept (DNAME, LOC) VALUES (:DNAME, :LOC) SET :DEPTNO = SCOPE_IDENTITY()';

More details about SQLInsert property here:
devart.com/sdac/docs/devart.dac.tcustomdadataset.sqlinsert.htm

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Active record after insert of a new record is not always the inserted

Post by StefanDWC » Mon 10 Sep 2018 12:36

Hello Stellar,

thanks for reply. To get the identity value of the inserted record works very well as long there is no "INSTEAD OF INSERT" trigger on the table where i insert into. If there is one i get always 0 as identity value and the focused record (at a TUniQuery) is always the first one in the table.
I tried it several times, with different tables.
If i use a script for the insert (via dbForge) and request the new value for the identity column via @@IDENTITY i get the expected value back.
Script which i used:
insert into documents (DocType, FirstName) VALUES (60, 'Panda');
select @@IDENTITY AS NewRecord;

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Active record after insert of a new record is not always the inserted

Post by Stellar » Wed 12 Sep 2018 07:24

The SCOPE_IDENTITY system function returns values ​​inserted only within the current scope, as the @@IDENTITY function action is not restricted by any scope. The query to add a record that executes TUniQuery, and the trigger have different scopes. When using the "INSTEAD OF INSERT" trigger, adding a record into the table occurs in the trigger body, but obtaining the identifier in the record addition query. Therefore, the SCOPE_IDENTITY function cannot get the identifier value, while the @@IDENTITY function will get the value of the identifier.

You can create a custom query to add a record that will use the @@IDENTITY system function to get the identifier, for example:

Code: Select all

UniQuery1.SQLInsert.Text := 'INSERT INTO documents (DocType, FirstName) VALUES (:DocType, :FirstName) SET :ID = @@IDENTITY';
If you are using your own INSERT SQL query in the dataset, then to retrieve the inserted record ID, you should :
- set TUniQuery.Options.ReturnParams to True
- in the TUniQuery.BeforeUpdateExecute event specify explicitly the ptInputOutput parameter type for the Identity field

For example:

Code: Select all

DetailUniQuery.Options.ReturnParams := True;
...
procedure TForm1.DetailUniQueryBeforeUpdateExecute(Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if StatementTypes = [stInsert] then
    Params.ParamByName('ID').ParamType := ptInputOutput;
end;

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Active record after insert of a new record is not always the inserted

Post by StefanDWC » Sun 16 Sep 2018 08:02

Hello Stellar,

thanks for reply. Sounds promising! Unfortunately, it is only be a plan B solution.
I understand why your guys prefer to use SCOPE_IDENTITY().
However, i would like, if your add an option to switch between SCOPE_IDENTITY() and @@IDENTITY. Because, this table have not only this two fieds, there are a lot. So, i have to create a much bigger statement with many different fied types which are "randomly" filled by the user depending on the use case.
Up to now i had no single case where @@IDENTITY was not working perfectly in my scripts.
I would be very happy to see that your add that option with the next update.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Active record after insert of a new record is not always the inserted

Post by Stellar » Wed 19 Sep 2018 13:01

In TUniQuery, TUniTable, there is a special option LastIdentityValueFunction that determines which system function to use to obtain an identifier when adding a record. The LastIdentityValueFunction options can be set to the following values:
vfIdentity - @@IDENTITY
vfIdentCurrent - IDENT_CURRENT
vfScopeIdentity - SCOPE_IDENTITY

Example of using the @@IDENTITY function to obtain the indentifier when adding a record:
UniQuery1.SpecificOptions.Values['SQL Server.LastIdentityValueFunction'] := 'vfIdentity';

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Active record after insert of a new record is not always the inserted

Post by StefanDWC » Thu 20 Sep 2018 07:05

Hello Stellar,

thanks for reply. It seems the components have much more flexibility than officially documented.
I will test the different values and will tell you which one is working well in that case.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Active record after insert of a new record is not always the inserted

Post by Stellar » Wed 26 Sep 2018 08:13

Thank you for the information. We will add the description of the LastIdentityValueFunction property for MS SQL Server to the online documentation.

Post Reply