Perspective Dropdown SQL Query

Hi there,

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.

See below screenshot:

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

Ignition version: v8.1
OS: Windows 10, 64 bit

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.

Thanks alot @pturmel!

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

You can use list comprehension for this:

for row in pyData:
		pyDataL.append(row[0])

Becomes:

pyDataL = [row[0] for row in pyData] 
2 Likes

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.

Will try this
Also thanks @nminchin