Gateway Version 8.1.32
Hi,
i create a very simple "scalar named query" that return a string.
The returned string is a rapresentation of a json (nvarchar(max)).
When the NQ is called by Designer using the "Testing tool" it works correctly.
When the NQ is called by scripting the return value is not a scalar but an empty pyDataset (i know that is a new feature.. but scalar must return Any value and not the pyDataset).
def nq_Historian_Template_Props_by_Id(id):
nq = "RUB2/IG/Historian_Template_by_Plant"
nq_par = {'Id':id}
props = system.db.runNamedQuery(nq, nq_par)
return props
Calling the query using the "system.db.runScalarQuery" it works as aspected.
def Historian_Template_Props_by_Id(id):
query = "SELECT props from historian_template where historian_template_id = %i " % (id)
props = system.db.runScalarQuery(query, "RUB2_IG_Historian")
return props
id is a python function. Avoid using it as a variable. What happens if you use something else?
def nq_Historian_Template_Props_by_Id(idNum):
nq = "RUB2/IG/Historian_Template_by_Plant"
nq_par = {'Id':idNum}
props = system.db.runNamedQuery(nq, nq_par)
return props
2 Likes
Could it be your fallback?
Hi, I tried changing the return type and disabling the fallback, but with no luck..
What DB are you using? MySQL, MSSQL, Postgres, etc? What's the exact column type - json? Can you try coercing the column into a string/varchar directly in your named query, e.g. select cast(props, string) in whatever syntax your DB provider wants for table coercion?
Microsoft SQL
NVarchar(max)
I’ve already tried the select-cast method before writing the original post..
Same result with 8.1.34 version.
We tried to mock this up internally and weren't able to reproduce. Can you confirm that the parameter name in your named query exactly matches the one in script (in your screenshots, they both appear to be Id, capital I, lowercase d, but I just want to make sure). You might also want to rename the parameter, in case Id is somehow being sent semantically to the DB and throwing an exception? I'm grasping at straws here.
You could also try putting the NamedQueries.executor logger on TRACE on the Gateway and running your query; it should log a clear message indicating that the fallback value was used due to an exception inside your query (in case that's somehow the problem).
I'd probably contact Support about this at this point, though - it's sufficiently "weird" that it could be a number of things, and it'll be a lot faster to troubleshoot an issue like that live with a support rep.
1 Like
Hi,
you were right. Thanks to your advice, I realized that the named query invoked was not the correct one due to our silly copy/paste error 
... i'm so sorry! thanks for the support!
3 Likes
