How to implement SQL query in thingworx as follows,
Query:
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
In thingworx, how to pass country names as input parameter.
Solved! Go to Solution.
Some example is shown here: https://community.ptc.com/t5/IoT-Tech-Tips/Using-a-Validator-with-a-Service-to-perform-more-advanced/td-p/535319
No. Im going to use this query in "Database Thing" as "SQL Query". So, I want pass comma separated values to this query. How can i do with Thinworx SQL query services?
you can try like this:
select * from user_model where name not in ([[name1]],[[name2]])
Where name1 and name2 are your service inputs.
Thanks Mukul for the support.
My case is inputs are dynamic. Based on multiple selection in drop-down, I want query using IN.
You can try this approach, take all the selection as an infotable, push each value of that infotable column(country) into an array and make the array output something like ('Germany','France'); pass this String array as an input to SQL query service wherein put input like - select * from customers where country not in <<input>>;
Thanks for the support.
It seems, not working for me.
SQL query: testSQL
input (string)
select * from table where countrycode in input;
JavaScript Service:
var input = [530, 531];
let result = me.Test({
input: input /* STRING */
});
I am getting following error message.
Message :: Execute Update failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ']'. - See Script Error Log for more details.
Let me know anything i missed here.
change your query syntax like this:
select * from table where countrycode in <<input>>;
Change your input like this:
var input = ('530','531')
I tried the same.
Input basetype is String.
select * from table where countrycode in <<input>>;
input = ('530','531');
Getting following error:
Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
I think SQL server is not accepting it, I have postgres in my use case and it works fine like below. Let me test this with mssql, will get back to you.
Thanks for you support. Let me test in my environment and confirm.
Hi @Sathishkumar_C.
Were you able to complete your testing? What were the results?
Regards.
--Sharon
Its working with only one value.
Not working with multiple comma separated values.
Hi Satish
try putting the input like (<<filter>>) and not like([[filter]]) this is mentioned in the link provided by Sharon.