Getting last id from firebird lazarus / 16-Databases ---------------------------------------------------------------------- mangakissa · 2013-12-06 19:12 Referring to this topic http://forum.lazarus.freepascal.org/index.php?topic=14456.0 Has SQLdb the functionality to get the last ID after inserting a record. I know you can generate a insertSQL, but can SQLdb handle the returned parameter? This topic http://forum.lazarus.freepascal.org/index.php?topic=21240.15 talks about a new TSQLQuery with a insert query and a execSQL, but it's not the same. I need a way to get the last id from Firebird. ---------------------------------------------------------------------- otorres · 2013-12-06 19:52 Use the stored procedures, clause Returning Into :Variable, this variable is a output variable. ---------------------------------------------------------------------- goodname · 2013-12-06 20:22 If your using Firebird then TSQLQuery.Open should work with with RETURNING. Code: [Select]SQLQry.SQL.Text:='INSERT INTO tbl(fld)VALUES(1)RETURNING id'; SQLQry.Open; //edit ShowMessage('The inserted id is '+SQLQry.Fields[0].AsString); After open just read the result set. ---------------------------------------------------------------------- mangakissa · 2013-12-06 21:40 Code: [Select]SQLQuery1.SQL.Text := 'SELECT * FROM usertable'; SQLQuery1.Open;If I insert a record, how do I return my LASTID? ---------------------------------------------------------------------- Giu · 2013-12-06 21:43 Quote from: mangakissa on December 06, 2013, 10:40:46 pmCode: [Select]SQLQuery1.SQL.Text := 'SELECT * FROM usertable'; SQLQuery1.Open;If I insert a record, how do I return my LASTID? http://www.firebirdfaq.org/faq243/ ---------------------------------------------------------------------- otorres · 2013-12-06 21:43 Read the documentation http://www.firebirdsql.org/refdocs/langrefupd21-insert.html ---------------------------------------------------------------------- exdatis · 2013-12-07 07:54 I use view to find new id, before post: CREATE VIEW VNEW_ID(   NEXT_ID) AS SELECT     GEN_ID(GEN_SOME, 1)   FROM RDB$DATABASE ---------------------------------------------------------------------- Graeme · 2013-12-07 22:23 As someone that has worked for more years than I can remember with database applications.... I urge you to forget about the "auto increment" ID fields. Rather let your application generate a unique ID and insert it with the rest of the record data. eg: I use GUID's as they are 100% unique ever across the whole database - which is sometimes quite handy. There are many other options too, like the High/Low Integer algorithm introduced by Scott Ambler. tiOPF has a sample implementation of that. I also suggest you treat ID fields as just that - a non user visible unique ID for a record. Never seen my the end-user. ---------------------------------------------------------------------- marcov · 2013-12-07 22:41 TSQLQuery is also a designtime component, and has a insertsql property. Does returning work there? Not all database work is manual. ---------------------------------------------------------------------- goodname · 2013-12-08 16:40 Quote from: Graeme on December 07, 2013, 11:23:28 pmAs someone that has worked for more years than I can remember with database applications.... I urge you to forget about the "auto increment" ID fields. Rather let your application generate a unique ID and insert it with the rest of the record data. As someone that has worked with databases for a long time I suggest using auto increment fields when you need a hidden primary key. It is possible for two different applications to generate the same number at the same time and overwrite each others work. Getting the database to generate the key eliminates this issue. Unless you can guarantee the application will generate a globally unique id. Quote from: marcov on December 07, 2013, 11:41:39 pmTSQLQuery is also a designtime component, and has a insertsql property. Does returning work there? Not all database work is manual. It would be interesting if TSQLQuery.InsertSQL.Text could have a custom insert statement using RETURNING and capture returned data. I don't think anyone has created such a result set. The only way I know is setting the TSQLQuery.SQL.Text with a Insert ... Returning statement and then using TSQLQuery.Open to get a result set. ---------------------------------------------------------------------- Graeme · 2013-12-08 18:40 Quote from: goodname on December 08, 2013, 05:40:08 pmAs someone that has worked with databases for a long time I suggest using auto increment fields when you need a hidden primary key. It is possible for two different applications to generate the same number at the same time and overwrite each others work. Getting the database to generate the key eliminates this issue. Unless you can guarantee the application will generate a globally unique id. Read my message again. I said I use GUID's - they are as guaranteed unique as you get! The High/Low Integer algorithm is pretty damn good too, but requires more effort. The problem with auto-increment is what this thread is about. How to retrieve that value the instant you inserted a record. Why? Because say you have a master-detail set of records that must all be inserted in one transaction. You can't insert the detail records until you know the PK (ID field) of the master record. And as this thread mentions, not all DB components or database servers/engines return the value of the auto-increment field, or you have to try and retrieve it as best you can after the master record insert, but before the detail records insert. Just a terrible mess! Use GUID's as the ID (PK) value and everything can be done on the client side, all in one transaction, guaranteed no conflicts, works with all databases, and no need for back-and-throw queries. ---------------------------------------------------------------------- marcov · 2013-12-08 19:29 Quote from: Graeme on December 07, 2013, 11:23:28 pmeg: I use GUID's as they are 100% unique ever across the whole database How do you generate GUIDs in a cross-database way that are guaranteed unique in the database (and thus uniqueness in a system is not enough, since I might restore the database to a second system, or might be part of a cluster) ---------------------------------------------------------------------- goodname · 2013-12-08 20:17 Graeme: I did mention that if the application could generate a globally unique id then it could be done that way. Most databases I've worked with will reserve auto generated numbers within a single transaction. It is guaranteed by the database engine that other transactions will get a different number. Can you tell me which engines don't behave this way. It is not difficult to get the generated id and insert it into a prepared detail insert. Generating a globally unique id seams a little harder to me. The only problem arises when you are working with multiple database engines as there is no standard cross database way of getting the auto generated id. If your having to deal with multiple database engines then you may have a point. Code: [Select]START TRANSACTION; INSERT INTO mastertbl(fld)VALUES(1)RETURNING id; Client side opens result set returned by insert and puts the value into detail insert query. INSERT INTO detailtbl(foreignKey,fld)VALUES(:id,1); END TRANSACTION; The following is a trick that works well with PostgreSQL. No need to get the id on the client side or generate a globally unique id. The currval function returns the auto generated id for the current transaction. I'm sure something similar can be done with other database engines. Code: [Select]START TRANSACTION; INSERT INTO mastertbl(fld)VALUES(1); INSERT INTO detailtbl(foreignKey,fld)VALUES(currval('mastertbl_id_seq'),1); END TRANSACTION; ---------------------------------------------------------------------- marcov · 2013-12-08 20:32 Quote from: goodname on December 08, 2013, 09:17:12 pmThe following is a trick that works well with PostgreSQL. No need to get the id on the client side or generate a globally unique id. The currval function returns the auto generated id for the current transaction. I'm sure something similar can be done with other database engines. Code: [Select]START TRANSACTION; INSERT INTO mastertbl(fld)VALUES(1); INSERT INTO detailtbl(foreignKey,fld)VALUES(currval('mastertbl_id_seq'),1); END TRANSACTION; Afaik Oracle (where postgresql afaik got most of the inspiration for Sequences) and Firebird (generator) have similar concepts. ---------------------------------------------------------------------- janasoft · 2013-12-09 09:21 Quote from: Graeme on December 08, 2013, 07:40:21 pm The problem with auto-increment is what this thread is about. How to retrieve that value the instant you inserted a record. Why? Because say you have a master-detail set of records that must all be inserted in one transaction. You can't insert the detail records until you know the PK (ID field) of the master record. And as this thread mentions, not all DB components or database servers/engines return the value of the auto-increment field, or you have to try and retrieve it as best you can after the master record insert, but before the detail records insert. Just a terrible mess! I think there's a solution for this problem: you can get the value of the autoincrement field on the BeforeInsert event. So you have no problems to applied it in the master-detail relation. I don´t see any problem in this approach, but perhaps I'm wrong ... ---------------------------------------------------------------------- mangakissa · 2013-12-09 10:19 The database engine must do the work for you, not the other way. ---------------------------------------------------------------------- taazz · 2013-12-09 13:49 Quote from: marcov on December 08, 2013, 08:29:52 pmQuote from: Graeme on December 07, 2013, 11:23:28 pmeg: I use GUID's as they are 100% unique ever across the whole database How do you generate GUIDs in a cross-database way that are guaranteed unique in the database (and thus uniqueness in a system is not enough, since I might restore the database to a second system, or might be part of a cluster) Think windows GUIDs the algorithm to create them is pseudo unique, well up to now I haven't any conflicts my self from different computers users and what not although I prefer the auto inc my self. ---------------------------------------------------------------------- Zoran · 2013-12-09 14:01 Quote from: marcov on December 08, 2013, 09:32:43 pmQuote from: goodname on December 08, 2013, 09:17:12 pmThe following is a trick that works well with PostgreSQL. No need to get the id on the client side or generate a globally unique id. The currval function returns the auto generated id for the current transaction. I'm sure something similar can be done with other database engines. Code: [Select]START TRANSACTION; INSERT INTO mastertbl(fld)VALUES(1); INSERT INTO detailtbl(foreignKey,fld)VALUES(currval('mastertbl_id_seq'),1); END TRANSACTION; Afaik Oracle (where postgresql afaik got most of the inspiration for Sequences) and Firebird (generator) have similar concepts. Yes, and sequences/generators are not isolated by transactions. When a value is taken from sequence/generator, it is lost forever, no matter if current transaction succeeds or not. Therefore, the uniqueness (and nothing else but uniqueness, you can get "holes" in your data generated this way) is globally guaranteed. ---------------------------------------------------------------------- Graeme · 2013-12-10 21:47 Quote from: marcov on December 08, 2013, 08:29:52 pmHow do you generate GUIDs in a cross-database way that are guaranteed unique in the database Functionality is built into Free Pascal. Code: [Select]function tiCreateGUIDString: string; var   lGUID: TGUID; begin   // Delphi support code omitted for this post, but that is one extra line of code   CreateGUID(lGUID);   Result := GUIDToString(lGUID); end; we then have a GUID OID generator that strips off the characters from the GUID we don't want.... Code: [Select]var   LValue: string; const   cGUIDLength = 38; begin   Assert(AAssignTo.TestValid(TtiOID), CTIErrorInvalidObject);   LValue := tiCreateGUIDString;   //           10        20        30   // 01234567890123456789012345678901234567   // {81A9C48C-DEF3-11D6-81C4-0002E31296EB}   // A GUID will be 38 chars long when created,   // or 36 chars long when {} are removed.   if (LValue[1] = '{') and     (LValue[cGUIDLength] = '}') then     LValue := Copy(lValue, 2, cGUIDLength - 2);   AAssignTo.AsString := LValue; end; Obviously all this is hidden from the developer if they use tiOPF, as the tiOPF framework takes care of generating the ID values for you. You simply need to tell it which OID generator to use, but the framework defaults to the GUID one. ---------------------------------------------------------------------- Graeme · 2013-12-10 21:49 Quote from: mangakissa on December 09, 2013, 11:19:09 amThe database engine must do the work for you, not the other way. I treat a database just like a treat other storage (example a INI or XML file). It is simply a place to store data for later retrieval. This way of thinking means my applications are very portable to other platforms and database servers. I don't rely on DB specific features (eg: auto-inc fields) and don't use stored procs. ---------------------------------------------------------------------- marcov · 2013-12-11 09:56 Quote from: Graeme on December 08, 2013, 07:40:21 pmThe problem with auto-increment is what this thread is about. How to retrieve that value the instant you inserted a record. Why? Because say you have a master-detail set of records that must all be inserted in one transaction. You can't insert the detail records until you know the PK (ID field) of the master record. Yes so begin transaction insert master get masteridinsert child1 with masteridinsert child2 with masteridcommit or rollback hereAll within one transaction As long as there is a way to get the masterid guaranteed for exactly that insert statement, you are good.  Doesn't matter if it is done via generators/sequences or via returning. QuoteAnd as this thread mentions, not all DB components or database servers/engines return the value of the auto-increment field, or you have to try and retrieve it as best you can after the master record insert, but before the detail records insert. Just a terrible mess! True. But at least Oracle, interbase/Firebird, postgresql and MSSQL >= 2005 have some way to do it. That basically leaves mysql. QuoteUse GUID's as the ID (PK) value and everything can be done on the client side, all in one transaction, guaranteed no conflicts, works with all databases, and no need for back-and-throw queries. But that precludes inserts from stored procedures reacting on triggers. ---------------------------------------------------------------------- mse · 2013-12-11 10:35 Quote from: Graeme on December 08, 2013, 07:40:21 pmHow to retrieve that value the instant you inserted a record. MSEgui has the flags "pf1_refreshinsert" and "pf1_refreshupdate" in "tmsefield.providerflags1" for the purpose. It uses the "returning" option of the DB and updates the field value automatically after record insert or update.  The numeric primary key field will be updated automatically by insert if no field has "pf1_refreshinsert" and the DB suports "lastinsertid". Another possibility is to use a "tsequencelink" which automatically fetches an ID from DB before inserting a record. ---------------------------------------------------------------------- mangakissa · 2013-12-12 08:34 MSEgui doesn't use SQLdb for connection databases. I thing the title is a little bit misplaced. The purpose is to get the last id created by the generator and get it back into the dataset without refreshing the table. The most simple thing of SQLdb is to refresh the inserted record only, so the created id is known after. Now you have to refresh the entire queryset and you don't like it if it has a lot of records dependent  the packetrecord property set. The whole problem of applyupdates is more than one records can be inserted / updated. I think 'INSERT INTO table () values () returning ' can't work with more records at once. ---------------------------------------------------------------------- marcov · 2013-12-12 08:57 Quote from: Graeme on December 10, 2013, 10:47:47 pmQuote from: marcov on December 08, 2013, 08:29:52 pmHow do you generate GUIDs in a cross- database way that are guaranteed unique in the database Functionality is built into Free Pascal. Those are afaik only guaranteed unique for one machine. Not for clusters/migrated db. ---------------------------------------------------------------------- otorres · 2013-12-12 13:44 Quote from: marcov on December 12, 2013, 09:57:59 amQuote from: Graeme on December 10, 2013, 10:47:47 pmQuote from: marcov on December 08, 2013, 08:29:52 pmHow do you generate GUIDs in a cross-database way that are guaranteed unique in the database Functionality is built into Free Pascal. Those are afaik only guaranteed unique for one machine. Not for clusters/migrated db. GUID or UUID is guaranteed of a UniqueGUID, The number of possible UUIDs is 340,282,366,920,938,463,463,374,607,431,768,211,456 (1632 or 2 128), or about 3.4 × 1038 ---------------------------------------------------------------------- howardpc · 2013-12-12 16:35 Quote from: otorres on December 12, 2013, 02:44:46 pmGUID or UUID is guaranteed of a UniqueGUID, A chance of 1 in 3.4 × 1038 that a GUID might be duplicated on that machine is indeed a very low probabiblity. However, it is not a guarantee. That would require a duplication probability of zero. ---------------------------------------------------------------------- mse · 2013-12-12 19:10 Quote from: mangakissa on December 12, 2013, 09:34:23 amMSEgui doesn't use SQLdb for connection databases. MSEgui uses a modified version of sqldb. I assume if desired the "refresh" functionality could be backported. For the Free Pascal team it is even simpler to implement because they can change db.pas which I couldn't at that time. QuoteThe whole problem of applyupdates is more than one records can be inserted / updated. I think 'INSERT INTO table () values () returning ' can't work with more records at once. AFAIK there is a separate INSERT statement for every inserted record in applyupdates. ---------------------------------------------------------------------- otorres · 2013-12-12 19:26 Quote from: howardpc on December 12, 2013, 05:35:05 pmQuote from: otorres on December 12, 2013, 02:44:46 pmGUID or UUID is guaranteed of a UniqueGUID, A chance of 1 in 3.4 × 1038 that a GUID might be duplicated on that machine is indeed a very low probabiblity. However, it is not a guarantee. That would require a duplication probability of zero. not is on machine, is on the world machines, if you use the UUID generator on the Firebird server, is almost impossible to repeat. ---------------------------------------------------------------------- BigChimp · 2013-12-12 19:57 Could somebody please explain to me the difference between a GUID derived from Firebird, Oracle, MSSQL Server or a GUID gotten from the OS which FPC presumably uses? Both should be equally unique or not unique. If they're not, that's a bug. ---------------------------------------------------------------------- marcov · 2013-12-12 20:04 Quote from: BigChimp on December 12, 2013, 08:57:46 pmCould somebody please explain to me the difference between a GUID derived from Firebird, Oracle, MSSQL Server or a GUID gotten from the OS which FPC presumably uses? Both should be equally unique or not unique. Most GUIDs are guaranteed within a certain domain (say a machine), and only improbable outside. If the GUID is not within the domain of the DB, moving the db to a different system (or chaining together different machines) means the result is not guaranteed unique anymore.  IOW if you use GUIDs in your database, you want to do that within the domain of that database, not some unrelated machine it just happens to run on. QuoteIf they're not, that's a bug. There is a difference between intention and practical implementation. The only way to absolutely make sure that GUIDs are never duplicate is to keep a worldwide centralized list of all issued GUIDs, which I hope I don't have to explain is impractical. ---------------------------------------------------------------------- BigChimp · 2013-12-12 20:16 Yes, there are several sorts of UUIDs: https://en.wikipedia.org/wiki/UUID I wonder whether "most" are not guaranteed globally unique but only within a domain, but that's not really the point. Regardless, if you have GUID conflicts in your db you should handle them the same as any other kind of replication/duplicate conflict, wouldn't you? E.g. when using a "normal" integer artificial PK, on multiuser systems, users could enter the exact same data twice; if you use unique constraints on the relevant candidate primary key fields you'd get an error anyway, even if the integers are different. You'd need to handle that sooner or later. In the GUID case, in adddition, you could then theoretically have the same GUID but wildly different data. You would use a similar mechanism of dealing with it. ---------------------------------------------------------------------- marcov · 2013-12-13 09:09 Quote from: BigChimp on December 12, 2013, 09:16:39 pmYes, there are several sorts of UUIDs: https://en.wikipedia.org/wiki/UUID I wonder whether "most" are not guaranteed globally unique but only within a domain, but that's not really the point. From wikipedia: In this context the word unique should be taken to mean "practically unique" rather than "guaranteed unique". QuoteRegardless, if you have GUID conflicts in your db you should handle them the same as any other kind of replication/duplicate conflict, wouldn't you? Depends on code and context. IF you check "does already exist in database" with a newly generated duplicate GUID, you won't notice, information just silently disappears. Specially with OPFs this is dangerous, since they often check a cache. The point is however that Graeme suggests you replace a system that is guaranteed unique within the table (auto-increment) by one that isn't. (GUID). ---------------------------------------------------------------------- BigChimp · 2013-12-13 10:46 Quote from: marcov on December 13, 2013, 10:09:37 amThe point is however that Graeme suggests you replace a system that is guaranteed unique within the table (auto-increment) by one that isn't. (GUID). Ok - rather than the discussion abstract - as we're talking about Firebird here: 1. Firebird claims to deliver RFC4122 UUIDs but actually don't; see http://tracker.firebirdsql.org/browse/CORE-3238. They intend to/will change to type 4 RFC4122 (random) numbers on Posix. Strange decision, but your point stands here: if you use Firebird UUIDs, they're not guaranteed unique. 2. FPC GUID: http://www.freepascal.org/docs- html/rtl/sysutils/createguid.html => if operating system support exists, that will be used, otherwise a random number implementation.... Wonder what OSes are supported... Code: [Select]program guid; {$mode objfpc}{$h+} uses  Classes, Sysutils; var   OurGUID: TGUID; begin   CreateGUID(OurGUID);   writeln(GUIDToString(OurGUID)); end. Windows 7: {37CC070B-1ACF-48BA-AB0F-29C517FE1852} => Version 4 (random). Oops. Linux x64: {252BC126-CA9D-4B54-AB14-8E599D59AFF8} => Version 4 as well!? 3. Whichever way Graeme implements UUIDs (perhaps tiOPF has code for it, don't know): I'll let Graeme cover that. ---------------------------------------------------------------------- marcov · 2013-12-13 10:49 Quote from: BigChimp on December 13, 2013, 11:46:10 amQuote from: marcov on December 13, 2013, 10:09:37 amThe point is however that Graeme suggests you replace a system that is guaranteed unique within the table (auto-increment) by one that isn't. (GUID). Ok - rather than the discussion abstract - as we're talking about Firebird here: 1. Firebird claims to deliver RFC4122 UUIDs but actually don't; see http://tracker.firebirdsql.org/browse/CORE-3238. They intend to/will change to type 4 RFC4122 (random) numbers on Posix. Strange decision, but your point stands here: if you use Firebird UUIDs, they're not guaranteed unique. 2. FPC GUID: http://www.freepascal.org/docs- html/rtl/sysutils/createguid.html => if operating system support exists, that will be used, otherwise a random number implementation.... Wonder what OSes are supported... So then the practice is even worse then I assumed. I assumed they were guaranteed unique if obtained from the same source. (e.g. because of a timer factor) ---------------------------------------------------------------------- BigChimp · 2013-12-13 13:54 Apparently on Windows they happen to use OS functions that do create globally unique IDs, but yes... it's bad. I really don't understand the devs' unwillingness to even consider changing it (they complain about having to include external code etc) as having a globally unique ID is just about the point of using GUIDs/UUIDs in databases AFAIC. Perhaps it is/will be better in FB 3, don't know. BTW: would you happen to know which OSes do support non-random (version 4) UUIDs on FPC? ---------------------------------------------------------------------- marcov · 2013-12-13 15:21 Quote from: BigChimp on December 13, 2013, 02:54:11 pmApparently on Windows they happen to use OS functions that do create globally unique IDs, but yes... it's bad. I really don't understand the devs' unwillingness to even consider changing it (they complain about having to include external code etc) as having a globally unique ID is just about the point of using GUIDs/UUIDs in databases AFAIC. Perhaps it is/will be better in FB 3, don't know. BTW: would you happen to know which OSes do support non-random (version 4) UUIDs on FPC? No, I simply implemented the syscall in *BSD to keep them going, and that's about it. I don't even know why it was needed other than the autogenerate GUID for interfaces in Lazarus. Ask Joost or Michael. ---------------------------------------------------------------------- BigChimp · 2013-12-13 15:37 Ok, thanks. ---------------------------------------------------------------------- laguna · 2013-12-13 17:04 This for read Code: [Select]SELECT     GEN_ID(GEN_SOME, 0)   FROM RDB$DATABASE ---------------------------------------------------------------------- BigChimp · 2013-12-13 17:30 Quote from: laguna on December 13, 2013, 06:04:36 pmThis for read Code: [Select]SELECT     GEN_ID(GEN_SOME, 0)   FROM RDB$DATABASEBoring. See http://forum.lazarus.freepascal.org/index.php/topic,22883.msg135784.ht ml#msg135784 ---------------------------------------------------------------------- goodname · 2013-12-13 20:06 Updated for use with firebird.  Code: [Select]START TRANSACTION; INSERT INTO mastertbl(fld)VALUES(1); INSERT INTO detailtbl(foreignKey,fld)VALUES(GEN_ID(GEN_SOME, 0),1); END TRANSACTION; ---------------------------------------------------------------------- Graeme · 2013-12-13 23:26 Quote from: BigChimp on December 13, 2013, 02:54:11 pmApparently on Windows they happen to use OS functions that do create globally unique IDs, but yes... it's bad. You guys are missing the point. 1 in a couple million or billion (or probably more) chance of getting a duplicate!!! That odds are more than good enough for me. In the 10 years I have used GUID's as database ID fields, I haven't hit a single duplicate. Me crossing the road and getting hit by a car has far worse odds - and thank goodness that hasn't happened yet. Also, as what any good developer will do... If there is a conflict - handle it in your software! Generate another number and keep going. The changes of you generating two duplicate GUID's in a row is near infinity. As the saying goes: "Don't make a mountain out of a mole hill." You guys are looking for a  problem where there is none. GUID's are still far better than auto-inc fields as far as I'm concerned. Also I never said GUID's are your only alternative. If for some insane reason you are not happy with the randomness of GUID's, then implement another ID generator. The point is, generating them client side is simply much more convenient and portable than vendor auto-inc solutions (and hoping you can retrieve that value for detail records - db server and db components permitting). ---------------------------------------------------------------------- marcov · 2013-12-14 10:11 Quote from: Graeme on December 14, 2013, 12:26:23 amYou guys are missing the point. 1 in a couple million or billion (or probably more) chance of getting a duplicate!!! That odds are more than good enough for me. In the 10 years I have used GUID's as database ID fields, I haven't hit a single duplicate. Me crossing the road and getting hit by a car has far worse odds - and thank goodness that hasn't happened yet. Also, as what any good developer will do... If there is a conflict - handle it in your software! Generate another number and keep going. The changes of you generating two duplicate GUID's in a row is near infinity. That's the whole point, this DIY method is not entirely safe, and thus needs conflict code. (and even then there might be loopholes). While the alternative, just using autoincrement is just fine. A very minor portion of your db code gets a bit db specific (at least till all dbs implemented returning, which we are now close, the frameworks just need to catch up). Also you have to explain it to other people with access to the database, and hope they can source GUIDS in the same source (e.g. in their stored proceduires) IMHO your opinion is pre 2005 DIY sentiment when it was all harder. Maybe there are still valid usecases if you really desire 100% blind portability across databases at rock bottom level, but it goes to far to raise that to a general recommendation. Reinier: FreeBSD7 still generates type 1 (mac address) GUIDs. Probably because it is older, most systems stopped with that method because it makes machine tracking to easy. ---------------------------------------------------------------------- BigChimp · 2013-12-14 10:53 @Graeme: hi seagull, thanks for flying past this thread without bothering to actually read it and then dropping your contribution. Conflict resolution? Really? Interesting. ---------------------------------------------------------------------- mangakissa · 2013-12-14 13:20 Summary: Use GUID. But this is not usable to different OS.Use 'SELECT generator'. Get you own primary key without interfering triggers (in Firebird)Use 'SELECT  ... RETURNING @id'. Very usefull when you save your record separately.But the main purpose is: how do I get a last id using TSQLQuery with his main query. Code: [Select]TSQLQuery.SQL.Text := 'SELECT * FROM table'; ---------------------------------------------------------------------- taazz · 2013-12-14 13:42 use a before post event and use your 2nd solution to read the generator and put its value in the field. eg Code: [Select]SqlQuery1.FieldByname('Customer_ID').Value := GeneratorSQL.Fields[0].value; ---------------------------------------------------------------------- goodname · 2013-12-14 14:00 mangakissa: There are a number of choices for getting the hidden key which you have listed. The one choice that is not available is getting the key from the SELECT * FROM tbl; result set. This is just a jumble of everything and there is no way to reliably determine the new key. If your trying to find the new record in SELECT * FROM tbl; that matches the key use TSQLQuery.Locate() ---------------------------------------------------------------------- Graeme · 2013-12-14 17:02 Quote from: marcov on December 14, 2013, 11:11:50 amthe alternative, just using autoincrement is just fine. A very minor portion of your db code gets a bit db specific (at least till all dbs implemented returning, which we are now close, the frameworks just need to catch up). Like I said, I needed a solution 10 years ago. auto-inc sucked, and GUID's worked perfectly since then. QuoteMaybe there are still valid usecases if you really desire 100% blind portability across databases at rock bottom level, but it goes to far to raise that to a general recommendation. Auto-inc would never have worked for us, no matter how good the framework support was. We have 250+ franchise businesses - each running their own database. Monthly we import data from them into a "master" head-office database. Auto-inc fields are only good for a single table. We need world/global unique values when importing 250+ databases into a single one - all without having to worry about conflicting ID values. And if you wondered why we need to do this... for pulling nation wide stats at head-office. ---------------------------------------------------------------------- Graeme · 2013-12-14 17:06 Quote from: mangakissa on December 14, 2013, 02:20:42 pmUse GUID. But this is not usable to different OS. Uh? Our products (which use GUID's for all records) run under Windows, Linux and FreeBSD. No problems at all, and it 10 years, not a single conflict. We also have code that imports data into the database, and generates about 80,000 to 100,000 records all at once - again this has never produced a single conflict for us.