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

[michael.vancanneyt at wisa.be]

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.