How to get last ID after TSQLQuery.Append() in PostgreSQL
lazarus / 16-Databases

----------------------------------------------------------------------
vladvons · 2013-10-19 07:30

Create simple table with auto increment 'id' field using 'SERIAL'
keyword for PostgeSQL

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT
);

Code: [Select]// this ID not exists. Just open SQLQuery (Dont know
easiest way)
SQLQuery.SQL.Text := 'SELECT * FROM users WHERE id = -1';
SQLQuery.Active := false;
SQLQuery.Active := true;

SQLQuery.Append();
SQLQuery.FieldByName('Name').AsString := 'John';

// save changes
if (SQLQuery.Transaction.Active) then
begin
  SQLQuery.ApplyUpdates();
  TSQLTransaction(SQLQuery.Transaction).Commit();
end;

// Now i want to know lasdt ID
SELECT currval(pg_get_serial_sequence('users', 'id'))
ERROR:  currval of sequence "users_id_seq" is not yet defined in this
session

SELECT last_value FROM users_id_seq;
OK

// i dont want to use 'RETURNING'
INSERT INTO users (name) VALUES ('John') RETURNING id;


is  there possibility to get last ID with native FPC functions?

perhaps my SQLQuery.Append() conception is wrong, so please give me an
advice with example

----------------------------------------------------------------------
taazz · 2013-10-19 07:51

how about executing <SELECT currval(pg_get_serial_sequence('users',
'id'))> before  calling TSQLTransaction.commit? Be warned I do not
know if it will work just a thought.

----------------------------------------------------------------------
ludob · 2013-10-19 08:17

Quote from: taazz on October 19, 2013, 09:51:52 amhow about executing
<SELECT currval(pg_get_serial_sequence('users', 'id'))> before 
calling TSQLTransaction.commit? Be warned I do not know if it will
work just a thought.
It won't work if there are multiple simultaneous transactions.
Sequences in PG are not part of a transaction.

SQLQuery.Append() is just an in memory append. Nothing is done to the
DB before ApplyUpdates. So anything before that will not help in
getting the last ID.

A possible solution is to call  SELECT nextval(users_id_seq) and use
the resulting value to set your id with:
Code: [Select]SQLQuery.Append();
SQLQuery.FieldByName('ID').AsInteger := NewID;
SQLQuery.FieldByName('Name').AsString := 'John';

----------------------------------------------------------------------
vladvons · 2013-10-19 09:15

thanks ludob, works!