Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: SQL Server SCOPE_IDENTITY doesn't work on FireDac objects in Delphi XE7


This question is answered.


Permlink Replies: 3 - Last Post: Jan 27, 2016 5:32 AM Last Post By: Dmitry Arefiev
Sergio Govoni


Posts: 3
Registered: 5/29/08
SQL Server SCOPE_IDENTITY doesn't work on FireDac objects in Delphi XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 21, 2016 3:58 AM
Hi guys,

I'm working with Delphi XE7 and I'm trying to use the SQL Server SCOPE_IDENTITY (http://bit.ly/1nbjH7J) function to retrieve the last identifier inserted (after an INSERT statement) on a table that has an identity column.

When I call this function both on a FireDac query and on a FireDac connection (with the ExecSQLScalar function) it always retrieves NULL. I suppose the SCOPE_IDENTITY is executed with another scope and for this reason it returns me NULL.

I have to use the SCOPE_IDENTITY function because my tables may contain Triggers and functions @@IDENTITY (http://bit.ly/1nbjMrO) and GetLastAutoGenValue return identities values from the Trigger and not from the first table where I execute the INSERT statement.

I'm trying to execute something like this:

var
  Identity_Result: Variant;
  DB: TFDConnection;
 
...
...
 
Identity_Result := DB.ExecSQLScalar('Select Scope_Identity() as U');
if (Identity_Result <> Null) then
  Result := Identity_Result;
 
...
...
 


I'm missing something? Have you any ideas?

Thanks!

Sergio Govoni
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL Server SCOPE_IDENTITY doesn't work on FireDac objects in Delphi XE7
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 25, 2016 10:52 PM   in response to: Sergio Govoni in response to: Sergio Govoni
1) Consider to use TFDConnection.GetLastAutoGenValue('')
2) SCOPE_IDENTITY may return NULL because there are MARS enabled
and/or prepared statement are in usage. You can try the following:
* set DB.ResourceOptions.DirectExecute to True then call ExecSQLScalar
* or use dedicated TFDQuery with ResourceOptions.DirectExecute = True
* disable MARS by adding MARS=No to your connection parameters

--
With best regards,
Dmitry
Sergio Govoni


Posts: 3
Registered: 5/29/08
Re: SQL Server SCOPE_IDENTITY doesn't work on FireDac objects in Delphi XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 27, 2016 3:21 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Hi Dmitry,

thanks for your answer! I tried with DB.ResourceOptions.DirectExecute set to True before the INSERT statement and now the call of SCOPE_IDENTITY function works on the same scope of the INSERT statement even without using dedicated TFDQuery.

I'm going to try if this setting may affects on performance..

Bye
Sergio
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL Server SCOPE_IDENTITY doesn't work on FireDac objects in Delphi XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 27, 2016 5:32 AM   in response to: Sergio Govoni in response to: Sergio Govoni
I'm going to try if this setting may affects on performance..

With one time executing commands and SQL Server the direct
execution will improve performance. And may avoid different similar
to your problems.

With multiple times executing commands or with other than
SQL Server databases the direct execution will kill performance.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02