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

[Marcos Douglas]

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.

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


Marcos Douglas