Hi,

I'm using 2.6.5 (fixes_2_6) now but something broke between 2.6.2 and 2.6.5 using AutoInc fields.
AutoInc never worked well (at least for MSSQL) and because that I have my own "Query" [1] that implements AutoInc values very well.

I override the ApplyRecUpdate method from TSQLQuery and update the AutoInc field getting the value in DB using FLib.GetLastAutoIncValue method that, for MSSQL, I use "select scope_identity() as id" (see code below)

The problem is to use Field.SetData to put a value. If the field is
AutoInc it is ReadOnly too and I got a exception:
      Edit;
      Fields[I].SetData(@LastId);
      Post;

But in 2.6.2 version it worked.

I need to know the ID (identity column). So, if the behavior was changed between 2.6.5 and 2.6.2, then the AutoInc fields needs to works too, no?

Now, how I can get the ID using AutoInc, please?

=== BEGIN CODE ===
procedure TghSQLdbQuery.ApplyRecUpdate(UpdateKind: TUpdateKind);
var
  I: Integer;
  LastId: NativeInt;
  Fld: TField;
begin
  inherited;
  if UpdateKind  ukInsert then
    Exit;
  for I := 0 to Fields.Count -1 do
  begin
    Fld := Fields.Fields[I];
    if Fld.IsNull and
       ((Fld.DataType = ftAutoInc) or (LowerCase(Fld.FieldName) =
'id') and (Fld is TNumericField)) then
    begin
      LastId := FLib.GetLastAutoIncValue;
      if LastId <= 0 then
        Exit;
      Edit;
      Fields[I].SetData(@LastId);
      Post;
      Exit;
    end;
  end;
end;
=== END CODE ===

[1] https://github.com/mdbs99/Greyhound

Regards,
Marcos Douglas

Do you have tried for ftAutoInc field simply set ReadOnly:=False; ? -Laco.
P.S. there is feature request about automatic fetching of new values of idnetity columns, but implementing it in generic way for all supported SQLConnections is not trivial ;-)

In MSEgui I used flags in tfield.providerflags1
(pf1_refreshinsert,pf1_refreshupdate) which trigger the use of the "returning" clause in insert or update statement if the DB supports it. Additionally there is tcustomsqlconnection.updateprimarykeyfield() which is called after insert, it automatically updates the primary key field field with the "last insert id" if the DB supports it.
The third option is to use tsequencelink which automatically fetches sequence values from the DB before inserting.

Martin

Martin Schreiber wrote / napísal(a):

Thanks for interesting points, ideas
-Laco.

LacaK wrote

Do you have tried for ftAutoInc field simply set ReadOnly:=False; ? -Laco.

Errr... I forgot this option... worked, thanks! :)

P.S. there is feature request about automatic fetching of new values of idnetity columns, but implementing it in generic way for all supported SQLConnections is not trivial ;-)

Why not only start the implementation?
I think some property "UpdatePrimaryKey". This property could be use in an automatic way for some DBMS eg MSSQL that you have to call "select scope_identity() as id".
Maybe, for some DBMS, the programmer could write some SQL to execute, similar to "UpdateSQL".

Martin Schreiber, has many good ideas too. ;-)

Regards,
Marcos Douglas