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

[michael.vancanneyt at wisa.be]

On Sat, 22 Oct 2011, Marco van de Voort wrote:

> In our previous episode, Felipe Monteiro de Carvalho said:
>> Ok, now I want to insert a record in my table and I would like to
>> obtain the auto-generated PrimaryKey
>
> This is a classic problem, since SQL simply doesn't support this.
>
> So all DBs do something else, for postgresql there are sequence objects that
> can be queried, while other allow to return the id of the autogenerated
> fields.
>
> Afaik sqldb does not abstract this yet.

That's because it can't be abstracted correctly. There are 2 incompatible mechanisms.

1. autogenerated fields (mysql, MS SQL server) which must be retrieved after the insert using a special API.

2. or sequences, which must be generated manually before the insert (DB2, Oracle, Firebird) using a special
    API, but which may or may not be generated in an AFTER INSERT trigger.
    In which case it's impossible to retrieve the sequence value after the insert except by re-reading the record.

I don't know which mechanism postgresql follows.

Michael.