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

Community Tip - Visit the PTCooler (the community lounge) to get to know your fellow community members and check out some of Dale's Friday Humor posts! X

unable to fetch data table entries with a string primary key

adey-2
1-Newbie

unable to fetch data table entries with a string primary key

I have a data table with a string primary key. however, i store GUID in it. I am not able to fetch the data table entries in a service that takes the primary key's value as a parameter. I have something like this:

In one service:-

var guid = generateGUID();

            // log user request in DT

            var values1 = Things["UserRequests"].CreateValues();

            values1.Status = 0; // in progress

            values1.RequestID = guid; //GUID [Primary Key]

            values1.RetryCount = 0; //INTEGER

            values1.LoggedTime = new Date(); //DATETIME

            values1.VehicleID = vehicleID;

            var params1 = {

                sourceType: undefined,

                values: values1,

                location: undefined,

                source: undefined,

                tags: undefined

            };

            Things["UserRequests"].AddDataTableEntries(params1);

In another service that takes the request id:-

var values = Things["UserRequests"].CreateValues();

values.RequestID = RequestID; //STRING [Primary Key]

var params = {

  maxItems: undefined /* NUMBER */,

  values: values /* INFOTABLE*/,

  query: undefined /* QUERY */,

  source: undefined /* STRING */,

  tags: undefined /* TAGS */

};

// result: INFOTABLE

var result = Things["UserRequests"].QueryDataTableEntries(params);

result.length returns 0

6 REPLIES 6
jkaczynski
4-Participant
(To:adey-2)

Hello Anuria Dey​,

If you want to get entry by key, there is a better solution. It assumes, that you have only one primary key, it won't work if you have more primary keys on the Data Table:

var params = {

     key: "GUID"

}

var result = Things["RequestDataTable"].GetDataTableEntryByKey(params);

However, your way also should work correctly. I just copied your code and if I invoke the second service and pass correct RequestID, it works.

After you invoke first service, is a new entry to DataTable added?

Hope it helps.

Regards,

J.

Thanks Jakub for a prompt response!
I have tried this out as well.

The main problem i'm facing is that i pass the Request id i used to add a data table entry into another service, where i want to use it to fetch a particular entry from the table. what i observe is that maybe it does not detect the Request id as a string and hence does not fetch me any entry. When a hard code a guid like:

  1. var params = { 
  2.      key: "<any guid>" 
  3.  
  4. var result = Things["RequestDataTable"].GetDataTableEntryByKey(params);

it works and fetches the corresponding entry but not in case of the variable which i am supposed to use.

Is there any way i can convert it to a string??

jkaczynski
4-Participant
(To:adey-2)

Hi Anuria Dey​,

It depends on what type of object you have in the variable. You can try to log your variable to the Script log and check if it is correct, so:

logger.warn("Request ID: " + RequestID + " is of a type: " + typeof RequestID);

Additionally, to make a string from a basic types, you can try to invoke a toString() method on the variable, e.g.:

var keyString = RequestID.toString();

Hope it helps. If so, you can concern to mark my previous or this answer as a Correct Answer for other users to refer.

Regards,

J.

nkhose
1-Newbie
(To:adey-2)

Extending on this .. we can also accomplish by adding a subcription on datatable and select event update or add , pass in the id parameter which is the primary key set of your datashape and script as like.. .

if(eventData.id)

{

Things['yourDataTable'].SecondServiceInvoke({Primary_Id:eventData.id});

}


So above subcription will always invoke whennever you add or update the datatable in this case you may set the event as ADD .


Also to note eventData.id will always return the newly generated primary key whatever you have set int or GUID



adey-2
1-Newbie
(To:adey-2)

Sorry i forgot to mention that the second service where i am passing the request id(table's primary key) as parameter is an async service Seems like an issue with that. Also i am using ThingWorx 6.6 composer

Basically, the first service starts a new transaction, and changes made by that transaction are not available to other threads (async services are new threads) until the transaction closes.

You are much better off switching to a subscription instead of an async service. The subscription will fire when the event happens (Add, Update, Delete, ect), which will be after the transaction closes.

Top Tags