[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)

[Marcos Douglas]

On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt
<michael at freepascal.org> wrote:
>
>
> On Fri, 14 Nov 2014, Reimar Grabowski wrote:
>
>> On Thu, 13 Nov 2014 13:26:32 -0300
>> Marcos Douglas <md at delfire.net> wrote:
>>
>>> Hi,
>>> Take a look in Greyhound project: https://github.com/mdbs99/Greyhound
>>
>> I did and could not see how it helps.
>>
>>> Here you can see an example using "last id":
>>> https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285
>>
>> Yes, if you are using Greyhound, which I am not.
>>
>> So it looks like there's nothing in TSQLQuery that does the job for me.
>> I now just use another TSQLQuery with the following SQL:
>>
>> SELECT LAST_INSERT_ID() AS SomeNameIChoose;
>>
>> Seems to work well, reduces the lines of code as I don't have to set
>> parameters (my primary goal), can be reused for other inserts (being not
>> table dependent) and took about 1 Minute to implement.
>
>
> Connections for databases that have lastinsertID you can call the method
> created for this:
>
> Function GetInsertID: int64;
>
> There is a plan to let SQLDB retrieve the value of some fields fro the DB,
> but it is not implemented yet.

Hi Michael,

To do that is need to implement a GetInsertID method in all drivers of
SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate:

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;

      if Fld.ReadOnly then
        Fld.ReadOnly := False;

      Edit;
      Fields[I].SetData(@LastId);
      Post;
      Exit;
    end;
  end;
end;

https://github.com/mdbs99/Greyhound/blob/master/src/ghsqldblib.pas#L157


Marcos Douglas