I need to populate dropdown with SQL query and I did it using named query, it returns in dataset. However I converted it to json so that my WHERE clause would work. With the datasets, it was showing me an errors.
Here in the WHERE clause if I pass a single value, it works but I want to pass a values like 1,2,3, so on. As seen in screenshot, when I pass another value it shows me an error. Basically this is how it should work:
I have tons of recipes with ID being primary key and another column is recipe_name. I am not showing all recipes for a select in dropdown, I am passing certain values based on certain selections. Like below:
SELECT recipe_id as value, recipe_name as label FROM recipe WHERE recipe_id IN (hereVariableListComes)
This works well with vision but I am facing some erros here. please suggest
This was answered in the other thread. Use a script with runPrepQuery, generating SQL with a suitable number of ? substitutions. You can't just pass a tuple into a query. At least, not without massaging its string representation into something your SQL server will tolerate.
I did it the way you said this time. I did it in the transform and it all worked out.
Is there any better way to achive this than this?
pyData = system.dataset.toPyDataSet(system.tag.read("[projName]Global/ReleasedMasterRecipes").value)
pyDataL = []
rowCount = len(pyData)
placeholders = ",".join(list("?"*rowCount))
query = "SELECT recipe_id, recipe_name FROM recipe WHERE recipe_id IN ({})".format(placeholders)
for row in pyData:
pyDataL.append(row[0])
value = system.db.runPrepQuery(query,pyDataL)
return value
This works out but little bit confused if there is any better way to do that pyDataL list I am making
The native Dataset type (straight out of the tag) also has a .getColumnAsList() method that would be even simpler. No conversion to a PyDataset nor any loop.
