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

[michael.vancanneyt at wisa.be]

On Mon, 24 Oct 2011, Reinier Olislagers wrote:

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

Typo on my part, sorry.

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

Yes, but not all DBs support this. We use Firebird a lot, and it didn't
support that construct until version 2.1 or so.

In each case: it's hard to abstract correctly, but nevertheless we'll try
and make managing this easier. Martin's ideas for this are useful.

What's funny is that I never understood how the MySQL/SQL-Server way could
correctly work. For example I have 2 tables, both with an auto-incremental
field. 
Table 1 has an after insert trigger that does an extra insert in table 2. 
When I do an insert in table 1 , what does 'last_insert_id' return ? 
The value for table 2 or table 1 ? 
The last inserted id for your connection is the one for table 2, but you 
need/expect the last id for table 1 :-)

Michael.