Generate GUID in SQL - Part 2

[Ubaidullah]

Hi Bruce,

Last time this was discussed (Generate GUID in SQL), the recommendation was that the GUID should be generated by the client program.

I am currently designing a database and the client will be developing their software on it using languages other than Clarion. There will also be a Nettalk Web app.

To avoid issues with GUID values coming from the other apps, I am leaning towards setting the GUID columns with a default of NEWID() on the server ( or left(replace(newid(),'-',''),16) to make the value similar to st.MakeGUID() ).

Question is, what do I need to do in Nettalk so it gets the GUID value correctly? Not sure if setting PROP:ServerAutoInc would be enough as the docs say that this value is reset every time and ADD is performed.

Regards,
Ubaidullah Nubar.

[Jane]

I think that at least part of the reason Bruce recommends against using SQL Uniqueidentifier GUIDs is so one is not tied to a specific backend.
And another is that if you need to generate a unique identifier in a disconnected mobile app, it's much easier to generate a 16-character random string using javascript than to generate a SQL GUID with javascript.

Also, it's advantageous to have the client generate the row's unique identifier.  That way, for example, you can create invoice items before you've saved an invoice header (because you have the invoice GUID as a variable).  So you generate the GUID *before* saving any rows, rather than having SQL create a NEWID on insert which you then need to try to get.

If you don't care about simplicity of creating the GUID with javascript and want to use the MS SQL uniqueidentifier you could use prop:sql to have SQL make one for you, or use String Theory, or use one of the various Windows API methods.  Some recent discussion on Clarion Hub: https://clarionhub.com/t/function-to-create-a-guid/6580/5

[Bruce]

>> To avoid issues with GUID values coming from the other apps, I am leaning towards setting the GUID columns with a default of NEWID() on the server ( or left(replace(newid(),'-',''),16) to make the value similar to st.MakeGUID() ).

you are leaning wrong.
Random string guids are easily created in any language.

The recommendation to generate the guid client side has not changed.

Cheers
Bruce

[Ubaidullah]

I was thinking of issues that can arise if the other developers don't generate the GUID correctly.

Most examples online (How to create a GUID / UUID in JavaScript ?) suggest making GUID of length 32 such as ?550e8400-e29b-11d4-a716-446655440000?.

Can StringTheory's MakeGuid generate 32 bit GUIDs to keep it consistent?

Regards,
Ubaidullah Nubar.

[Bruce]

>> I was thinking of issues that can arise if the other developers don't generate the GUID correctly.

If the other developers don't do lots of things correctly you will have issues. I hope you are thinking as hard about those things as you are about this one.

I feel like if they are unable to create a random string, then they will likely be unable to do all the other things you need them to do. Trying to "protect" them by creating a worse solution, and a bunch of problems elsewhere, does not sound like a useful approach to me. But hey, it's your system, do whatever you like.

In which case;
>> Question is, what do I need to do in NetTalk so it gets the GUID value correctly? Not sure if setting PROP:ServerAutoInc would be enough as the docs say that this value is reset every time and ADD is performed.

You would need to inspect all the generated code, templates, and classes, to see how auto-numbering is handled. I'm sure it's do'able. I'm sure it's also a complete waste of your time.

>> Most examples online (How to create a GUID / UUID in JavaScript ?) suggest making GUID of length 32 such as ?550e8400-e29b-11d4-a716-446655440000?.
That's not a length of 32. That's just a display field. The underlying data is 16 bytes long. It's like saying that a date in clarion is 10 bytes long (yyyy/mm/dd) because that's how you _see_ it - which has nothing to do with how it's stored.

>> Can StringTheory's MakeGuid generate 32 bit GUIDs to keep it consistent?

StringTheory's MakeGuid generates a guid 128 bits long. not 32 bit. Feel free to reformat it with a picture to display it in hex format, with hyphens if you like.

I fear you are projecting your own lack of skills onto your fellow programmers. If you specify that the field should contain a 16 byte random string, then that's all you need to do.

If you want a formal specification for the field see
https://www.capesoft.com/docs/NetTalk14/NetTalkApps.Htm#DataSyncDictionaryFields

Cheers
Bruce

[Ubaidullah]

Bruce,

Thanks for your detailed answer.

It's not that developers are unable to create a random string. I geeked out a bit on this and found that this is a real issue. Which is why I was attempting to generate the GUID on the server side.

https://web.archive.org/web/20190121220947/http://devoluk.com/google-chrome-math-random-issue.html

https://github.com/ramsey/uuid/issues/80

Also, some SQL docs consider UUIDs as a sub type of strings. But I understand what you are saying that it is really a 128 bit number represented as a hex string.

Anyway, I will go with your advise and leave the GUID generation on the client side. The field is going to be a primary key after all so I can recommend them to test for failure on insert.

Regards,
Ubaidullah Nubar.