Begin Transaction, problem with tx number java.util.map

Ignition
,
1

Hey all,

I am running a script from a button event, that sends a message to the root container, which runs a named query, and inserts a few values into the DB.
I am trying to test the system.db.beginNamedQueryTransaction because I am trying to run multiple update queries back-to-back in a new project, but for some reason I only get a few updates (out of 27) to run successfully.
In this test, this is the query:

INSERT INTO [dbo].[Table_3] ([F1], [F2], [F3])
VALUES (:p_1, :p_2, :p_3 )

This is the message handler: (`test01')

def onMessageReceived(self, payload):
	# implement your handler here
	query = 'Coater/Test01'
	p1 = self.getChild("NumericEntryField").props.value
	p2 = self.getChild("NumericEntryField_0").props.value
	p3 = self.getChild("TextField").props.text
	params = {'p_1':p1, 'p_2':p2, 'p_3':p3}
	proj = 'Sandbox_Sample'
	db = 'IGNSVR_Dev'
	tx = ''
	
	tx = system.db.beginNamedQueryTransaction(proj, db, 2)
	system.db.runNamedQuery(query, params, tx)
	system.db.commitTransaction(tx)

(I removed the closeTransaction to see if anything would change...)

This is the button event script:

def runAction(self, event):
	msg = 'test01'
	payload = {}
	system.perspective.sendMessage(msg, payload)

This is the gist of the error:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 23, in onMessageReceived at ... 
java.lang.ClassCastException: java.lang.ClassCastException: 
Cannot coerce value '90da8353-ce17-42a0-852e-65506aacc7d2' into type: interface java.util.Map

I've read a few other threads and the docs and it appears the args for Perspective scope are correct.
Any ideas?

2

Looks like in some cases the tx is being passed to the params argument of the runNamedQuery function.

system.db.runNamedQuery(query, params, tx)

Needs project name at the front. If this is in perspective it runs in gatetway-esc scope, despite what the manual says.

I made a helper function to automatically add project name at the front based on the executed scope. I use this across the board in perspective projects, handlers and otherwise. Some of my use cases match yours exactly (message -> handler -> transaction), though all my handlers are 1 line calls to a script library function.

def runContextualNamedQuery(path, params=None, tx=None, getKey=False):
	""" """
	if shared.util.getExecutionScope() in ("P", "G"):
		return system.db.runNamedQuery(system.util.getProjectName(), path, params, tx, getKey)
	else:
		return system.db.runNamedQuery(path, params, tx, getKey)

from com.inductiveautomation.ignition.common.model import ApplicationScope

def getExecutionScope():
	""" """
	scope = ApplicationScope.getGlobalScope()
	if ApplicationScope.isClient(scope):
		return "C"

	if ApplicationScope.isDesigner(scope):
		return "D"

	if ApplicationScope.isGateway(scope):
		if "perspective" in dir(system):
			return "P"

		return "G"

	return "UNK"
1 Like
3

shared.util is a Project Library package?

This works well! Thank you Ryan!

4

Correct, its in the project at the top of our inheritance stack. We've also put stuff like pturmel's later.py, xml.py and queueUtil.py in there.

Edit, looks like this issue has been around a while? Found this:

Also, if you are interested, a transaction decorator that automatically catches errors:

2 Likes
5

This will always be true (in gateway scope, if you have the Perspective module installed) - this won't catch scenarios where you're not in a Perspective launched context but elsewhere on the gateway (e.g. a tag event script). See @nminchin's similar looking code here:

I'll also mention for posterity here that in 8.3 we introduced new named query scripting functions to address this fundamental inconsistency.

3 Likes
6

Huh, guess I've been lucky enough with my usage I haven't smacked into this. Will update this immediately.

Any insight as to why the named query call is expecting the project name, despite being called in a perspective context? This appears to only happen after starting a transaction in a perspective scope.

7

I don't know if this makes any difference, but I tried IsolationLevel 8 and things weren't working very well, with 27 update queries in queue, but with a level 2, everything seems to work fine so far. I also changed the helper function as Paul pointed out in the other thread.

EDIT:
And, I'm running them like this:

		try:
			runContextualNamedQuery(query, params, txNumber1)
		except Throwable as t:
			system.db.rollbackTransaction(txNumber1)
		else:
			system.db.commitTransaction(txNumber1)
		finally:
			system.db.closeTransaction(txNumber1)
		
		sleep(0.5)

I don't know if the sleep is necessary, but I seem to recall an issue with running queries without a slight pause. This might not make a difference when using transactions, though. Have yet to test it out.

8

Not sure. I would've thought that providing a transaction entered some different block of code early on, but the check to load the contextual project name happens right at the top of the runNamedQuery block.