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

[luiz americo pereira camara]

2014-11-14 11:43 GMT-03:00 Michael Van Canneyt <michael at freepascal.org>:

On Fri, 14 Nov 2014, Marcos Douglas wrote:
>
>
> This RETURNING clause works for all DBMS?
>>
>
> AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS
> :-)
>

MySQL also does not implement it

This is the workaround i use today:

var
  Info: TSQLStatementInfo;
  ActualConnection: TSQLConnection;
  InsertQuery: TSQLQuery;
begin
  Result := '';
  if (FConnection is TSQLConnector) then
    ActualConnection := TSQLConnectorAccess(FConnection).Proxy
  else
    ActualConnection := FConnection;

  Info :=
TSQLConnectionAccess(ActualConnection).GetStatementInfo(Query.SQL.Text,
True, stNoSchema);
  InsertQuery := CreateInsertQuery(Query, Info.TableName,
ActualConnection.FieldNameQuoteChars);
  try
    if (ActualConnection is TPQConnection) or (ActualConnection is
TIBConnection) then
    begin
      InsertQuery.SQL.Add(Format('Returning %s', [FPrimaryKey]));
      InsertQuery.Open;
      if InsertQuery.RecordCount > 0 then
         Result := InsertQuery.Fields[0].AsString;
     ActualConnection.Transaction.CommitRetaining;
  end
  else
  begin
    InsertQuery.ExecSQL;
    ActualConnection.Transaction.CommitRetaining;
    if (ActualConnection is TSQLite3Connection) then
      Result := IntToStr(TSQLite3Connection(ActualConnection).GetInsertID)
    else if (ActualConnection is TConnectionName{MySql}) then
      Result := IntToStr(TConnectionName(ActualConnection).GetInsertID);
  end;
  finally
    InsertQuery.Destroy;
  end;
end;

Luiz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20141114/697d236e/attachment-0003.html>