[Solved] INSERT RETURNING an_id

[ig]

I've followed the wiki https://wiki.lazarus.freepascal.org/postgres#How_To hoping to get this working:

Query.SQL.Text:= 'INSERT INTO myschema.films(film_name)' +
                 'VALUES(:film_name) RETURNING film_id;';
Query.Params.ParamByName('film_name').AsString:='Mission Impossible';
Query.Open;
ID:= Query.FieldByName('film_id').AsInteger;

However I always get the error message: SQLQuery: field not found "film_id".

What am I missing here?

Lazarus 2.0.6
Database: PostGreSQL 12.1
OS: Windows 10

[Zvoni]

What am I missing here?

You don't have a column called 'film_id' which is integer primary key with autoincrement/autovalue?

[egsuh]

Try using quotations...  i.e.

  Returning "film_id";       

I do not know exactly, but I experienced similar problem.  PostgreSQL seems to require " "   when the name contains _.   Also it may be case-sensitive. 

If this still do not work, try films."film_id".

[ig]

I isolated the case and got it working. Need to figure out why it's not working in a more complicated world.

Txs,
ig

[ig]

Some clarification

• Before assigning the SQL.Text, make sure the query is closed. When this is not the case, subsequent calls to the insert query will result in the same id.
• The insert sql needs to be self standing, i.e. in case a datasource is associated with it,  will not work