Lazarus

Free Pascal => Database => Topic started by: ig on March 11, 2020, 10:05:15 pm

Title: INSERT RETURNING an_id
Post by: ig on March 11, 2020, 10:05:15 pm
I've followed the wiki https://wiki.lazarus.freepascal.org/postgres#How_To (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
Title: Re: INSERT RETURNING an_id
Post by: Zvoni on March 12, 2020, 09:10:01 am
What am I missing here?
You don't have a column called 'film_id' which is integer primary key with autoincrement/autovalue?
Title: Re: INSERT RETURNING an_id
Post by: egsuh on March 12, 2020, 09:38:23 am
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".

Title: Re: INSERT RETURNING an_id
Post by: ig on March 12, 2020, 10:50:51 am
I isolated the case and got it working. Need to figure out why it's not working in a more complicated world.

Txs,
ig
Title: Re: INSERT RETURNING an_id
Post by: ig on March 12, 2020, 11:08:39 am
Some clarification


TinyPortal © 2005-2018