On Mon, 24 Oct 2011, Reinier Olislagers wrote:
On 22-10-2011 15:33, michael.vancanneyt-0is9kj9s...@public.gmane.org wrote:
On Sat, 22 Oct 2011, Marco van de Voort wrote:
In our previous episode, Felipe Monteiro de Carvalho said:
Ok, now I want to insert a record in my table and I would like to
obtain the auto-generated PrimaryKey
This is a classic problem, since SQL simply doesn't support this.
So all DBs do something else, for postgresql there are sequence
objects that
can be queried, while other allow to return the id of the autogenerated
fields.
Afaik sqldb does not abstract this yet.
That's because it can't be abstracted correctly. There are 2
incompatible mechanisms.
1. autogenerated fields (mysql, MS SQL server) which must be retrieved
after the insert using a special API.
2. or sequences, which must be generated manually before the insert
(DB2, Oracle, Firebird) using a special
API, but which may or may not be generated in an AFTER INSERT trigger.
In which case it's impossible to retrieve the sequence value after
the insert except by re-reading the record.
Correct, but for option 2. - at least in Firebird - BEFORE INSERT
triggers are often used, not AFTER INSERT.
See eg http://www.firebirdfaq.org/faq29/
Typo on my part, sorry.
Also, using INSERT...RETURNING it's very well possible to get the
sequence value directly when inserting the data.
Yes, but not all DBs support this. We use Firebird a lot, and it didn't
support that construct until version 2.1 or so.
In each case: it's hard to abstract correctly, but nevertheless we'll try
and make managing this easier. Martin's ideas for this are useful.
What's funny is that I never understood how the MySQL/SQL-Server way could
correctly work. For example I have 2 tables, both with an auto-incremental
field.
Table 1 has an after insert trigger that does an extra insert in table 2.
When I do an insert in table 1 , what does 'last_insert_id' return ?
The value for table 2 or table 1 ?
The last inserted id for your connection is the one for table 2, but you
need/expect the last id for table 1 :-)
Michael.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
