runNamedQuery()

I am trying to run "runNamedQuery()" from a script. I want to use it like I would the following:
data = system.db.runQuery(SELECT * FROM datatable)
Then I would use data[0][1] or something similar throughout my script.
When I try using
data = system.db.runNamedQuery(queryName, params)
I never have any data in the data dataset.

  1. Does your named query return data in the Named Query Testing tab? (You didn't say.)
  2. Does your named query return data when run from the Script Console?
  3. Your statement,
    data = system.db.runQuery(SELECT * FROM datatable)
    should be quoted thus:
    data = system.db.runQuery("SELECT * FROM datatable")
    but named queries are to be preferred.

Tip: use the </> code formatting button to preserve code indentation and apply syntax highlighting.

The named query does work in the testing tab.

The named query does not return data from the script console

Then please show how you are setting up the params variable.

When you run the following script:

Create a python dictionary of parameters to pass

params = { "param1" : "my string" , "param2" : 10 }

Run the Named Query

system.db.runNamedQuery( "myUpdateQuery" , params)

Or when you run this script:

Request the Named Query with an empty dictionary as the second parameter.

system.db.runNamedQuery( "folderName/myNamedQuery" , {})

How do you get the results of the query?

Please edit your post and format your code blocks with the </> button.

I figured out how to pull the data out of the runNamedQuery script. i.e. how to get the results from running the query.

And how did you do that? I have the same problem right now.

Thank you

Please show a preformatted text snip of your code. The context of the OP and follow-ups here is extremely vague as to what the actual problem was.

It is a function. You assign the return value to a variable. Something like this:

ds = system.db.runNamedQuery(....)

(This is pretty fundamental python behavior. Have you taken any training?)

I have a Named Query called Select Report Number B

SELECT id_test_report FROM _test_report 
ORDER BY id_test_report DESC LIMIT 1;

This Query gives me the last number report and I need to send that number to another table. That is not the problem, the problem is that I had tried to get that value in my script like this:

	report=system.db.runNamedQuery("Select Report Number B")
	reportValue=report[0]

or

	system.db.runNamedQuery("Select Report Number B", paramReport)
	reportValue=paramReport[0]

Sorry if these kind of very easy questions of pretty fundamental python make the eyes of the Gods hurt but the ignition page is down
https://www.docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery

No need to apologize, it's much easier to help with proper details. Thanks for those.

Make sure you have configured the Named Query as scalar. Then what you need is:

reportValue = system.db.runNamedQuery("Select Report Number B", {})

No, its just in a different place now.

See this post:

3 Likes

Thank you!,

Now is working, I really appreciate all your help and thank you for the documentation update!

1 Like

One more aspect to be aware of/may save you some time in the future. This got me good for a while on fully understanding the different scopes.

I was using the runNamedQuery() in the alarm pipeline and I forgot that is a Gateway scope module. So the name of my project needed to be added. Took me way longer to figure out than it should have.

example:
Script console (designer scope? only works in designer):

db = system.db.runNamedQuery("Your Saved Name Query", {"pram1":"value",etc})

vs the alarm pipeline (Gateway scope)

db = system.db.runNamedQuery("ProjectName", "Your Saved Name Query", {"pram1":"value",etc})

If needed this post is really good at knowing what scope a script is coming from

Designer Scope is a variant of Vision Client Scope. FYI.

3 Likes