Named query cannot be found in project resource

I am having a strange situation where i get the following error when trying to run a named query via a Gateway scoped shared script:

Traceback (most recent call last):
  File "<module:shared.util>", line 190, in wrapper
  File "<module:shared.util>", line 492, in getMCcurrDataFromSQL
Exception: java.lang.Exception: Did not find query GetMCStats in project resources.

I am currently using the following method to determine the scope of which the function is being called, which seemed to work just fine for me in versions 7.9.X, but i am not sure it is the best way to do it now in versions 8.1.X.

The getScope function i am using:

def getScope():
	"""
	Determines what scope code is being called from. Certain functions require different parameters depending 
	on scope. 
	
	Returns:
		location (int): 0=Gateway, 1=Client, 2=Designer
	
	Date:			Author:			Notes:
	26/11/2019		richardNZ15	    Initial
	
	"""

	from com.inductiveautomation.ignition.common.model import ApplicationScope
	
	scope = ApplicationScope.getGlobalScope()
	
	if ApplicationScope.isClient(scope):
		location = 1
	elif ApplicationScope.isDesigner(scope):
		location = 2
	
	elif ApplicationScope.isGateway(scope):
		location = 0
	
	else:
		location = 0
	# end if
	
	return location
	
# end def

The function referenced in the error message that i am trying to run:

@errorHandlerGlobal("MachineCenters","shared/util/getMCcurrDataFromSQL ",alarmRqd=False)
def getMCcurrDataFromSQL(MC_ID):
	"""
	This function utilises a named query (GetMCStats) to return the current run minutes and production volume for a 
	machine center specified by unique ID (MC_ID).
	
	Parameters:
		MC_ID (string):		Machine center unique ID
	
		Returns:
			runMin (int):		Current accumulated MC run minutes
			prodVol (float):	Current accumulated MC production volume
		
		
		
		Date:				Author:				Revision:
		17/02/2022			richardNZ15			Initial
		
	"""		
	scriptName = "shared/util/getMCcurrDataFromSQL"
	logger = system.util.getLogger("MachineCenters")
	
	runMin = -1
	prodVol = -1.0
	 
	params = {"MC_ID":MC_ID}
	# Need to specify Project name as first argument if calling from Gateway scope.. otherwise must be left blank.
	if shared.util.getScope() > 0:
		data = system.db.runNamedQuery("GetMCStats", params)	
	else:
		data = system.db.runNamedQuery("G1-Line01","GetMCStats", params)
	# end if
	
	if data.getRowCount() != 1:
		logger.error(scriptName + " - Invalid number of rows returned from Named Query 'GetMCStats' for MC_ID = '%s'" % MC_ID)
		
		return runMin, prodVol
	else:
		runMin = data.getValueAt(0,"CurrRunMins")
		prodVol = data.getValueAt(0,"CurrM3")
		###logger.debug(scriptName + " - Stats for MC '%s': runMin= %0d, prodVol= %06f" % (MC_ID, runMin, prodVol))
		print (scriptName + " - Stats for MC '%s': runMin= %0d, prodVol= %06f" % (MC_ID, runMin, prodVol))
		return runMin, prodVol
	# end if
	
	
# end def

I currently have the following project inheritance configured, which i suspect has something to do with the issues:
G1-Master
G1-Master > G1-Line01
G1-Master > G1-Backend

I would expect all normal calls of the function to originate from G1-Line01 project, which is the main SCADA project.

I get the error while calling the function in the G1-Master Designer scope, in the script console, but do not get any errors while calling from G1-Line01 Designer scope

The named query itself is defined in the G1-Line01 project, so it makes sense that i would get errors in the G1-Master designer.

What is odd is that i’ve got errors when users have called the function in the G1-Line01 project. Is this because the script call is calling shared.util.getMCcurrDataFromSQL, which is a gateway shared script defined in the G1-Master project?

In which case i’m doomed?

Would it not be simpler if IA just allowed us to pass the ProjectName into the system.db.runNamedQuery

function regardless of scope?? So we can point it where it needs to go?

Yes. The other system.db functions that have an optional data source in project scope have it in the python args list in a suitable place. IA screwed up when designing the NQ API.

But you aren't doomed. Shared/inherited scripts are not magically running in gateway scope when called from Vision or the Designer. All scripts run in the scope they are called from. Getting to another scope requires messaging. Consider putting a gateway message handler in your G1-Line01 project that executes this query. Then you can use system.util.sendRequest to call it.

4 Likes

I just wanted to chime in I had the same issue originally where I had to check scope to see what version of system.db.runNamedQuery to call as you are doing. I found that it made my code unnecessarily complicated and my solution was the same as what @pturmel is suggesting - have the client send a message that triggers the script on the gateway.

In my use case, it was a long operation with a lot of data manipulation going on, so it was better suited for the gateway anyways for resource reasons, but also, so a client could not start it and then exit their session in the middle of it - once it starts on the gateway, it will run even if the client logs out/turns off their computer (assuming the gateway computer stays on).

Overall though I agree that it would make things much easier if we could call system.db.runNamedQuery with the project argument from whatever scope, if only so we don’t have to do stuff like this

if shared.util.getScope() > 0:
	data = system.db.runNamedQuery("GetMCStats", params)	
else:
	data = system.db.runNamedQuery("G1-Line01","GetMCStats", params)
1 Like

It's on the list :slight_smile: Probably won't happen until 8.2, since there's a bunch of other stuff we'd like to reorganize and shuffle around a bit too, but we're aware of the problem.

And to reassure anyone reading that - as always, we'll take pains to ensure existing scripts continue to work on upgrade; there will just be new options available for certain scripts to call instead.

6 Likes

Thanks for the feedback everyone. I will try the gateway message handler in combination with sendRequest if it comes to that.

But what i still don't quite understand is that if this function is being called in the Vision scope, as below,

then my scope checking should work, and run the following line:

data = system.db.runNamedQuery("GetMCStats", params)	

Because the client is on the G1-Line01 project, it should certainly have access to the named query that is defined in G1-Line01, AND any named queries defined in G1-Master, due to inheritance.

What is frustrating is that i cannot really debug what is going wrong, as any loggers i might setup to diagnose things along the way will only show up on the Vision client, which i do not have access to.
I've tested similar calls to the script with a new test button, and it seems to work fine on my Vision Client.

Not true. You can use system.util.getLogger() to log messages to the gateway logs. Since you have access to the designer, you probably also have access to the gateway web page.

Loggers are scope dependent, and will print to wherever the script is being called. Hence if a button is pressed in a client scope, which calls a shared script that has loggers in it, those logger messages will go to the Client Diagnostics logging view.

I’m not aware of how you can force a client / designer scoped logger to push to the gateway logs.

This application is Vision btw.

Yes, but you can easily use sendMessage to a gateway message handler and then use getLogger() which will cause the logger to always be run from the gateway scope.

Not recommended for mass use, but for troubleshooting purposes where you don’t have access to the local consoles, it works quite well.

1 Like

Right, I see. Yes good trick. I only recently started thinking about using sendMessage function when Perspective came along.
It is tempting to adopt that more widely as a debugging tool…

1 Like