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

[Reinier Olislagers]

On 22-10-2011 15:33, michael.vancanneyt-0Is9KJ9Sb0A at public.gmane.org wrote:
> 
> 
> 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.

Correct, but for option 2. - at least in Firebird - BEFORE INSERT
triggers are often used, not AFTER INSERT.
See eg http://www.firebirdfaq.org/faq29/

Also, using INSERT...RETURNING it's very well possible to get the
sequence value directly when inserting the data.

What's more incompatible to me is the difference between:
a. manually having to specify a PK, either totally manually or using a
sequence
b. having the DB do it for you, either via an autonumber (your option 1)
or a trigger with a sequence.

The problem is that I think it's very difficult to SQLDB to figure out
whether it's in situation a or b. If in a and the DB supports sequences,
SQLDB will still need to be told which sequence to use.

If in b, we face all the various ways of getting an autonumber or
auto-generated PKs back, see e.g.
https://secure.wikimedia.org/wikipedia/en/wiki/Insert_%28SQL%29#Retrieving_the_key

When in b, we then get the issue of getting the primary key:

Doing it in one statement:
INSERT RETURNING for Firebird, Postgresql, Oracle, ....?
...

In two:
SCOPY_IDENTITY() for SQL Server 2000 and earlier, or if you don't/can't
use OUTPUT
LAST_INSERT_ID()
Special select statement for DB2
....

;)