On Sat, 22 Oct 2011, Felipe Monteiro de Carvalho wrote:
Hello,
Yet another chapter of my database problems =)
Ok, now I want to insert a record in my table and I would like to
obtain the auto-generated PrimaryKey
I tryed a number of options:
SQLGameSession.Insert();
SQLGameSession.Edit;
Remove this call. Edit must be called to edit an existing record.
SQLGameSession.FieldByName('GameEvent').AsInteger := lGameEventId;
SQLGameSession.Post;
You should add ApplyUpdates. Post posts the changes in the internal memory
buffer; Nothing is saved to the database untill you call ApplyUpdates.
This one just failed silently. Nothing was added to the database, no
idea why. I had done a SELECT * FROM WHERE which selected nothing.
Should I instead make a full selection of the table? Isn't that very
inneficient for just inserting 1 record? (My app is already very slow
in the database connection as my profiling shows)
And another attempt:
DBComm.SQLTransaction.StartTransaction; <-- Fails here with
"transaction already active"
Correct. The default transaction is made active after connecting.
You should call StartTransaction on this transaction only after a commit or
rollback.
SQLText := Format('INSERT INTO "GameSession" (GameEvent, TableId)
values (%d, %d);', [lGameEventId, ARequest.GameId]);
DBComm.PQConnection.ExecuteDirect(SQLText);
DBComm.SQLTransaction.Commit;
It's not recommended to use ExecuteDirect. Instead do a
With TSQLQuery.Create(Self) Do
try
Database:=MyDatabase;
Transaction:=MyDatabase.SQLTransaction;
SQL.Text:='INSERT INTO "GameSession" (GameEvent, TableId) values (:EventID,
:GameId);';
ParamByName('GameEvent).AsInteger:=lGameEventId;
ParamByName('GameID').AsInteger:=ARequest.GameId;
ExecSQL;
(Transaction as TSQLTransaction).Commit;
finally
Free;
end;
The use of parameters is recommended, it avoids SQL injection.
Not to mention that in this second way I would have no idea how to
obtain the primary key...
By reading the just created record, see my reply from yesterday.
If the table uses a sequence, first get the new sequence value, and then do
the insert with the generated value.
Didn't you get a copy of the Lazarus book ? There is a whole chapter on
database programming with Lazarus. All the above is explained there.
Michael.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
