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

[Michael Van Canneyt]

On Fri, 14 Nov 2014, Marcos Douglas wrote:

>> Your remarks sound like maybe you don't understand/know exactly what
>> RETURNING does ?
>> It was invented exactly to deal with such things.
>
> AFAIK RETURNING is a SQL clause. I know that but I never used before.
>
> You tell me SQLdb will use RETURNING clause if the DMBS have support,
> right? Well, AFAIK MSSQL does not have support for this... but MSSQL
> have IDENTITY (auto-inc) fields. How I will use RETURNING on MSSQL? If
> this clause don't exists -- if already exists, sorry -- how I can get
> the ID auto-inc?
>
> Please, don't tell me that will be implemented a parser to search a
> RETURNING clause on SQL, cut this part, execute, do other things to
> get the ID and paste the RETURNING again.  :)

Nono.

What I have in mind is the following:

Add pfRefreshUpdate/pfRefreshInsert for ProviderFLags

Then:

a) If the DB engine supports RETURNING clause (it seems 3 engines do, 3 don't),
    that will be used to fetch the values. This is the most optimal approach.

b) For engines that do not support "returning" there is the option to specify a refresh SQL statement.
    (coincidentally: these are the engines that use autoInc type fields instead of sequences)

    the 'last_insert_id' parameter for this statement will be detected (using the reserved name
    or the name of the ftAutoInc field) and filled with the contents of the last generated ID.

    This if the engine supports getting the last insert id, this refresh statement will be constructed
    and automatically used in 2 cases:

    1) if pfRefreshUpdate/pfRefreshInsert is found in the list of fields.
    2) if no pfRefreshUpdate/pfRefreshInsert is found in the list of fields, but there is 1 ftAutoInc field and
       the database engine supports getting the last generated ID.

That should cover most, if not all, cases where a refresh is needed, with minimal 
interference of the programmer...

Michael.