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

[Marco van de Voort]

In our previous episode, michael.vancanneyt at wisa.be 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.

A combination. Autogenerated, but the API call that the value must be
obtained from is basically the sequence object. (which also has different
operations).

Afaik Zeos manages to abstract this for all these dbs by having a sequence object.