Hello,
Looking for some help with a script I have that's triggering a named query. The named query is
Select linkID
from Recipe_Links
where recipeID = :recipe_id
AND Status = 1
and the section of script that's running it is:
named_query_path = "get_link_ids_by_recipe_id"
named_query_params = {"recipe_id": max(recipe_ids_list)}
IDs = system.db.runNamedQuery(named_query_path, named_query_params)
IDs_list = [x[0] for x in system.dataset.toPyDataSet(IDs)]
message = "%s" %IDs_list
system.perspective.openPopup("", "Popup/MessageBox", {"Message" : message})
I have verified that the "max(recipe_ids_list)" is returning the integer I expect, so there are definitely valid records that should be getting selected by the query. I can run the query in the testing section of the named query editor, feeding it a known-correct integer value, and it does return the records I expect. I've quadruple checked that the name I'm using in the script is correct and that the parameter name is correct too.
Does anyone have an idea what might be going wrong? I get no error popups, no errors in the gateway log, and the rest of my script executes fine (minus the part it's supposed to do if there were any values in the IDs_List)
Hmmm.. That should work.
The only things I can thing of:
-
system.perspective.print the max(recipe_ids_list) in your script execution to make sure its the right ID (I know you mentioned that you already verified, but I want to stress that its important to verify it as your script sees that variable).
-
8.1.29 now returns Named Query results as PyDataSet by default, so if you're on a more recent version, maybe that's what's causing the problem (you're casting to PyDataSet when it's already a pyds)
-
Make sure that as your script executes, you're actually running that part of your script. It's hard to tell without seeing the entire script, but I usually put a print statement before and after a section to make sure it actually gets executed.
The gist of my answer is: I'm grasping at straws because what you have in the question looks correct to me. I just verified with a sample query and it should work. I was hoping it was something obvious, like you missing a database parameter to your named query (that should trigger an error) or you calling the parameter parameter to the system.db.runNamedQuery params instead of parameters (idk why it's called parameters here but params everywhere else - this still gets me once in a while).
1 Like
'preciate the help here!
I can confirm the script executes up to that point, I have a message box popup getting fired and printing the value of max(recipe_ids_list) executing before the sample I gave there, and I also confirm that the messagebox after the query runs is executing and printing "" showing me my empty list.
It does feel like it's some issue with the input data type though, as I can run the following section of code with a static integer value passed in for "recipe_id":
def help(recipe_id):
named_query_path = "get_link_ids_by_recipe_id"
named_query_params = {"recipe_id": recipe_id}
part_link_ids = system.db.runNamedQuery(named_query_path, named_query_params)
part_link_ids_list = [x[0] for x in system.dataset.toPyDataSet(part_link_ids)]
return part_link_ids_list
That said, the earlier sample still gave me an empty list when I ran it with the same static integer under the same conditions: named_query_path =
"get_link_ids_by_recipe_id"
named_query_params = {"recipe_id": 1}
IDs = system.db.runNamedQuery(named_query_path, named_query_params)
IDs_list = [x[0] for x in system.dataset.toPyDataSet(IDs)]
message = "%s" %IDs_list
system.perspective.openPopup("", "Popup/MessageBox", {"Message" : message})
so I'm still really out of my mind on what's happening. I am on version 8.1.30, but the examples I have of the query working still use the toPyDataSet
I was able to resolve it using system.db.runQuery() and giving a query string with a %s to pass in my max(recipe_ids_list) parameter. No idea why the unnamed query makes the difference over doing a named query, but my list is no longer empty, so I'm calling that a W
REDACTED
as it turns out, my unnamed query approach left out my "AND status = 1" condition from the query. Adding that back into the query, named or unnamed, causes the script to return an empty list, even though it still works properly in the query browser and in the named query builder
Is your recipe_id param in your named query an integer or a string? If it's expecting a string, did you try passing in "1" rather than 1 into your script?
1 Like
Unfortunately no, it's looking for an integer and getting an integer
Can you get a screenshot of the query browser where it's working, and the named query definition that isn't?
Hmm. Still wondering if it's something to do with parameter passing and data types, that's normally what I find if I have inconsistencies between results in the named query workspace and calling the named query via script. Maybe play around with running the named query from the script console? (Don't forget it's gateway scoped)
1 Like
This is the full set of data in the table
This is the query I want to run, selecting just the IDs from that table for records that match the recipeID and have status = 1
here is the named query definition that does not work when called using my earlier syntax
and here is that same named query working correctly from the testing tab
seems to only fail on me when I try to use the AND syntax in a query that gets executed in from a script
When you SELECT * the column is called status and in your query, you're searching for Status - column names aren't case-sensitive AFAIK so that shouldn't matter but give that a try?
1 Like
had an almost-eureka moment an hour or so ago where I noticed that too but to no avail. The table column is "status" but keeping it lowercase in the query, named or not, did not fix it
That's so strange... what DB vendor are you using? Is your default DB set (although it should be as you're able to run the query in the Test workspace)? Is the data type of recipe_id really Int8 (that translates to bigint for MSSQL).
using MSSQL 2019, default is set, and no it looks like I'm just using regular int in the table at the moment. would that cause issues even though I'm way below the limits of int right now?
I think if your DB column is of type INT and your parameter is of type bigint, that may cause issues. I vaguely remember running into something like this in the past, but I don't remember exactly. What happens when you try to set the Named Query parameter type to Int4?
With the parameter set to Int4 I still get the same behavior; still works if I don't include the "AND status = 1" condition in the query
