Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
Automatically populate a GUID field |
Mon, Jun 16 2014 2:44 AM | Permanent Link |
Adam H. | Hi,
Is it possible to have EDB populate a GUID field the same way as an Autoinc (when a record is created)? I've attempted to do this by ticking 'Generated' and setting the 'Generate as expression' as Current_Guid, but every time a record is altered, it changes the GUID value automatically. I thought maybe I got "computed" and "generated" mixed up, but that doesn't appear to be the case. Am I misunderstanding the function of Generated. Should I be creating my GUID fields from within Delphi instead on the OnNewRecord event as I did in DBISam? Thanks & Regards Adam. |
Mon, Jun 16 2014 3:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Is it possible to have EDB populate a GUID field the same way as an >Autoinc (when a record is created)? Yes and No The Yes part: just set the columns default to CURRENT_GUID The No part: GUIDs are not sequential just unique (at least reasonably so) Its probably this difference which has stopped you spotting the obvious solution <vbg> ElevateDB has a few more CURRENT_s than DBISAM - worth looking up Roy Lambert |
Mon, Jun 16 2014 12:56 PM | Permanent Link |
Adam Brett Orixa Systems | Adam
A way around this is to use the following definition for your GUID field: ADD COLUMN SomeField GUID DEFAULT Current_GUID or if you need something more customizable: ADD COLUMN SomeField GUID DEFAULT GenerateGUID() -- Then if needed create the new function "GenerateGUID" CREATE FUNCTION "GenerateGUID" () RETURNS GUID COLLATE "AFK" BEGIN DECLARE aGUID GUID; --You can put any custom code in here if you like. SET aGUID = Current_GUID; RETURN aGUID; END -- In this way the column is given the GUID as a default, rather than "generated" so it won't be changed on every POST. |
Mon, Jun 16 2014 6:23 PM | Permanent Link |
Adam H. | Hi Roy,
Thanks for your reply... > The Yes part: just set the columns default to CURRENT_GUID> > The No part: GUIDs are not sequential just unique (at least reasonably so) > > Its probably this difference which has stopped you spotting the obvious solution <vbg> OK - what confuses me is this: if I have a table with Autoinc's (or integers that are generated) and I have 10 records, I have primary key values of 1 to 10. Then, if I go back and Edit record 5, it doesn't change it's value to 11. (The next autoinc field). It appears as though it recognises that the value has been previously generated, and doesn't attempt to change it. So I don't understand why GUID's are any different? I understand that they're not sequential, but I have no idea why it appears to work differently for integers as it does GUID's. > ElevateDB has a few more CURRENT_s than DBISAM - worth looking up Thanks for that. I've done a search through the edb2sql help file, and have only found: current_guid current_timestamp current_user current_database current_date current_computer current_time .... nothing that would appear to help me with the issue I'm facing. :-/ Cheers Adam. |
Mon, Jun 16 2014 6:28 PM | Permanent Link |
Adam H. | Hi Adam,
> A way around this is to use the following definition for your GUID field: > > ADD COLUMN SomeField GUID DEFAULT Current_GUID Thanks for that. At present I'm using EDBManager to create my tables. I'm guessing that what you're proposing above can't be done through EDBManager? (As I can't see any other options for it). I guess I'm still trying to get my head around the whole Generated concept. > or if you need something more customizable: > > ADD COLUMN SomeField GUID DEFAULT GenerateGUID() > > -- > > Then if needed create the new function "GenerateGUID" > > CREATE FUNCTION "GenerateGUID" () > RETURNS GUID COLLATE "AFK" > BEGIN > DECLARE aGUID GUID; > --You can put any custom code in here if you like. > SET aGUID = Current_GUID; > RETURN aGUID; > END Very interesting. At this stage I don't need anything more customisable, but it's very interesting to see what could be done. Thanks Adam. |
Tue, Jun 17 2014 3:28 AM | Permanent Link |
Uli Becker | Adam,
I can't help you with the general differences between Integer and GuiD regarding generated fields, but what I do to "generate" a GUID that won't change is a trigger: SCRIPT BEGIN EXECUTE IMMEDIATE 'CREATE TRIGGER "CreateGUID" BEFORE INSERT ON "TestTable" BEGIN SET NewRow.MyGUID = CURRENT_GUID; END'; END Uli |
Tue, Jun 17 2014 4:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>OK - what confuses me is this: > >if I have a table with Autoinc's (or integers that are generated) and I >have 10 records, I have primary key values of 1 to 10. > >Then, if I go back and Edit record 5, it doesn't change it's value to >11. (The next autoinc field). It appears as though it recognises that >the value has been previously generated, and doesn't attempt to change it. > >So I don't understand why GUID's are any different? I understand that >they're not sequential, but I have no idea why it appears to work >differently for integers as it does GUID's. OK I can see that as well, and I'm not sure of the answer, however, I'll try and answer it below. To solve your current problem simply use the default page and set CURRENT_GUID as the default. That way the field will pick up its value when the record is created and will not change. You can make teh default tab visible by making sure neither Generated nor Computed are checked. Right here goes for the answer, and to tell you how to use GENERATED/COMPUTED to do what it is you want. GENERATED / COMPUTED are identical but for one element - GENERATED stores the calculated result on disk as part of the physical table, COMPUTED doesn't (I hope I have that the right way round but I get confused with it) In both cases you are telling the engine to calculate the contents of the field as and when the record is changed. This is each and every time the record is changed, it doesn't matter what has changed the calculation will be performed. So if you use CURRENT_GUID as the sql for a GENERATED/COMPUTED column then every time you change the record that column will pick up the value of CURRENT_GUID. That explains why you can see it changing. It seems that part of your confusion is caused by the behaviour of "autoincs". If you look at the column definitions "ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) "Tst2" GUID COLLATE "ANSI_CI" GENERATED ALWAYS AS CURRENT_GUID then you can see the difference. Its "AS IDENTITY" ie this is a special case. You can sort of do what you want by altering the sql used for the calculation eg "Tst2" GUID COLLATE "ANSI_CI" GENERATED ALWAYS AS IF(Tst2 is null,CURRENT_GUID,tst2), which is pretty much (I suspect) what setting the default does. Roy Lambert |
Tue, Jun 17 2014 2:13 PM | Permanent Link |
Barry | "Adam H."
I'm not sure why you want to generate the GUID instead of using the Default value. Perhaps you want the GUID as soon as the table goes into Insert mode? Try this: Generated: MyCol = IFNULL(MyCol, Current_GUID(), MyCol) So it only generates MyCol if it is NULL, otherwise it uses the existing MyCol value that was originally generated. Normally I would use Default value of: MyCol = Current_GUID() Or if I want to know the GUID prior to adding the row, I have Delphi get a Current_GUID() and insert that into the column with the reset of the column values. Barry |
Tue, Jun 17 2014 6:16 PM | Permanent Link |
Adam H. | Hi Uli,
Thanks for that script. It's interesting looking at the different ways that people go about accomplishing this. Cheers Adam. |
Tue, Jun 17 2014 6:19 PM | Permanent Link |
Adam H. | Hi Roy,
Thanks again for your help... > OK I can see that as well, and I'm not sure of the answer, however, I'll try and answer it below. > > To solve your current problem simply use the default page and set CURRENT_GUID as the default. That way the field will pick up its value when the record is created and will not change. You can make teh default tab visible by making sure neither Generated nor Computed are checked. Aah - that's where I was going wrong. Thank you - I shall try this. I guess where I messed up is that I've been using AutoIncs in EDB - so I'm used to 'that' way - and when I designed a small app with GUID's - I was going through the same routine. Now that I know that Autoinc and GUID's have completely different 'rules' (for lack of a better word) when it comes to automatically generating / defaulting their value. Thank you! Adam |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |