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

[silvioprog]

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

E.g:

// implemented in FPC PQConnection.pas, using the RETURNING feature
TPGSequence = class(..., ISQLSequence)
..
  function GetLastId: int64;

// implemented in programmer project, using X feature
TXSequence = class(..., ISQLSequence)
..
  function GetLastId: int64;

So:

PQConnector1.SetSequencer(TPGSequence.Create);

or:

XYZConnector1.SetSequencer(TXSequence.Create);

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20141114/81f06fd6/attachment-0003.html>