2 Replies Latest reply on Oct 12, 2017 2:51 AM by karnoia RSS
    karnoia Newbie

    How can I use an Infotable as Input Parameter for a SQL Query?

    Good morning,

     

    I have been working with Thingworx for a while now, but I haven't been able to make this work. I have a service that gets data from a Stored Procedure, I show it on a Grid widget, modify and add new data, and I need to send it to SQL again. My problem is that I have all that data on a Infotable, and I haven't been able to pass that data to the stored procedure. My plan was to run through the infotable with a loop and a counter "EditedGridData.rows[counter].parameter" (something like in the example in the image) but it doesn't work. I know that it works with just strings, numbers and booleans as inputs, but I have 4 infotables like this one, with 6-10 parameters each, so it would need to have about 35 services just for that.

     

    Other solution that I thought of was to use a second Service that have the Infotable as output, output each row once and call the SQL Query once for each row, linking the parameters inside as different inputs (string, bools, ints). With this solution I don't have the problem of needing a crazy amount of different Services, but I need to run both of them once per row, so with the 4 infotables that I have, I would end up running between 10-15 times each one (number of rows that I usually need), so I'll end up running a full service about 100 times

    (12rows * 2services per infotable * 4infotables), which would decrease the performance a lot.

     

    Is there any way I can make this work without any of those problems (crazy amount of services or crazy amount of runs)? I thought about using the properties of the Thing in which I have the SQL Querys, but I haven't been able to call them from SQL Query code.

     

    Regards,
    Josue Nanin

     

     

     

     

     

    SQL Query.JPG