How to obtain the auto generated primary key value after an insert

gururamnath · 2012-01-25 20:11:59

Hi,
I'm using the TSQLDBStatement's Execute function to do a insert of a record. I'm not sure how I can get the auto generated primary key value of the newly inserted record. Can anyone let me know how to get the value ?

Thanks,
Guru Kathiresan

ab · 2012-01-26 06:33:31

It will depend on the database used.

In order to be independent on the database used, for instance TSQLRestServerStaticExternal.EngineAdd (in SQLite3DB unit) gets the ID before inserting it to the database.
It is far from best efficient (since it needs to make a request to the DB before each insert), but it is working.

If you have some ideas about how to implement it in a generic way (not to be bound to a specific engine), I'm very interested!

Leander007 · 2012-01-26 12:43:29

I'm not really in database programming, but I think that for each "real" database engine could be specified best solution (Firebird has generators, MS SQL has @@IDENTITY, SQLite3 has ROWID...) for auto generated IDs. This approach is made with many frameworks, which just abstracts low level database specifics with high level objects.

Some other way is to do with common sql, issuing select statement immediately after insert as a "batch":

INSERT INTO Table1 (Field1) VALUES (Value1);
SELECT max(ID) FROM Table1;

But for the second one, I think that all engines does not allow batch queries (exotic ones and if I remember correctly I couldn't do batch queries with IBX components for Firebird database) and atomicity is in question too (locking, transactions ?!).

Last edited by Leander007 (2012-01-26 13:10:42)

ab · 2012-01-27 11:47:36

What is implemented in TSQLRestServerStaticExternal.EngineAdd is:
- SELECT max(ID)
- INSERT with this ID
- protected by a critical section
So insertion will always work if mORMot is the only point of access to the DB, but will fail on INSERT if another process did use this ID.

There is no generic way of implementing it for all DB providers...

Leander007 · 2012-01-27 16:15:54

Yes, this is the case (and not the rare one) what I'm pointing out. In real world database servers, you are not the only one who is accessing data. If you are having the benefit to have all control, that this is really rare case and you are really lucky .
So maybe is the time to push this forward (this is not only about the IDs) and do some lower layer which is tunned to the ground. So it is really opposite to the e.g. TSQLRecordMany approach (works everywhere but it is not optimized and not nature from the db point of view). It is really more work to be done, but will be better tuned if there is some abstraction introduced and lower level sql (or other dialect) is wrapped whit it.

Another case whit this is, that we somehow introduce more descriptive way of all data properties. Not only, if this is the unique field (stored), but all the info, is this unique field, how long it is (chars, this is not only database info, it is useful for automatic gui design too) etc. Whit newer delphis this could be easily done with attributes, but with older ones some structure (record or class) should be introduced.

Arnaud what are you thinking about this?

Last edited by Leander007 (2012-01-27 16:32:47)

ab · 2012-01-28 09:07:50

Leander007 wrote:

So maybe is the time to push this forward (this is not only about the IDs) and do some lower layer which is tunned to the ground.

SynDB units shall be enhanced - but they have most of the Zeos features by know.
What I see:
- Retrieve the number of an autogenerated ID;
- Batch update (at least for Oracle and MS SQL) - i.e. sending several rows of data at once for INSERT.

Leander007 wrote:

So it is really opposite to the e.g. TSQLRecordMany approach (works everywhere but it is not optimized and not nature from the db point of view).

I do not get why TSQLRecordMany is not "optimized from the DB POV"? It just use small SQL joined queries over indexed fields. It is very fast.
And the new automatic JOINed query (introduced in the trunk) can be very powerful. See http://blog.synopse.info/post/2011/12/0 … JOIN-query

Leander007 wrote:

It is really more work to be done, but will be better tuned if there is some abstraction introduced and lower level sql (or other dialect) is wrapped whit it.

If you have some more precise design patterns, I'm interested.

Leander007 wrote:

Another case whit this is, that we somehow introduce more descriptive way of all data properties. Not only, if this is the unique field (stored), but all the info, is this unique field, how long it is (chars, this is not only database info, it is useful for automatic gui design too) etc. Whit newer delphis this could be easily done with attributes, but with older ones some structure (record or class) should be introduced.

You can already specify the char size of fields (using the index keyword) for external tables - this is not needed for SQLite3.
IMHO attributes are not the ultimate magic either. They are not good enough to describe most complex DB layouts in the wild: see this comment.

Daniele Teti wrote:

Attributes are nice but are limited to simple mapping. How you can mapp 3 object in a simgle table with attributes mapping? How map a single object in 3 tables? External file mapping is very powerfull and extendible. However, Attributes could be used for simple mapping.

Since for our TSQLRecordMappedAutoID and TSQLRecordMappedForcedID classes (for mapping and reverse-engineering guidance from existing database content), attributes won't be sufficient.
So here are my thoughts:
- For simple mapping of new DB, current TSQLRecordExternal based mapping is enough and set all necessary parameters (no need for attributes);
- I may add attributes syntax (as additional) for newer version of Delphi, and allow the previous syntax for older Delphi - but it is just syntaxic sugar, not a real feature;
- The DB mapping will be probably on an external binary or JSON file, and - what I prefer - in a generated unit file in pure pascal code (this file may be overriden on next update) - just like the TLB and the associated .pas file;
- I'm enhancing our SynDBExplorer tool, in order to reverse a DB layout and create DB layout in pascal code directly.
I've a big Oracle project to reverse, with years of data and several DB layout patterns used - I think it will be a good candidate for this.

Leander007 · 2012-01-29 13:02:30

ab wrote:

I do not get why TSQLRecordMany is not "optimized from the DB POV"?

Naturally when you design the database relations first (not ORM tool), then you connect e.g. two tables with foreign keys (if it is feasible on the chosen database, if not then the TSQLRecordMany should be used) and put some rules on it (cascade delete...). So what is here better, you have two tables and you have some automatic rules on the lowest level, this is really good if you are connecting to the database with other tools too.

ab wrote:

If you have some more precise design patterns, I'm interested.

Not yet, because current project (simple layout, single connecting tool to the db) which I'm working on, does need none of discussed.

ab wrote:

IMHO attributes are not the ultimate magic either. They are not good enough to describe most complex DB layouts in the wild.

I'm aware of that. But for plain description of table (1:1) is enough, e.g. from the serialization point of view (like xml serialization) when you design your model from scratch (CreateMissingTables), not for connecting to some existing database, which could be complex...
I'm aware too, that this is only syntactic sugar, not a real feature, because such behavior, could be described as is with TSQLRecordExternal and even be read from some external source (xml files, schemas). For the last one, you are pointing out generated Pascal source, what could be very cool (JSON is fine for data, but not for human editing if this is introduced too).

ab wrote:

I've a big Oracle project to reverse, with years of data and several DB layout patterns used - I think it will be a good candidate for this.

. That would be the ultimate test .

Last edited by Leander007 (2012-01-29 13:04:03)

mORMot Open Source

#1 2012-01-25 20:11:59

How to obtain the auto generated primary key value after an insert

#2 2012-01-26 06:33:31

Re: How to obtain the auto generated primary key value after an insert

#3 2012-01-26 12:43:29

Re: How to obtain the auto generated primary key value after an insert

#4 2012-01-27 11:47:36

Re: How to obtain the auto generated primary key value after an insert

#5 2012-01-27 16:15:54

Re: How to obtain the auto generated primary key value after an insert

#6 2012-01-28 09:07:50

Re: How to obtain the auto generated primary key value after an insert

#7 2012-01-29 13:02:30

Re: How to obtain the auto generated primary key value after an insert

Board footer