UUID - Unique Field

[rdonnay]

There was something I failed to address at our gathering this month.

The issue came up when Bobby Drakos was trying to update a record in a SQL cursor.
The cursor was not an updateable cursor because the filtering system used the LIKE() scalar function.
So when the application tried to update the record with DC_DbGather() it could not acquire a Lock on the record.

A workaround for this is to make sure that DC_DbGather() uses a SQL UPDATE statement to update the record.
Therefore I am writing a new function named DC_DbRecord2SqlUpdate() which will be called by DC_DbGather() in the event that SQL is needed to update the record.

There are a few caveats which must be addressed, however.

The SQL UPDATE command must use a WHERE clause to insure that the correct record gets updated.
To narrow down an update to a single record, it must know the name of a field that has a value which is UNIQUE to the entire table.
For example, the unique number in a Drivers database would be the HACK.

Therefore the function would be called like this:

cStatement := DC_DbRecord2Update( oRecord, ‘MN_DRV’, ‘HACK’)

That statement would be executed via an SQL Execute function.

In SqlQuery.exe, however, this is a much bigger problem because I have no way of knowing which field is unique when doing an update.
Also, there are many tables in which there is NO unique field.

SQL updates REQUIRE a UNIQUE field, therefore you should all be preparing for this reality, even if you don’t anticipate using SQL UPDATE any time soon.

This is very simple.
Just add a field named UUID,’C’,36,0 to every table.
Every time you add a new record to the table, replace that field with the following code:

REPLACE UUID WITH uuidtochar(uuidcreate()) // example: b8b9f770-991f-4afb-aa65-6751acbcef25

If you use DC_DbGather() to add new records, I will automatically add the unique ID to the UUID field (if it exists).

This will make updates much easier going forward.

I will also be adding a new parameter to DC_DbGather() - <oSession>.
This will be a pointer to a DacSession() object to be used for the SQL UPDATE in the event that a record lock cannot be obtained.

I will also be creating a new class named DC_DacSession(). It will inherit from DacSession() but will contain some new iVars to make SQL work simpler.
For example, there will be an iVar to store a code block which will be called to execute any SQL statements.
This way, when you make a DacSession connection to your ADS or ODBC data, you can also define which SQL execute function to use.

To make it easier for you to add the UUID field to your tables, I will be adding a new feature to SqlQuery in the next update that will do this for you for all tables in a selected connection.

[Tom]

Besides this, a UUID is the best choice if any (internal) unique identifier is needed, but not shown to the user. If you want to match/connect tables, create any kind of n:m-relation, identify contacts or whatever - as long as it's only needed inside the app, use a UUID. This will end up struggling with numbers, tables collecting them, failing autoincrements and stuff like this. And it will even work if replication with a system is done which didn't have any connection to the database so far. A UUID is unique everywhere in the world, not only on your system.

[skiman]

Besides this, a UUID is the best choice if any (internal) unique identifier is needed, but not shown to the user. ...

Exact.

And if you ever want to use BOA to build an application in combination with SqlQuery, you can use that ID.

The combination of SqlQuery and BOA makes it possible for every developer to create solutions for customers who has an SQL database and want it in a webapplication. Now you can say 'Yes I can' to your customer.

[unixkd]

Good

How much will BOA cost ?

[rdonnay]

A UUID is unique everywhere in the world, not only on your system.

You don't want to get into an argument with Bobby Drakos about this.
He doesn't like the idea of UUID because it is NOT guaranteed to be unique in the world.
He claims that he got the same UUID from 2 different downloads of transactions from a credit card processor.

I tried to explain that UUID is a "practical application" for the small world in which we live but not in the entire world.
It's an "optimistic" way of looking at application development because the probably of a UUID collision is very remote.
Bobby says this isn't good enough for him.

My answer to him is that the only way the world can guarantee 100% uniqueness is if the seed of a UUID were based on one "atomic clock" which assigns all UUIDs. All computers in the world would be required to acquire their UUID from that clock. There is no other perfect solution. The military needs this but we, as business application developers, don't need it. UUIDs generated on our computers are good enough.

If you absolutely must insure that no duplicates can be created, it's simple enough to do the following:

Code: Select all

oRecord:UUID := uuidtochar(uuidcreate())
DO WHILE DbSeek(oRecord:UUID,,'UUID') 
   oRecord:UUID := uuidtochar(uuidcreate())
ENDDO

DC_DbGather(oRecord,.t.)

Additionally, for most applications, UUIDs don't need to be unique in the world or even in a single application.
They only need to be unique in each database (or table).

[Tom]

The chance of having the same two random UUIDs is 2^122, which is a number with 36 zeros. If Bobby got something looking like a random UUID twice, he a) maybe living in a different universe (which I always thought) or b) was wrong. Or he has confused a UUID with a UID.

[rdonnay]

The chance of having the same two random UUIDs is 2^122, which is a number with 36 zeros.

Not only is it nearly impossible to generate 2 UUIDs in the world that would collide, but it is even more improbable that there would be 2 UUID collisions on the same computer.

The most probable cause of such a failure of uniqueness is poor housekeeping on the part of the developer of the credit card processing company. They were not the best programmers as we discovered over the years. Or there could have been a failure in their Sql Server during an update or insert.

My guess is that they overwrote an existing record in a table with another record and then changed the numeric data while not changing the UUID. It makes sense that it was a software or hardware failure of some type.

[skiman]

Good

How much will BOA cost ?

Hi,

You can find all the information, documentation and price on https://www.boa-plaform.com