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

[Marcos Douglas]

On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt
<michael at freepascal.org> wrote:
>
>
> On Fri, 14 Nov 2014, Marcos Douglas wrote:
>
>> On Fri, Nov 14, 2014 at 10:39 AM, Michael Van Canneyt
>> <michael at freepascal.org> wrote:
>>>
>>>
>>>
>>> On Fri, 14 Nov 2014, Marcos Douglas wrote:
>>>
>>>> On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt
>>>> <michael at freepascal.org> wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Fri, 14 Nov 2014, Reimar Grabowski wrote:
>>>>>
>>>>>> On Thu, 13 Nov 2014 13:26:32 -0300
>>>>>> Marcos Douglas <md at delfire.net> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>> Take a look in Greyhound project: https://github.com/mdbs99/Greyhound
>>>>>>
>>>>>>
>>>>>>
>>>>>> I did and could not see how it helps.
>>>>>>
>>>>>>> Here you can see an example using "last id":
>>>>>>>
>>>>>>> https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285
>>>>>>
>>>>>>
>>>>>>
>>>>>> Yes, if you are using Greyhound, which I am not.
>>>>>>
>>>>>> So it looks like there's nothing in TSQLQuery that does the job for
>>>>>> me.
>>>>>> I now just use another TSQLQuery with the following SQL:
>>>>>>
>>>>>> SELECT LAST_INSERT_ID() AS SomeNameIChoose;
>>>>>>
>>>>>> Seems to work well, reduces the lines of code as I don't have to set
>>>>>> parameters (my primary goal), can be reused for other inserts (being
>>>>>> not
>>>>>> table dependent) and took about 1 Minute to implement.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Connections for databases that have lastinsertID you can call the
>>>>> method
>>>>> created for this:
>>>>>
>>>>> Function GetInsertID: int64;
>>>>>
>>>>> There is a plan to let SQLDB retrieve the value of some fields fro the
>>>>> DB,
>>>>> but it is not implemented yet.
>>>>
>>>>
>>>>
>>>> Hi Michael,
>>>>
>>>> To do that is need to implement a GetInsertID method in all drivers of
>>>> SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate:
>>>
>>>
>>>
>>> No. Not all SQL engines support lastID. Postgres and Firebird don't.
>>
>>
>> I know. For these the method returns 0 or the user need to code some
>> more to work.
>>
>> If a DMBS does not support transaction, for example, I can't implement
>> transactions on SQLdb? Is better to implement transactions for all
>> DMBS that works and do nothing in few cases that not supported. The
>> same for auto-inc, IMHO.
>
>
> No worries. We will implement it, but simply not in the way you suggest.
>
>>
>>> A more general mechanism is needed.
>>
>>
>> The code I posted is general, only in TSQLQuery.ApplyRecUpdate method.
>> In my example before, FLib instance (Greyhound's code) would be a
>> driver for SQLdb that will call GetLastAutoIncValue. A code for each
>> DBMS driver.
>
>
> 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.

> 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.
If you use auto-inc, the DMBS have support to return the last id on
your session.

> The only way to do this correctly is using a RETURNING clause when doing the
> insert.

This RETURNING clause works for all 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?

Marcos Douglas