[fpc-pascal] How to insert a record and get the primary key with sqldb?
Michael Van Canneyt
michael at freepascal.org
Sat Oct 22 16:20:13 CEST 2011
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.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).
More information about the fpc-pascal