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.

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

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.

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.

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.

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

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

"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

Hi Uli,

Thanks for that script. It's interesting looking at the different ways
that people go about accomplishing this.

Cheers

Adam.

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