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

Marcos Douglas md at delfire.net
Fri Nov 14 17:18:18 CET 2014


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

Of course.  :-)

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

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


Marcos Douglas




More information about the Lazarus mailing list