Skip to main content
adey-21-Visitor
1-Visitor
March 27, 2017
Question

unable to fetch data table entries with a string primary key

  • March 27, 2017
  • 6 replies
  • 4617 views

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

jkaczynski1-Visitor
1-Visitor
March 27, 2017

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.

    adey-21-VisitorAuthor
    1-Visitor
    March 27, 2017

    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.  
    5. 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??

    jkaczynski1-Visitor
    1-Visitor
    March 27, 2017

    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.

    nkhose1-Visitor
    1-Visitor
    March 27, 2017

    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-21-VisitorAuthor
    1-Visitor
    March 29, 2017

    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

    ptc-62921031-Visitor
    1-Visitor
    March 30, 2017

    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.

    Terms & ConditionsAccessibility statement