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

Michael Van Canneyt michael at freepascal.org
Fri Nov 14 15:11:30 CET 2014



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

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

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)

Michael.




More information about the Lazarus mailing list