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

[Marcos Douglas]

On Fri, Nov 14, 2014 at 2:11 PM, Michael Van Canneyt
<michael at freepascal.org> wrote:
>
>
> 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.

OK

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

This option, to specify a SQL statement, is the programmer that write
the SQL? This should come from driver, don't?

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

OK

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

Looks good.


Marcos Douglas