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

[Graeme Geldenhuys]

On 2014-11-13 14:07, Reimar Grabowski wrote:
> It seems to work but it's IMHO not very nice.
> Ideally I'd like to have TSQLQuery do it for me and offer me some property like SQLQuery.LastInsertID or something along those lines.
> Is there any way to get the new PK value without manually querying for the inserted record?

Do you have control over the database structure? If so, simply change
the "auto-increment" fields to standard Int or Int64 data type then use
the well known Ambler High/Low method to generate your identify values.
This method is also multi-user and multi-insert (multiple inserts one
after the other) friendly. See tiOPF's Options/tiOIDInteger.pas unit for
an implementation. tiOPF is available on SourceForge.

Alternative (my preferred solution) is to use a GUID as your identify
value. In is table and database unique, so opens up a world of opportunity.

There are SO MANY benefits to generating your own identify value BEFORE
you insert a record. I have worked for more years than I care to
remember with databases, and even with something as hugely popular as MS
SQL Server there is inherent issues with auto-increment fields.

My advice, avoid auto-increment as much as possible!

Regards,
  - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/