[fpc-pascal] How to insert a record and get the primary key with sqldb?

[Felipe Monteiro de Carvalho]

Hello,

Yet another chapter of my database problems =)

Ok, now I want to insert a record in my table and I would like to
obtain the auto-generated PrimaryKey

I tryed a number of options:

  SQLGameSession.Insert();
  SQLGameSession.Edit;
  SQLGameSession.FieldByName('GameEvent').AsInteger := lGameEventId;
  SQLGameSession.Post;

This one just failed silently. Nothing was added to the database, no
idea why. I had done a SELECT * FROM WHERE which selected nothing.
Should I instead make a full selection of the table? Isn't that very
inneficient for just inserting 1 record? (My app is already very slow
in the database connection as my profiling shows)

And another attempt:

  DBComm.SQLTransaction.StartTransaction; <-- Fails here with
"transaction already active"
  SQLText := Format('INSERT INTO "GameSession" (GameEvent, TableId)
values (%d, %d);', [lGameEventId, ARequest.GameId]);
  DBComm.PQConnection.ExecuteDirect(SQLText);
  DBComm.SQLTransaction.Commit;

Not to mention that in this second way I would have no idea how to
obtain the primary key...

any suggestions welcome.

thanks,
-- 
Felipe Monteiro de Carvalho