cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

system generated number for primary key in the datatable

PreetiGupta
14-Alexandrite

system generated number for primary key in the datatable

I want to generate sequential number for my number field in the data table. Is this possible?

thanks.

6 REPLIES 6

Hello,

I have done this before by creating a property on the datatable which I just increment by one each time I successfully add a new entry. The property serves to function just like a nextVal would within an actual database.

Hope this helps!

Tori

Hi,

A better solution for primary keys is to use the GUID type.

It's not incremential (that prevent bad joins and improve security) and you have a function to get a new GUID:

var uuid =  generateGUID();

Regards

jkaczynski
4-Participant
(To:rpallares)

Hi Rafael PALLARES,

The GUID is only theoretically​ unique. The better solution is suggested by Tori.

Hi PREETI GUPTA​,

If I have more than one DataTable that should have uniquely generated ID's, I'm usually creating another DataTable with fields:

  • DataTable name - here I store the name of DataTable I'm generating ID for,
  • ID - the incremented value of last ID.

And one service that gets the DataTableName parameter, returns appropriate ID and increments it the same time.

Regards,

J.

Hi,

In effect, the GUID object is only theorically unique (Universally unique identifier - Wikipedia)

BUT, that probability is :

Only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. Or, to put it another way, the probability of one duplicate would be about 50% if every person on earth owned 600 million UUIDs.

You have much more chances to reach the INTEGER or LONG type limit and have a collision.

I highlight the fact that all the ThinWorx system use GUID massively for its objects.

And how do you prevent concurrency accesses to your parameter you increment and use it as primary key?

I persist and I suggest to use a GUID object. That's made for it.

Regards.

jkaczynski
4-Participant
(To:rpallares)

Hello,

Yup, statistic for duplicate is 1 bilion UUIDs and Murphy's law is "during the first run on the production instance"

A well-written Java class and concurrency access should not be a problem. If implemented correctly, of course.

Anyway, I can agree with you regarding the issue of LONG MAX_VALUE limit. The biggest numeric type in Thingworx is represented by Double. I wonder when there will be implemented anything bigger, even simple BigDecimal.

Regards,

J.

Jakub Kaczynski could you please elaborate on concurrent access? Say there is a service on a template level which runs on a scheduled basis. The aim of the service is to create a record in a single datatable for each corresponding thing. Would incrementing a last order number (primary key) still work? Thanks.

Top Tags