[fpc-pascal] How to insert a record and get the primary key with sqldb?
Michael Van Canneyt
michael at freepascal.org
Sat Oct 22 16:36:02 CEST 2011
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
>>> 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
> 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
But never mind. I think the trick is useful, and we should look into getting it in SQLDB.
More information about the fpc-pascal