I have wrote a SQL query to fetch a certain data into a Dropdown-Menu based on IN function. So for example: SELECT recipe_name FROM recipe WHERE recipe_id IN (1,2,…,n)
My tuple after IN is variable and for that I’ve wrote a project library function which works fine. The issue is I’m triggering this query when I move cursor/mouse on a Dropdown-Menu.
This works perfectly in the designer when I go to RUN, however it doesn’t work when I launch into client.
Please suggest. Let me know if you need anything else for this.
It seems to me that this isn’t something that should be done as part of a mouseXXX Event because the results might not be reported by the time the user has clicked. If the recipe id list is variable, then what is calculating the ids to include? You could always save that query as a Named Query, and then bind the dropdown’s options property to the results of the Named Query, and supply the list as a parameter for the Named Query. Doing this would result in the Named Query running whenever the list is modified.
The recipe_id is variable, the selected recipe id gets added into the list when a user clicks on “Release for a Production” Button from another window.
Hence for example if on a window 1 (a master recipe screen) recipe_id 2 is selected and user presses “Release this recipe for Production”, this ID gets added into a Memory Dataset tag by system.dataset.addRow (if it’s not there already in the dataset). Later there’s a project script which converts this list into a tuple. Then the Dropdown-Menü present on window 2 (Control Recipe screen) will have this recipe to select.
The reason I added this script to a mouse Entered script and not to a binding is, I tried SQL Query binding and send variable list as a parameter after WHERE recipe_id IN function, but i wasn’t sure what DataType should the parameter be to store the tuple. I tried with int, string (later use typeCast) but couldn’t get the result.
I’ll try with the named query, I’ll only have to pass the variable list as a named query :=Parameter.
Is that correct?
JDBC does not generally support passing a list as a single parameter. Using the IN operator generally requires the use of runPrepQuery where the appropriate number of question marks are placed inside the operator's parenthesis, and the argument list constructed to match the number of question marks. Sorry.
Using the IN operator generally requires the use of runPrepQuery where the appropriate number of question marks are placed inside the operator’s parenthesis, and the argument list constructed to match the number of question marks
Okay got your point, but as I mentioned earlier; my list is variable. Hence I’m not sure how putting appropriate number of question marks would work here as when I add/delete values from a list. I’d have to increase/decrease number of ? as well.
Yes. You construct the query string with the number of question marks that match your tuple's length (along with any other params), and then execute passing the tuple (extended with other params if applicable).
Here’s an alternate method for dynamic variable list, however it’s a bit of a hack and a bit fragile, so if you can get Phil’s method to work, go with that.
In this example I’m getting my list from DB, so create a SQL tag type string, query:
SELECT GROUP_CONCAT(CONCAT("'", listItems, "'") SEPARATOR ',') FROM db.table WHERE abc=xyz
You can’t use PrepQuery, so you have to use string substitution;
x = system.tag.read("yourSQLtag")
sites = system.db.runQuery("SELECT Site from Sites where Site IN (%s)" % x,DB)
@cmallonee I tried using as a NamedQuery. I am getting some issue in the List I generated.
Thing is when I execute same script in Script Console, I am getting the correct output. But in the designer on the string custom property (which later has passed as a queryString parameter to named query) it does not update. Sometimes it also shows same tuple twice in the string.
Below is script I added in Project Script:
dsTag = system.tag.read("[Proj_Name]Global/ReleasedMasterRecipes").value
releasedRecipesL = []
def releasedRecipes():
for i in range(dsTag.getRowCount()):
releasedRecipesL.append(dsTag.getValueAt(i,0))
return tuple(releasedRecipesL)
Later added string custom property to the dropdown and called this function
You’re using the deprecated system.tag.read() function; you should use either readBlocking or readAsync. Depending on scope, is there any chance that the releasedRecipesL list is being retained between executions? You should put in some logging, but it looks to me like your list is being created, appended to, and then when the function is called again the previous list is being appended to again. Try this:
def releasedRecipes():
dsTag = system.tag.readBlocking("[Proj_Name]Global/ReleasedMasterRecipes").value
releasedRecipesL = []
for i in range(dsTag.getRowCount()):
releasedRecipesL.append(dsTag.getValueAt(i,0))
return " ".join(releasedRecipesL)
I tried using join method but it did not work either. It kept on adding previous list into newest one.
Hence right now I created empty set instead of list and later converted to tuple.
