6 Replies Latest reply on Mar 30, 2017 4:50 PM by chadha RSS
    adey-2 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

      • Re: unable to fetch data table entries with a string primary key
        jkaczynski-2 Creator

        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.

          • Re: unable to fetch data table entries with a string primary key
            adey-2 Newbie

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

              • Re: unable to fetch data table entries with a string primary key
                jkaczynski-2 Creator

                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.

            • Re: unable to fetch data table entries with a string primary key
              nkhose Creator

              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



              • Re: unable to fetch data table entries with a string primary key
                adey-2 Newbie

                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

                  • Re: unable to fetch data table entries with a string primary key
                    chadha Apprentice

                    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.