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

[Michael Van Canneyt]

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

> 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.

I fail to see how it can do that. E.g. the mechanism by Martin is just a trick to update 
a field value after an insert/update. That's a fine/useful mechanism, but not what I'd call 
'abstracting'.

But never mind. I think the trick is useful, and we should look into getting it in SQLDB.

Michael.