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

[Graeme Geldenhuys]

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

  http://tiopf.svn.sourceforge.net/viewvc/tiopf/tiOPF2/Trunk/Options/tiOIDInteger.pas?revision=2087&view=markup


Hope this helps a bit.

-- 
Regards,
  - Graeme -


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