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

[Felipe Monteiro de Carvalho]

On Sat, Oct 22, 2011 at 3:33 PM,  <michael.vancanneyt at wisa.be> wrote:
> 2. or sequences, which must be generated manually before the insert (DB2,
> Oracle, Firebird) using a special
>   API, but which may or may not be generated in an AFTER INSERT trigger.
>   In which case it's impossible to retrieve the sequence value after the
> insert except by re-reading the record.

Indeed, it looks like that Postgres uses this second method, because I did this:

  SQLText:='INSERT INTO "GameSession" ("GameEvent", "TableId") values
(:GameEventParam, :TableIdParam);';
  SQLGameSession.SQL.Text := SQLText;
  SQLGameSession.Params.ParamByName('GameEventParam').AsInteger:=lGameEventId;
  SQLGameSession.Params.ParamByName('TableIdParam').AsInteger:=ARequest.GameId;
  SQLGameSession.ExecSQL;
  DBComm.SQLTransaction.Commit;
  SQLGameSession.Active := True;

And I get a message saying that my value for SessionId is missing ...
but I thought it would be auto-generated, because I created my table
like this:

CREATE TABLE "GameSession" (
    "SessionId" bigint NOT NULL,
    "GameEvent" bigint NOT NULL,
    "TableId" bigint NOT NULL
);


ALTER TABLE public."GameSession" OWNER TO postgres;

CREATE SEQUENCE "GameSession_SessionId_seq"
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public."GameSession_SessionId_seq" OWNER TO postgres;

ALTER SEQUENCE "GameSession_SessionId_seq" OWNED BY "GameSession"."SessionId";

-- Not sure what this does
-- SELECT pg_catalog.setval('"GameEvent_Id_seq"', 1, true);

ALTER TABLE ONLY "GameSession"
    ADD CONSTRAINT "GameSession_pkey" PRIMARY KEY ("SessionId");

Note that I did not invest this stuff ... I just copied from stuff to
create other tables which were already in the project.

-- 
Felipe Monteiro de Carvalho