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

[Michael Van Canneyt]

On Fri, 14 Nov 2014, silvioprog wrote:

> On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt <michael at freepascal.org> 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.
>       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.
> 
> 
> Indded.
>  
>       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)
> 
> 
> Just a suggestion: it would be nice to implement it using interface or abstract class (like ConnectionDef). So if
> a database X does not provide the RETURNING support, a "mortal programmer" could do that by himself on his own
> project. =D

I was planning some fallback mechanism, yes. 
And a "class function SupportReturningClause : Boolean" or something similar.
Or maybe

TDBCapability = (lastID,returningclause);
TDBCapabilities = set of TDBCapability;

and

Class function DatabaseCapabilities : TDBCapabilities;

I don't really like using interfaces when things can be done more simple.

Michael.