[fpc-pascal] How to insert a record and get the primary key with sqldb?

Graeme Geldenhuys graemeg.lists at gmail.com
Sat Oct 22 19:32:44 CEST 2011

On 22/10/2011, Felipe Monteiro de Carvalho wrote:
> Yet another chapter of my database problems =)

As this message thread proves, there are many issues with Auto-inc
field, so simply DON'T USE THEM!

You have many alternatives, which will save you LOTS of grey hairs.
Auto-Inc field are just evil, no matter which RDBMS you use.

1) Use a GUID created at the time you do the Insert. This also means
you can do Master/Detail records with no problems at all.

2) If you must use an Integer as a primary key, then simply implement
the Ambler High/Low method. Google will give you more information, but
in a nutshell it works as follows: You have a "next_id" table in the
database. When you application starts it reads the last used value,
books out say the next 10 numbers, and update the Next_ID table. This
now means you can insert 10 new records without needing to revisit the
Next_ID table, and nobody else will get those 10 numbers.

Here is an example implementation as done in tiOPF2


Hope this helps a bit.

  - Graeme -

fpGUI - a cross-platform Free Pascal GUI toolkit

More information about the fpc-pascal mailing list