[fpc-pascal] Re: How to insert a record and get the primary keywith sqldb?

[Ludo Brands]

> This code:
> 
>   SQLGameSession := TSQLQuery.Create(nil);
>   SQLGameSession.Transaction := DBComm.SQLTransaction;
>   SQLGameSession.Database := DBComm.PQConnection;
>   SQLGameSession.FieldDefs.Add('SessionId', ftLargeint);
>   SQLGameSession.FieldDefs.Add('GameEvent', ftLargeint);
>   SQLGameSession.FieldDefs.Add('TableId', ftInteger);
> 
>   OPDebugLn('[TGameSessionComm.CreateSessionResponse] 
> Obtaining New Primary Key');
>   SQLGameSession.SQL.Text:='SELECT
> nextval("GameSession_SessionId_seq") as res;';
>   SQLGameSession.Open;
>   FSessionId := (SQLGameSession.Fields[0] as 
> TLargeIntField).AsLargeInt;
>   SQLGameSession.Close;
> 

The space before OPDebugLn indicates other code in between? If not, you
can't add fielddefs for fields that aren't going to be in the result set. I
just made a small test with the table and sequence definition you provided
earlier and this works for me:

var
  SQLGameSession : TSQLQuery;
  SessionId:LargeInt;
begin
  SQLGameSession := TSQLQuery.Create(nil);
  SQLGameSession.Transaction := SQLTransaction1;
  SQLGameSession.Database := PQConnection1;
  SQLGameSession.SQL.Text:='SELECT nextval(''"GameSession_SessionId_seq"'')
as res';
  SQLGameSession.Open;
  SessionId := (SQLGameSession.Fields[0] as TLargeIntField).AsLargeInt;
  SQLGameSession.Close;
  SQLGameSession.SQL.Text:='INSERT INTO "GameSession" ("SessionId",
"GameEvent", "TableId") values (:SessionId,:EventID, :GameId)';
  SQLGameSession.ParamByName('SessionId').AsInteger:=SessionId;
  SQLGameSession.ParamByName('EventID').AsInteger:=234;
  SQLGameSession.ParamByName('GameID').AsInteger:=123;
  SQLGameSession.ExecSQL;
  (SQLGameSession.Transaction as TSQLTransaction).Commit;
  SQLGameSession.Free;
end;

Ludo