Thanks for your reply. In the meantime i have one more query. Would you please share your insights about it
I am currently working on a ThingWorx Database Thing service and came across a best practice recommendation stating that [[ ]] should not be used in SQL queries. Instead, values should be passed as input parameters. However, I am facing difficulties implementing this correctly.
I referred to this ThingWorx documentation:
https://support.ptc.com/help/thingworx/platform/r9.7/en/index.html?_gl=1*br4gd2*_gcl_au*OTY2NDE4Mjg3LjE3Mzk5NDI0MjM.*_ga*MjAwNjg4ODQ2MS4xNzM5OTQyNDIy*_ga_7NMP2MSYPM*MTc0MjgxODk5NS4zOS4xLjE3NDI4MTkwMjIuMzMuMC4w#page/ThingWorx/Help/Composer/Things/ThingServices.htmlThingWorx Service Best Practices
My SQL Query in ThingWorx Service
Previously, I was using [[ ]] like this:
sql
SELECT * FROM dbo.QualityDataTable WHERE lineName = [[lineName]] AND
FREETEXT((serialNumber, userIdentification, jsonData), <<filterParameter>>)
ORDER BY qualityCheckTimestamp DESC;
This approach worked fine, but based on the documentation, I wanted to switch to named parameters instead.
Approach 1: Using Named Parameters in JavaScript
I attempted to pass the parameters like this in my JavaScript service:
javascript
let params = { filterParameter: "'" + searchinput + "'",
lineName: lineName };
let dataBaseQuery = Things["V2.MYSQLDatabaseController"].SQLQuery_GetEntryBySearchInput(params);
However, this approach did not work, and I’m unsure whether I should keep the manual quotes around searchinput for FREETEXT().
Approach 2: Using ? Placeholders
To follow best practices, I modified my SQL query to use ? placeholders:
sql
SELECT * FROM dbo.QualityDataTable WHERE lineName = ? AND
FREETEXT((serialNumber, userIdentification, jsonData), ?)
ORDER BY qualityCheckTimestamp DESC;
And updated my JavaScript code to:
javascript
let params = { values: [lineName, searchinput] // Passing parameters as an array };
let dataBaseQuery = Things["V2.MYSQLDatabaseController"].SQLQuery_GetEntryBySearchInput(params);
However, this also did not work, and I suspect it may be due to how parameters are bound in the query.
Questions:
How should I properly bind named parameters (lineName, filterParameter) in a ThingWorx Database Thing query?
Do I need to manually add quotes around searchinput for FREETEXT() to work correctly?
Does ThingWorx support ? placeholders for SQL queries in Database Thing, or should I stick with named parameters?
