DA - SQL Server & Sequences

Data Abstract
1

Hi,
I can’t seem to insert a record in a TDAMemDataTable when the table has a sequence as primary key.

Error: -1: Can't refresh delta, record isn't found

Which is a correct message because the statement executed after inserting the record is
SELECT IsNull(@@Identity, 0) as LastInc

DA seems to neglect the generator/sequence.

image
image739×645 15.6 KB

Using Delphi 10.3.2 with DA 10.0.0.1457 (FireDAC driver)

Any tips?

Regards,
Filip

2

Filip,

i’m afraid I’ll have to leave this to Eugene to answer next week; he’s off for a well-deserved vacation, this week.

4

Hi,

I was browsing the code and found that sequences are not supported for MSSQL.

function MSSQL_DoGetLastAutoInc(const GeneratorName: string;Query: IDAServerDataset): variant;
begin
  try
     // Using Ident_Current is not fully safe
     //if Trim(GeneratorName) <> '' then
     //  Query.SQL := 'SELECT IsNull(Ident_Current(' + QuotedStr(GeneratorName) + '), 0) as LastInc'
     //else
    Query.SQL := 'SELECT IsNull(@@Identity, 0) as LastInc';
    Query.Open;
    result := Query.Fields[0].Value;
  finally
    Query := nil;
  end;
end;

However, checking the mechanism of PostgreSQL or Firebird, I noticed a similar mechanism is used.

function IB_GetLastAutoInc(const GeneratorName: string;Query: IDAServerDataset): Variant;
begin
  try
    Query.SQL:=Format('SELECT Gen_id(%s,0) FROM RDB$Database', [GeneratorName]);
    Query.Open;
    Result := Query.Fields[0].Value;
  finally
    Query:=nil;
  end;
end;

Getting the value of a sequence after an insert is never correct because sequences ‘do not obey’ transactions. The value of a sequence transcends a transaction. The only correct way - in my opinion - is getting the next value before doing the actual insert. Since you’re doing 2 queries anyway, there would be no influence in performance.
Note that this is only for sequences. The current mechanism for autoinc values is correct, because the engine has a special API for it.

Best regards,
Filip Lagrou

5

In addition, I changed the code in uDAADOInterfaces pending a final solution… (lines 510 up to 524)

function MSSQL_DoGetLastAutoInc(const GeneratorName: string;Query: IDAServerDataset): variant;
begin
  try
    if Trim(GeneratorName) <> '' then
      Query.SQL := 'SELECT current_value FROM sys.sequences WHERE name = ' + QuotedStr(GeneratorName)
    else
      Query.SQL := 'SELECT IsNull(@@Identity, 0) as LastInc';

    Query.Open;
    result := Query.Fields[0].Value;
  finally
    Query := nil;
  end;
end;

Regards,

Filip Lagrou

6

Ah yes, I believe MSSQL has built-in AutoInc field type support, so it doesn’t need a separate sequence/generator set up.

7

Logged as bugs://D19276 for investigation

8

bugs://D19276 was closed as fixed.

9

Hi Marc

Ah yes, I believe MSSQL has built-in AutoInc field type support, so it doesn’t need a separate sequence/generator set up.

SQL Server supports sequences (since version 2012) and the AutoInc field type. When using sequences in SQL Server the MSSQL_DoGetLastAutoInc method results in a faulty value.

In my opinion bug D19276 is not fixed or am I missing something?

Regards,
Filip

10

Hi,

it was fixed 4 days ago and we haven’t created public build yet that contains this fix

11

ah… OK, I thought this was included in .1569
My mistake, sorry!