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

[Michael Van Canneyt]

On Sat, 22 Oct 2011, Felipe Monteiro de Carvalho wrote:

> 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);

Sets the initial value of the sequence.

>
> 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.

You must get a value first, and pass it along to the insert statement.

Auto-generation is not possible, since you don't have any link between 
the sequence and your table. (normally, this is done in the trigger).

Michael.