On Sat, 22 Oct 2011, Felipe Monteiro de Carvalho wrote:
On Sat, Oct 22, 2011 at 3:33 PM, <michael.vancann...@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.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
