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

[silvioprog]

On Thu, Nov 13, 2014 at 11:07 AM, Reimar Grabowski <reimgrab at web.de> wrote:

> Hi,
>
> first of all I am new to using Lazarus/FPC for database communication.
> I have set up my database connection, transaction and queries via the
> Object Inspector and all is working as it should. But I have a little
> problem getting the PK value of a newly inserted record (the PK field is an
> auto-incremented one).
> Actually it's not really a problem, I just don't like the way I do it and
> found no better one.
>
> Here is some code:
>
> SQLQuery.Insert;
> SQLQuery.FieldByName('SomeField').AsString:=SomeValue;
> SQLQuery.FieldByName('AnotherField').AsString:=AnotherValue;
> ... (setting more fields)
> SQLQuery.Post;
> SQLQuery.ApplyUpdates;
> SQLTransaction.Commit;
> // Now the part I don't like
> SQLQuery.Close;
> SQLQuery.Params.ParamByName('SomeField').AsString:=SomeValue;
> SQLQuery.Params.ParamByName('AnotherField').AsString:=AnotherValue;
> ... (setting more params, the same as the fields in the insert)
> SQLQuery.Open;
> NewID:=SQLQuery.FieldByName('AutoIncrementedPrimaryKey').AsInteger;
>
> It seems to work but it's IMHO not very nice.
> Ideally I'd like to have TSQLQuery do it for me and offer me some property
> like SQLQuery.LastInsertID or something along those lines.
> Is there any way to get the new PK value without manually querying for the
> inserted record?
>
> BTW the wiki (http://wiki.freepascal.org/Working_With_TSQLQuery) mentions
> that you should send 0 for the ID and add an appropriate 'ON DUPLICATE KEY'
> phrase to your SQL. I did not do this. I just omitted the ID in my
> SQLQuery.InsertSQL and AFAICT it works flawlessly.
>
> R.


I use an own "TPreparedStatement" (equivalent to TSQLQuery prepared +
Params, but my structure does not uses TDataset) that I get the last
inserted ID with (pseudo codes):

[code]
uses
  Core.SQL.PreparedStatement, ...

  VStmt := TPreparedStatement<TMyObject>.Create;
  ID := VStmt.Execute; // get last inserted ID instead of rowsaffected
[/code]

or:

[code]
  VStmt := TPreparedStatement<TMyObject>.Create;
  ID := VStmt.Execute.Values.ID; // get last inserted ID
[/code]

or:

[code]
uses
  Core.SQL.ConnectionFactory, ...

  PK := TConnectionFactory.GetInstance(Context).Insert(MyObject);
[/code]

or:

[code]
  TConnectionFactory.GetInstance(Context).Insert(MyObject);
  WriteLn('The generated ID is: ', MyObject.Id);
[/code]

or with on conflict:

[code]
  TConnectionFactory.GetInstance(Context).UpdateWithOnConflict(MyObject,
TConnection.CONFLICT_REPLACE);
  WriteLn('The generated or existing ID is: ', MyObject.Id);
[/code]

I'll send it soon to the Github. =)

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20141113/f4c7a750/attachment-0003.html>