runNamedQuery comprehension syntax options

Ignition
,
1

I'm trying to tidy up a lot of code I wrote ages ago when I was even worse at it than I am now.
One thing I want to do just now is to make an array or list of pyDatasets from the same named query run for several machines.

The parameters for the names query are id, start, and end.

I've had a look through the forum and this post seemed helpful:

so I've started there.

The code I have at the moment is:

def transform(self, value, quality, timestamp):
	
	machineDict = self.getChild("root").getChild("oeeRunTime").getChild("flxSettings").getChild("flxSetUp").getChild("machineID").props.value
	
	machinePy = system.dataset.toPyDataSet(
		system.db.runNamedQuery(
		"Script/function for id",
		{'id':[machineDict[v]['machID'] for v in range(len(machineDict))],
		'start':self.custom.startTime,
		'end':system.date.addHours(self.custom.startTime,8)}
		)
	)

	return machinePy

I'm getting an Error_ScriptEval. It's a long message, and I don't know how to copy it (I can just see it as hover text - it says "(Unknown Source)" a lot of times...

Any help on whether this is a thing it is possible to do, or not, and in which ways I have done or approached it wrong greatly appreciated :stuck_out_tongue:

Thanks
Cameron

2

I don't think this will ever work. I don't think any of the named query parameter types (or even query string) know what to do with a python list. Even if it's just passed through as a string, that's probably causing incorrect SQL syntax.

I think what you really need to do is something like

machinePy = None
endTime = system.date.addHours(self.custom.startTime,8)
startTime = self.custom.startTime
for v in range(len(machineDict)):
    if machinePy is None:
        machinePy = system.db.runNamedQuery("yourNamedQuery", {"id":machineDict[v]['machID'], 'start':startTime, 'end':endTime})
    else:
        newDs = system.db.runNamedQuery("yourNamedQuery", {"id":machineDict[v]['machID'], 'start':startTime, 'end':endTime})
        machinePy = system.dataset.appendDataset(machinePy, newDs)

return machinePy

This is just rearranging your way.

There are better ways to go through a dictionary. You can do something like

for key, value in machineDict.items():

And this can simplify things further. I leave this as an exercise for the reader :slight_smile:

I also think you need a project parameter for named queries in perspective?

3

Also, within Perspective scripts always qualify as gateway-scoped, so you'll need to include the Project name as an argument of the runNamedQuery invocation.

1 Like
4

Thank you for this!
That's kind of how I had it set up before trying to play with comprehensions in it :stuck_out_tongue:
It's good to be led away from barking up the wrong tree.
New dictionary stepping options is nice to have - I shall bark up that tree instead.

@cmallonee I've never had to use a project name for a named query. There are several of them in the projects I have - trying to put the project name as an argument in the named query call actually seems to upset it... :person_shrugging:

1 Like