[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)

[Michael Van Canneyt]

On Fri, 14 Nov 2014, Marcos Douglas wrote:

>> By the very nature of the problem: GetLastAutoIncValue simply cannot be
>> implemented in general.
>>
>> In firebird, postgres, these values are generated using a
>> generator/sequence.
>
> ...as well as could have a trigger for these cases, encapsulating the
> "problem" in database... but the code in these cases should be
> different.

I know. RETURNING will handle this case correctly.

>
>> You don't know the name of the generator, and even if you did, it is
>> impossible to retrieve the value that was used to fill your particular
>> record because the sequence/generator may have been updated several
>> thousands of times by the time your second statement arrives.
>
> If you use a sequence/generator, you should call it before INSERT.

That is one way, but not obligatory. Mostly it is done in triggers.

This is not a problem. RETURNING handles all cases.

> If you use auto-inc, the DMBS have support to return the last id on
> your session.

I know.

>
>> The only way to do this correctly is using a RETURNING clause when doing the
>> insert.
>
> This RETURNING clause works for all DBMS?

AFAIK Only sqlite does not (maybe it does today). 
But that is not a DBMS :-)

>> The idea is to add [pfRefresh] to providerflags. When doing an insert, the
>> fields with this flag will be added to the RETURNING clause and used to
>> update the buffer.
>>
>> Fields of type ftautoInc can be added to this list (i.e. they automatically
>> get pfRefresh in their providerflags)
>>
>> This approach will work on all SQL databases that support RETURNING.
>> (That should include any SQL database available today)
>
> Nice. But this is only a part of code. How do you will get the last ID
> and put in record?

This is the complete code, because the "returning" clause will contain the ID.

Your remarks sound like maybe you don't understand/know exactly what RETURNING does ?
It was invented exactly to deal with such things.

Michael.